OLAP vs OLTP | Online Transaction Processing vs Online Analytical Processing | Intellipaat

[Music] hello everyone welcome to telepath and we are back again with another interesting video on a versus topic today's session is gonna be all about Olaf versus OLTP which is online analytical processing versus online transaction processing so there's before we proceed further do subscribe to in telepaths Europe channel so that you never miss out on any upcoming videos so let's have a look at the agenda for this video first and foremost will brief you with the introduction to all up and OLTP in order to understand the concept more effectively then we'll go through the real-time examples based on each one of them and then gradually we will discuss about the comparison between these two based on few parameters such as motive to use types of query forms of query types of users normalization storage backup and recovery and design and then we will also get to know about the advantages and disadvantages of using OLTP and oil AP right and then finally we will wrap up the video with some conclusions based on them we'll also have a short quiz at the end of this video in order to recap but all you have covered make sure you put on your answers in the comment section below to know if you are correct also guys if you are looking for the courses in the pending technologies then do visit in telepath comm so without any further delays let's get started so as there is start of by understanding both of these technologies in a brief first and foremost what exactly is ola a Cronin fro OLAP is online analytical processing and consists of a type of software tools that are used for data analysis in order to make business decisions it is mainly used for an offline storage of data and this data can be accessed offering also write and database information stored in this is used by the business analysts like manager executive for analysis and reporting purposes right by using OLAP you can extract database informations for multiple databases and analyze it for decision making at one time and it is being majorly used for data analysis right moving forward to what exactly is OLTP acronym for OLTP is online transaction processing and is a system that manages transaction-oriented applications on the Internet in a three-tiered architecture and it has been used by the end-user such as database administrators database professionals and only B manages day-to-day transactions of and reason behind this is using data processing right now let us move forward and discuss the examples of OLAP and OLTP first and foremost example of ola let us understand how OLAP applications are used in different verticals of an organization first OLAP is used in finance departments for applications such as budgeting financial performance analysis activity-based costing allocation I would say and financial modeling right in sales department OLAP applications are used for sales analysis and forecasting and in marketing departments OLAP is used for marketing research analysis sales forecasting customer analysis promotions analysis and market or customer segmentations right and as all of the above applications of olla provides the information with the managers or decision makers that they need to make effective decisions about an organization's strategic directions and also that is in Netflix movies are recommended to users based on their previously watch history right it also gives you the percentage of matching between the recommended movie and previously watched movie right similarly in e-commerce companies like Myntra users get recommendations for their apparel accessories and other stuff based on the previous purchase right so as we move forward examples of OLTP are as follows so in online banking customers use online services for checking account balance and for directing the fund balances right and in order to purchase a book online it helps you to add a book to the shopping cart right and also call center staff users OLTP in order to view and update customers details and the next example I can give you is that in order to book an airline ticket and for sending a text message again OLTP is being used to write another use cases that in ATM Center it is being used for money in a drawls transfers deposits and inquiries right also other use cases are like to make order entries in hotel retail and medical industry so as in oil TV systems the interactions between the user other systems are comparatively very short users can perform complete transaction through short interactions and immediate response time is required in order to make any transaction in this in ATM machine transactions OLTP is used for money withdrawals transfers deposit and inquiries right in supermarkets such as Big Bazaar people do payment with debit or credit cards executive uses OLTP to keep track of how many customers visited for the date right so now these were the example for OLTP as well as for OLAP now let us move forward and discuss about the parameters based on which we will make differences between OLAP and OLTP databases so first and foremost thing parameter is the motive to use right so OLAP systems provide consolidated view of historical data of an enterprise for reporting planning and decision making whereas royalty fee systems are used to manage day-to-day fundamental operations right next is the types of query in OLAP large and complex queries are used for making the decisions right in this aggregation of tables across multiple database is frequently required but in OLTP system simple and standard queries are used with returns only a few records right next is forms of query in OLAP transactions are not required to be recorded hence select command is used for fetching the data for analysis purposes whereas OLTP uses insert delete and update statements for the same right next is types of user so Olaf users are mostly knowledgeable workers business analysts c-suite and managers for running complex queries for making the analysis right whereas old TP is mostly used by the employees on the front line that has end users like clerks and caches in order to record and review the transactions now let's move forward to our next parameters which are normalization right so in OLAP data is T normalized to improve query performance when aggregation must be performed but in case of OLTP data is stored in third normal form that is 3 NM in order to facilitate simple queries next part that is storage so OLAP requires significantly large space as it is used to store huge amount of historical data and OLAP contains data from multiple sources and requires storage of aggregate structures and numerous indexes in order to optimize query performance but relatively smaller space is required in case of OLTP as it is based on current data only right now moving forward to backup and recovery so OLAP backups are really needed because it is built to persist data but in OLTP data is backed up regularly without fail because it stores all functional data for the business only now as we discussed about design part so in OLAP design changes according to the reporting subjects like Sales inventory marketing and so on so basically the design here changes according to the reporting subject only right and snowflake model is being used here to design this right but in case of OLTP design changes as per the industry requirements and ER model is used for designing here for example airline medical retail etc uses specifically designed OLTP databases accordingly so as as we discussed about the parameters for both OLAP and OLTP now let's move forward and discuss the advantages and disadvantages in OLAP and OLTP first and foremost advantages of using Ola olaf creates a single platform for all types of business analytical needs which includes planning budgeting forecasting and analysis and the major benefit of using OLAP is the consistency of information and calculations and the additional advantages OLAP systems apply security restrictions on users and objects in order to comply with regulations and protect sensitive data right now let us move forward and discuss about the disadvantages of using Ola so it has high IT dependency so IRA professions will be responsible for implementation and maintenance of OLAP systems only right although business analysts of decision makers are the intended users for Ola but they will still have to work with the IT professionals because the traditional OLAP tools requires a complex modeling procedure and its users have to write a huge number of complex codes or scripts or SQL queries right now the next point to note here is the slow response so it says that slow in reacting to the business analysis demands so guys how does it work that as traditional OLAP tools require pre modeling and cooperation between people from various departments for reporting and analysis to make the reports more effective so it is usually slow in reacting to the business analysis demands right last point that we have here for disadvantages as if Streck model so as the traditional OLAP tools convert the data of two-dimensional from database and Excel to the multi-dimensional right so for using the Olaf freely business analyst or decision-makers must have the knowledge of rotating slicing drilling and other concepts before using it hence you can say that the abstraction of model hinders the business personal from analyzing freely right now let us move forward and discuss about the advantages of using olt first and foremost thing is that OLTP is used for managing day-to-day transactions of an organization right and the queries used in this are simpler and short so it requires less time in processing and also requires less space compared to Ola by simplifying individual processes OLTP widens the customer base of an organization it also allows indexed access of data a large number of users can use OLTP at one time and frequent queries and updates can be done here it has fast response time compared to Ola right so as we move forward let us discuss about the disadvantages of using OLTP the first and foremost thing is that if hardware failure occurs in OLTP systems then online transactions get severely affected OLTP system allows multiple users to change and access the same data at the same time which many time created unprecedented situation due to the costly system design then maintenance in oil data systems and multiple users can access and modify the system data at the same time you can't restrict a one user to change data while another person is already modifying the data there must be an effective way to ensure that people are not working at cross-purposes right while retaining a system that is responsive for everyone so OLTP concurrency is the solution which is available in the form of OLTP software packages and that will require costly system design and maintenance also in online banking the online transaction systems impose processing costs on the buyers and sellers aspect so as as we discussed about the advantages and disadvantages now let us move forward and discuss the integration part that is integration of OLAP and OLTP so companies like SAP II data is developing the in-memory hybrid transaction analytics database that brings together OLTP OLAP and Apache spark or MongoDB Apache Cassandra in or to ease the pain of users right the purpose behind doing this is to bring the transactional and analytical environments together so companies like snappy data is developing the in-memory hybrid translational analytics database that brings together OLTP OLAP and Apache spark or MongoDB and Apache Cassandra in order to ease the pain of the users right the purpose behind doing this is to bring the transactional and analytical environments together right by using the integrated form of it one can run the queries really fast and can also provide estimation of error accurately here the integrated platform includes approximate query processing that is a QP and a QP uses machine learning techniques in order to understand the kinds of queries a user might ask the systems to create data samples and that can be helpful to improve query performance right and that's another benefit of using the integrated form is it minimizes the operation cost so guys now let's move forward which one of them according to you is better so as if I keep my perspective here OLAP is historical multi-dimensional data retrieval system that is used to retrieve the data for analysis which can be helpful in decision making right well as OLTP is an online data modification system so OLAP system creates a single platform for all types of business analysis needs such as planning budgeting analysis and forecasting oil taper systems are used for day-to-day transaction OLAP is characterized by a large volume of data whereas OLTP is characterized by the large number of short online transactions choosing one over another totally depends upon the users requirement as both work for different purposes right all right guys so now let me wrap up the session by asking you a question so which one of them does the data warehouse support so the options are OLTP OLAP OLAP and OLTP operational databases or none of these you can let us know the answer in the comment section below so guys I hope this video was helpful to you if you have any further queries do let us know in the comment section below we will reach out to you beat it so guys thank you so much for watching this video and giving us a precious time see you again yeah

Channel: Intellipaat

Views: 65,802

Keywords: olap vs oltp, oltp vs olap, what is olap, online transaction processing vs online analytical processing, what is oltp, difference between oltp and olap, olap vs oltp in data warehouse, olap vs oltp difference, olap vs oltp databases, oltp, olap vs oltp with example, olap vs oltp in sql server, olap vs oltp youtube, olap, online analytical processing definition, differences between oltp and olap, difference between olap and oltp, what is oltp & olap, olap and oltp, Intellipaat

Id: BQpCGyUzTHo

Length: 13min 14sec (794 seconds)

Published: Sat Sep 21 2019