OLAP and OLTP — The two different data management techniques
Imagine the very early days of computer systems, there was no need of complex systems like RDBMS or Data Warehouses. In those days, data was stored in a single file and everything was quite straightforward to implement. However, with the evolution of Information Age, computers and software started to take our life by storm and they become the need of every business. With it started the era of Relational Database Management System (RDBMS) for the storage and management of an organization’s data. Till today, this is one of the effective ways of storing and extracting data within an organization.
Although, with the advent of Internet, we are no longer restricted to normal RDBMS systems. This is because the new age tools and apps store and access data in domains with no clear relationships and sometimes this is not even required (eg : to handle volatile data growth). In these scenarios we make use of NoSQL/MongoDB etc.
Nevertheless, a few terms of the old world are still used today, and it’s important to look at them with a modern approach. Two of them are, precisely, OLTP and OLAP. But for an overall context, let’s take a look at the following image that shows the relation between OLTP and OLAP.
From the image, you can clearly say that OLTP and OLAP are not competing approaches to the same issue, but processes that complement each other. Next you’ll find a more in depth explanation of each one of those terms.
OLTP
The term OLTP refers to Online Transaction Processing. It’s often used to mention databases that store and manage data relevant to the day-to-day operations of a system or company. An example of OLTP system is “ATM”.
As the information being stored on an OLTP data store was often critical to the business, a huge effort was put to ensure the Atomicity, Consistency, Isolation and Durability (ACID) of the data. Data stored according to these four principles are marked as ACID compliant, and this is where relational database management systems excel.
For eg : In our ATM machine, we need to ensure the principle of ACID which is an example of OLTP system. See the below example for clarity.
Let’s consider an example of two members of a family having two ATM cards linked to the same account and the account balance is $500 say. Now, if both of them go to withdraw the entire amount, then as per ACID principle, the first person should be able to withdraw and the second person should get the message of “Insufficient Funds”. In a second scenario, if the transaction of first person gets failed (say due to server failure), then the second person should be able to withdraw the money. In this way, the account information should always be up-to-date to avoid any malfunctioning.
But with the advent of web, we are no longer restricted to just the old definition of OLTP and now-a-days, we store the data on non-relational databases as well. Most of those data stores comply only with some of the four principles of ACID. But depending on the use case, it’s OK to relax on one or more of these principles in exchange for other benefits (speed, scalability, etc.).
So, say, if we have an app monitoring the number of registered users or likes on a particular product, we can make use of NoSQL or MongoDB with some of the principles of ACID and still consider it as an OLTP system.
OLAP
The term OLAP refers to Online Analytical Processing, and is often used to mention databases that store and manage data relevant for data analysis and decision making.
OLAP is strongly connected to Business Intelligence (BI), a specialization of software development targeted at delivering applications for business analysis. An example of OLAP system is financial reporting for different departments during different time-intervals.
The biggest advance that this area has brought is the capacity to generate reports on the fly. It ended the need to call the IT department to ask for a custom report, or to automate the generation of specific reports. A BI system can now answer questions that the developers didn’t had the need to know it in advance that the question was going to be asked.
BI systems are made possible by organizing the data in a form called Hypercube. This form explores the many dimensions of the data, and allows users to aggregate or drill down data by navigating the dimensions of the cube.
OLAP systems can be implemented using relational databases, and this technique is often named ROLAP (Relational OLAP). But for that, we need to design the database not in the 5th normal form but in the 3rd normal form.
We can live with redundant data when analyzing data. What really matters is the capacity to navigate through the dimensions of the data. And this is where ROLAP shines, as a database schema in the 3rd normal form is suited for aggregations and drill downs.
Conclusion
When encountering the terms OLTP and OLAP for the first time, it’s easy to question: which one is better? When in fact one should be asking: how does one complement the other?
We now know that:
- OLTP is used to store and manage data for day-to-day operations;
- OLAP is used to analyze that data.
