OLTP vs OLAP - Definition and Differences

We can divide IT systems into transactional (OLTP) and analytical (OLAP). In general we can assume that OLTP systems provide source data to data warehouses, whereas OLAP systems help to analyze it.

In Microsoft Dynamics AX 2012 database we can find an OLTP database, which stores the AX data and the AX Model store, and the SSAS database used for SSRS reporting. At the same time, the Microsoft SQL Server Analysis Services uses an on-line analytical processing (OLAP) engine that helps users analyze business data and identify trends.

In this post I would like to describe what OLTP and OLAP is, differences, and provide a day-to-day example about the two.

OLTP (On-line Transaction Processing) deals with operational data, which is data involved in the operation of a particular system and it is characterized by a large number of short on-line transactions (INSERT, UPDATE, and DELETE).

The main emphasis for OLTP systems is put on very fast query processing, maintaining data integrity in multi-access environments and an effectiveness measured by number of transactions per second.

In addition, in an OLTP system, the data is frequently updated and queried and to prevent data redundancy and to prevent update anomalies the database tables are normalized, which makes the write operation in the database tables more efficient.

Example: In a banking System, you withdraw amount through an ATM. Then account Number, ATM PIN Number, Amount you are withdrawing and Balance amount in account are operational data elements.

An OLAP (On-line Analytical Processing) deal with Historical Data or Archival Data, and it is characterized by relatively low volume of transactions.  In addition, the Queries needed for these systems are often very complex and involve aggregations as for OLAP systems the response time is an effectiveness measure.

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

The following table summarizes the major differences between OLTP and OLAP system design.

	OLTP System	OLAP System
Source of data	Operational data; OLTPs are the original source of the data.	Consolidation data; OLAP data comes from the various OLTP Databases
Purpose of data	To control and run fundamental business tasks	To help with planning, problem solving, and decision support
What the data	Reveals a snapshot of ongoing business processes	Multi-dimensional views of various kinds of business activities
Inserts and Updates	Short and fast inserts and updates initiated by end users	Periodic long-running batch jobs refresh the data
Queries	Relatively standardized and simple queries Returning relatively few records	Often complex queries involving aggregations
Processing Speed	Typically very fast	Depends on the amount of data involved; batch data refreshes and complex queries may take many hours; query speed can be improved by creating indexes
Space Requirements	Can be relatively small if historical data is archived	Larger due to the existence of aggregation structures and history data; requires more indexes than OLTP
Database Design	Highly normalized with many tables	Typically de-normalized with fewer tables; use of star and/or snowflake schemas
Backup and Recovery	Backup religiously; operational data is critical to run the business, data loss is likely to entail significant monetary loss and legal liability	Instead of regular backups, some environments may consider simply reloading the OLTP data as a recovery method