Differences between OLTP and OLAP

Differences between OLTP and OLAP


Online Transaction Processing vs Online Analytical Processing


Online Transaction Processing System (OLTP)

OLTP System deals with operational data. Operational data are those data  involved in the operation of a particular system.

Example: In a banking System, you withdraw amount from your account. Then Account Number, Withdrawal amount, Available Amount, Balance Amount, Transaction Number etc are operational data elements.

  • Operational Data
  • Operational data are usually of local relevance
  • Frequent Updates
  • Normalized Tables
  • Point Query

In an OLTP system data are frequently updated  and queried. So quick response to a request is highly expected. Since the OLTP systems invlove large number of update quiries, the database tables are optimized for write operations.

To prevent data redundancy and to prevent update anomalies the database tables are normalized.Set of tables that are normalized are fragmented.Normalization makes the write operation in the database tables more efficient.

Operational data are usually of local relevance.It involves Queries accessing individual tuple(individual record).These type of queries are termed as point queries.

Examples for OLTP Queries:

  • What is the Salary of Mr.John?
  • Withdraw Money from Bank Account : It perform update operation if money is withdrawed from account.
  • What is the address and email id of the person who is the head of maths department?

Online Analytical Processing (OLAP)

OLAP deals with Historical Data or Archival Data. Historical data are those data that are archived over a long period of time. Data from  OLTP are collected over a period of time and store it in a very large database called Data warehouse. The Data warehouses are highly optimized for read (SELECT) operation. Transactional data  are extracted from multiple OLTP sources and pre-proceesing is done on these data to make it compatible with the data warehouse data format.

Example: If we collect last 10 years data about flight reservation, The data can give us many meaningful information such as the trends in reservation. This may give useful information like peak time of travel, what kinds of people are traveling in various classes (Economy/Business)etc.

Analytical Query: Online Analytical Processing (OLAP) quries are of  analytical form. Query need to access large amount of data and require huge number of aggregations. It access large number of records from database tables and perform the required operation based on this.

  • Historical Data or Archival Data
  • Infrequent updates
  • Analytical queries require huge number of aggregations
  • Integrated data set with a global relevance

Updates are very rare in a Data warehouse.OLAP quiries will give aggregated information about the things happend in the past over a period of time and this will help the management in strategic decision making. Hence OLAP Queries are of  having  significant importance in strategic decision making.

Examples for OLAP Queries

  • How is the profit changing over the years across different regions ?
  • Is it financially viable continue the production unit at location X?