OLAP vs OLTP | What is Difference Between OLAP and OLTP

OLAP vs OLTP :

In my previous article in have written the articles on Data-warehousing concepts. I would like to explain OLAP vs OLTP. The OLAP stands for Online Analytical Processing and OLTP stands for Online Transaction Processing. Before discussing OLAP vs OLTP we need to understand the concept of Data warehousing and why data-warehousing is important. In simple words, data warehousing is the system which use to store the data from multiple heterogeneous systems to one centralized system for reporting purpose. The key purpose of data-warehouse is to use the historical data as well as current data for reporting purpose.

What is OLTP?

In this section, I would like to give brief information about OLTP or Online Transactional Processing. The IT systems are divided into two different systems. The First is OLTP and another is OLAP. The OLTP system provides source data to the OLAP system. Following are some bullet points which will give the user the idea about the Online Transactional Processing System. (OLTP)

1. OLTP system has daily used a system which uses a large number of small transactions like insert, update and delete.

2. OLTP (online transactional processing) is a type of system which is supporting the day to day transactional systems.

3. We can call that Operational database as OLTP systems.

4. The OLTP data is a live data which provides the fast query processing. The Speed of data processing in the OLTP system measured in transactions per second (TPS).

5. The OLTP system also provides Data Security as well as data integrity.

6. The OLTP system data is in well-normalized form and it indicates the live data.

7. Real world uses of OLTP data :

It is used for financial transactions in bank, Order entry and live data processing like any government institutes data.

8. Usually, OLTP system uses the third normal form you can call it as 3NF of Normalization.

9. OLTP system maintains concurrency and it avoids the centralization so as to avoid the single point of failures.

10.Real Example :

If Company head wants the transactional report of all Employees In – Out time.

As Company head wants a daily report of in-out time we need to provide it using OLTP system. We need to schedule the report on a daily basis using OLTP system.

What is OLAP?

In this section, I would like to give you brief information about the OLAP which stands for Online Analytical Processing. OLAP system is used for mainly reporting purpose which contains the transactional data as well as the historical data. There are so many scenarios where the higher management needs historical information for analysis. Following are some bullet points which will give you the brief information about Online Analytical Processing(OLAP).

1.OLAP stands for Online Analytical processing which is used in many complex BI applications.

2.OLAP database always uses multidimensional database model which is into the star schema.

3. In the OLAP database, the data is coming from multiple heterogeneous data sources as it contains the historical data as well as current data.

4.OLAP is used for complex calculations, Trends Analysis, sophisticated data modeling.

5.OLAP database is stored in multidimensional database model.

6.OLAP system contains less number of transactions but complex calculations like aggregation- Sum,count,average,min,max e.t.c.The Aggregated data in OLAP system must be in months,quarters,years,weeks e.t.c.

7. The key purpose to use OLAP system is to reduce the query response time and increase the effectiveness of reporting. If these aggregated calculations are already stored in the repository and if the user wants fast access to data then the user can use OLAP system.

8.OLAP database stores aggregated historical data in multidimensional schema.

9.OLAP database uses the Star schema and hierarchy like structures to store the data.

10. Hierarchies are used to store the data and execute it faster than nonhierarchical data.

11.Real Example :

If Company head wants information about Resources salary in the year 2000.

In spite of using the transactional system, we will use OLAP system here where aggregated data of the year 2000 for Resources is already present.

OLAP vs OLTP in Tabular format :

In this section i will try to explain the OLAP vs OLTP in Tabular format:

Following Table shows difference between OLAP and OLTP system:

OLAP	OLTP
Data Storage: It stores only historical data and historical data processing is done.	Data Storage: It involves daily processing of data
Users Of System: OLAP System is used by higher management like managers , analysts , executives,CEO,CFOs	Users Of System: OLTP system used by DBAs, Database Professionals ,Programmers for applying business logic.
Key use: OLAP is used to analyse the business	Key use: OLTP is used to run the business
Optimization Techniques: OLAP is very huge database so lot of indexes are used for fast data processing	Optimization Techniques: OLTP uses less indexing as data is less here
Database Schema: OLAP uses Star-Schema,Snowflakes schema or Fact-Dimensions	Database Schema: OLTP uses Entity Relations
Data Information: OLAP contains historical data	Data Information: OLTP contains Current data
Join Data: OLAP has less joins and in de-normalized form database	Join Data: OLTP has large no of joins and in normalized form
Aggregated Data: OLAP system has aggregated multidimensional data	Aggregated Data: OLTP has not aggregated data.
Summarized data: OLAP system gives Summarized consolidated data	Detailed Data: OLTP system gives data in detailed format
Data Size: OLAP database size is 100 GB to 100 TB	Data Size: OLTP database size is 100 MB to 100 GB

These are above difference points of OLAP vs OLTP.Hope you like this article on OLAP vs OLTP. If you like OLAP vs OLTP article or if you have any suggestions with the same kindly comment in to comment section.

Amit S

Oracle Consultant with vast experience in Oracle BI and PL/SQL Development. Amiet is the admin head of this website who contributes by preparing tutorials and articles related to database technologies. He is responsible to manage the content and front-end of the website.