OLTP Vs OLAP-Database Vs Data Warehouse

OLTP Vs OLAP or Database Vs Data Warehouse is a difference that can be confusing to the beginners because at an abstract level they appear to be storage for data. Focus on word ‘appear‘ because in reality they are nothing like each other.

You can look at these two posts from our blog to learn about the basic definitions on database and data warehouse


Here’s how we will go through this article

  1. Online Transaction Process OLTP

    We will see what is OLTP , purpose of an OLTP and where can we find such systems

  2. Online Analytical Processing OLAP

    Purpose of OLAP , Reports and Decision making , purpose of OLAP and where can we find such systems

  3. Difference Between OLTP & OLAP


Online Transaction Process (Database/OLTP ) :

Any system that is responsible for handling the transaction and is always available to do so.

Online in OLTP means availability of the system to handle the transaction. Transaction is a single operation that could be update, insert , delete a record.

Detail article on Transaction go through this : Transaction & Concurrency

PURPOSE OF AN OLTP

Purpose of an OLTP system is to handle data . This can be anything from our CRUD operations. This means , data entry systems are perfect example of an OLTP. Any business in order to conduct its activities successfully is now relying heavily on keeping records of it business.

Restaurant may need to know about it customers, number of orders they receive and what items on menu card are being ordered abundantly. Billing the orders and about vendors who provide the raw material. In order to do that, a software is used which stores all the data in one place in a way that it facilitates the restaurant to keep track of everything.

At the end of month , restaurant owner can look at basic reports to know the volume of orders and what to buy in order to keep the menu items full and always available . Let me put OLTP this way :


OLTP is a software that makes the wheel of business turn

Take a look at this diagram and see how a database in a diagram looks like

OLTP Vs OLAP
How OLTP is designed
Taken from : Microsoft Blogs

Online Analytical Processing (Data Warehouse/OLAP)

Any system that is responsible for analysing the data efficiently and effectively and is always available to do so.

Online means always available and word Analytical can be as broad as you want it to be. Analytics can be Data Analytics , Data Mining , Business Intelligence reports use of machine learning and much more.

PURPOSE OF AN OLAP

OLAP is fundamentally used for analysing the data. When we say data , we mean large amount of data, heaps of data spread overs years and in complex form. This system is responsible for answering all sort of analytical perspectives in business.

Take that same restaurant owner. For example , he started from one branch and his business expanded rapidly and now instead of one branch he has 20 in 10 different cities in three different countries and a different menu in most of them. When he had one restaurant his report requirements were simple.

Over the years , his requirements became complex. His marketing team may want to know about customer types , locations. What cuisine of food is famous in which branches. They may want to introduce new dishes based on customer type in other branches or remove few dishes.

Purchasing department would want to know the quantities in which to buy the items. This can vary from season to season. Takeaways and dine-in differences could be a factor. Delivery cost and the areas which his restaurant should be covering based on customer type. What should be the prices according to the branches and countries. Scenarios can be unlimited .

You can imagine the complexity of the reports required. For such an OLAP system comes into play. Let me put OLAP this way :

OLAP is a software that watches wheels of business turn
OLTP Vs OLAP
How OLAP is designed
Taken from : Microsoft Power BI Blog

Remember OLTP handles data and it is specific for this purpose. Whereas, OLAP is for analytical purposes .OLTP runs on engines suited for handling transnational data and are not very good for analysing . OLAP runs on engines suited for analytical purpose and perform just okay when it comes for inserting or updating data.

You can see the difference between two by looking at the design they use in diagrams above.


Difference Between OLTP & OLAP

Database OLTPData Warehouse OLAP
Mostly UpdatesMostly Reads
Many Small TransactionsQueries are long and complex
MBs-GBs of DataGBs-TBs of data
Raw DataSummarised or reconciled data
Thousands of UsersDecision Makers.
Read, Delete , Create , Update operationsRead Operations Only
Relatively Low cost . Suitable for any size of organisationsHigh Cost. Suitable for Large organisations Only

Journey from OLTP to OLAP

OLAP cannot exist without underlying OLTP systems. An OLAP is made up by storing data from one OLTP or more than one OLTP systems. It combines data through ETL and transform it in a way that it can be utilised for complex report requirement which could take hours and hours to do via traditional tools. OLAP can do that in seconds. Take a look at the below diagram to see how the journey

Imagine for finding total numbers of orders and the dishes orders in all branches of the restaurant . User have to combine data from all 20 branches one by one and the create a suitable interface so that the restaurant management can understand it easily .

OLTP Vs OLAP
Journey from OLTP to OLAP

You could argue that we can create reports to do this in OLTP systems. I agree. You can do that. But the problem is that for this , you have to create reports in a fixed way. Any additional questions will require a separate report. For 10 different questions , you will have 10 different reports in 10 different places. This will be a nightmare for management to go through. Ad-hoc questions will almost be impossible to answer on the spot.

This compilation of data will take forever to reach management . As a result, by the time data is compiled . Situation will change in a fast world we live in.

On the other hand , if you have an OLAP or Data Warehouse system. Those 10 different questions can be combined in one screen using consolidated data available in it. Ad-hoc questions can be answered on the spot . You can drill-down the data or drill across the data anyway you like.

Conclusion On OLTP Vs OLAP

Choosing the right-tool for the right-job is very essential . You can either a choose a small hammer to bring down the 20 feet wall or you can bring the sledge-hammer or bad-ass drill machine to bring it down. Task will be done in both cases. Therefore , you have to decide what is suitable in your scenario and resources. OLTP-small hammer or OLAP-sledgehammer

Join 2,261 other subscribers

One Reply to “OLTP Vs OLAP-Database Vs Data Warehouse”

Leave a Reply