Difference Between OLTP and OLAP

In the application world, there are two types of applications from the database perspective. Operational data and Historical data related to OLTP and OLAP applications respectively. In this article, we will you will learn the difference between OLTP and OLAP applications.

Operations Data (OLTP applications)

The operational data are those that “works”. It means these data are frequently updated and queried. The database is normalized for efficient search and updates. Therefore, you will not find update anomalies due to normalization process.

The operational data is stored on disks an typically suffers fragmentation, and it has local relevance. The data affected is in the same location that from a remote source.

The queries are very precise and access individual tuples. Most of them are transactional in nature.

OLTP vs. OLAP
OLTP vs. OLAP

Historical Data (OLAP applications)

Another set of data is historical data and it “tells” mainly gives “insight”. The updates to a database that store historical information need not update frequently. These are use by OLAP ( Online Analytical Processing) applications.

The data is accumulated from several sources and it has integrated data set with a global relevance.

The queries in such a system is analytical in nature, and therefore, requires huge amount of aggregation. Then there is performance issues during queries, not during updates as in the case of operational database.

Examples Of OLTP Queries

What is the salary of Mr. Mithra ?

What is the address and phone no. of the person in-charge of the finance department ?

How many people working in engineering department ?

Examples of OLAP Queries

How is the employee attrition scene changing over the years across the company?

Is there correlation between the geographic location of a company unit and excellent employee appraisals ?

Is it financially viable to continue our manufacturing unit in China ?

You can see a clear difference between the nature of queries in OLTP and OLAP. In next lesson, we discuss the dirty data and data warehouse.