OLTP and OLAP, What are the differences?
I still remember the first time when I was introduced to the two buzz words, OLTP and OLAP; I found it very confusing. It was around 7 years back during my university days. But now, it’s a very simple concept for me. Through this post, I’ll try to make it simpler for all.
By definition,
OLAP stands for Online Analytical Processing and is used for data analysis and not data processing.
whereas
OLTP stands for Online transaction processing and is primarily used for data processing and not for data analysis.
OLTP is for running the business and OLAP is for analysing the business.
Why can’t we use OLTP for analysis?
Understand with examples
Let’s try to understand this by an example.
In the world of sports, let’s say cricket; Who is the best cricket player?
Or in terms of demographic data collection, In which state, the population is increasing? In which state, the male population is increasing? In which state, the female population is increasing?
For analysis, we need to study the data for a certain period of time. We will need to analyse the past data to answer the above question, data for the last 5–10 years, some historical observation is mandatory.
Also, we don’t need the entire data for analysis, instead only selective fields are required which can be helpful for analysis.
OLTP stores the real-time data but it does not store the historical data. The historical loading takes time and needs study of data for analysis.
If we use OLTP for both the purpose (also the work of OLAP), the OLTP will become very slow. The real-time data contains transactional data and the transactions are required to be extremely fast for great user experience within the application.
So, the industry introduces another database OLAP for analysis.
If someone asks like who scored maximum runs in today’s match? Then, OLTP is best suited.
But for the question like who scored maximum runs in ODI till date? Then, OLAP will be needed.
Source of Data
What is the source of data in OLTP? Interactions in applications and real-time transactions in the application.
The data is stored in OLTP via transactions in the application. For OLAP, the source of data is OLTP or file system. The data from OLTP or filesystem is moved to OLAP every day, every week or every month depending on the requirement. The process of moving data from OLTP to OLAP is the ETL process.
Extract from the source, the transformation of data and loading of data to a database. So, the transformation is critical as it happens to depend on the requirement of analysis. First, convert the data into some particular form like a date should be in some specific format etc. Then load the data into the warehouse.
This warehouse is the OLAP. Any Datawarehouse system is an OLAP system
Services make life easy
How this ETL is done? This is done using different tools like Informatica, Talend etc.
AWS or Azure have their services for all the purpose. For instance, in AWS we have services like RDS for OLTP, Redshift for OLAP and S3 for the file system.
The landing area for data from the application can be a file system or the database.
For analysing data from Warehouse, we use several BI or OLAP tools for reporting purpose like ClickView, Tableau etc. These tools are used for analysing and drawing reports, insights etc.
Examples for OLAP
- A company might compare its product sales in September with sales in October in different product categories like laptops, mobiles, TVs, then compare those results with another location which may be stored in a separate database.
- Amazon analyzes purchases by its customers to come up with a personalized homepage for a user with products suggestion powered by a better recommendation system.
Examples for OLTP
- Online banking like payment transactions
- Booking transactions like online train/airline ticket booking
- Sending a text message with meta details stored
- Order entry in e-commerce websites.
- Add a product to shopping cart
