Overview
- OLTP and OLAP are 2 data processing capabilities
- Understand the difference between OLTP and OLAP
Introduction
You acquire new information every day. But it is only after analyzing it that you can add it to your knowledge and make more informed decisions.. The same goes for all organizations in the world. Organizations collect large volumes of data and then process it to make sense and make the right business decision. Unlike humans, an organization has two types of data processing capabilities: OLTP y OLAP.
Contrary to its name, there is a big difference between the two. While one manages processes in real time, the other helps analyze large volumes of data to improve the organization's capabilities. Let's understand this difference between the two in a little more detail in this article..
Table of Contents
- What is OLTP?
- OLTP example
- OLTP Features
- What is OLAP?
- OLAP example
- Features of OLAP
- OLTP versus OLAP
What is OLTP?
OLTP, O Online transactional processing, systems handle a large number of transactions that occur in real time. But, What are the transactions?
Well, Proceedings are processes that occur in their entirety and isolated from each other. They insert, update or delete data in a database. In a successful run, changes made by a transaction in a database persist in the database even in the event of a system failure.
Transactional data is stored in Relational databases that they assure ACID properties for transactions. This data is written and queried at a very fast pace to avoid processing delays.
OLTP governs transactions because they are the critical processes that we encounter in our daily lives.. Online transactions, e-commerce orders, online hotel reservations, ATM transactions, etc., are managed by OLTP processes.
OLTP examples
Imagine logging into an e-commerce website to reserve the latest pair of your favorite headphones that are currently on sale.. Consider the following:
- Multiple people may be trying to reserve the headphones, but none is aware of the processes of the others. (Isolation)
- The order will be considered successful only when any user completes all the steps together with the payment. (Atomicity)
- Once a user successfully completes the order, will be updated in the website database. Then, headphones will no longer be available on the website. (Ccoherence)
- Now, even if the ecommerce website goes down due to a flood of user traffic, the user still owns the headset they successfully purchased. (Ddurability)
OLTP ensures that such transactions are carried out without inconsistencies in the database with the help of the ACID (atomicidad, consistency, isolation, durability) properties (that we just discussed).
OLTP Features
- Manage transactions in real time.
- These systems modify data in the database.
- Handle transactions that are governed by ACID properties.
- These systems store data in relational databases.
- The implementation of OLTP transactions is usually very fast, on the order of milliseconds.
What is OLAP?
Organizations have data generated from transactions stored in various OLTP databases. But this data is of no use unless it is analyzed to obtain valuable information for the organization.. But nevertheless, querying this data directly from OLTP databases is not efficient due to the large amount of data and the complexity of the queries that must be written. Therefore, we store this data in a different database called OLAP databases.
OLAP, O Online analytical processing, databases store data in aggregate form from multiple OLTP databases. Later, this data is stored in a data warehouse. But, instead of a transaction level view, provides a multidimensional view of the data. This means that if the organization wants to see the aggregated sales data, you can view them according to multiple categories and subcategories: Location (region, country, condition), time (year, my, day), the client (gender, age), etc. This enables organizations to perform advanced analysis of their data., giving them a deeper understanding of your products.
It goes without saying that the key to the success of OLAP databases is the multidimensional view.. But, How is it stored? Let's take an example to understand that.
OLAP example
Taking our previous example from the ecommerce website, imagine that the company now wants to analyze the sales of the past year. But just looking at sales per month is too simplistic. Considering sales per month categorized by region would be a better approach.
This table is known as Cubo OLAP with two dimensions (quarter and region) and the aggregated data stored here is known as measure.
The data stored in the data warehouses is in a similar way with as many dimensions as needed for the analysis.
Here, time, region and category are dimensions. The shaded cell of the cube shows the data for the month of February, in the North region for the Clothing category.
Features of OLAP
- This is historical data.
- These systems do not make changes to the data.
- Stores data in data warehouses in a multidimensional way.
- Used for data analysis purposes.
- The data is never modified.
OLTP versus OLAP
| OLTP
(Online transactional processing) |
OLAP
(Online analytical processing) |
|
| Functionality | Manage transactions that modify data in databases. | Used for analytical and reporting purposes. |
| Source | Real-time organization transactions. | Data is consolidated from multiple OLTP databases. |
| Storage format | Tabular form in relational databases. | Multidimensional shape in OLAP cubes. |
| Operation | Read and write | Read only |
| Response time | Fast processing as queries are simple. | Slower than OLTP |
| Users | Executives, data scientists | Programmers, database professionals |
Final notes
To sum up, in this article, we discuss the difference between OLTP and OLAP. It sure is a rudimentary topic, but essential to laying the foundation for a career in data engineering.
If you are looking for more articles of this type in the field of data engineering, I highly recommend the following items to start your journey:
I hope this article has been able to give a clear explanation to the subject. Let me know if you have any queries in the comments below.
