OLTP vs OLAP: Online Transaction Processing and Analytical Processing Differences
Advertisement
This page compares OLTP vs OLAP and mentions the difference between OLTP and OLAP. OLTP stands for Online Transaction Processing and OLAP stands for Online Analytical Processing. The OLTP systems have day-to-day transaction data which keeps changing, e.g., R/3 or other databases.
The best example of an OLTP system is an ATM machine, as shown in Figure 1 below.
In an ATM, transactions happen every day, and the data will be stored in the system called OLTP. From the OLTP, the data is sent to the OLAP system. From data stored in the OLAP, we will create the reports.
Image alt: OLTP vs OLAP example
The OLTP system collects the data from day-to-day transactions in an ATM machine. Hence, it contains current or present transactional data. OLTP will be sending this information to the OLAP system. The OLAP system does not contain present data, but it contains historical or old data. OLAP data is being analyzed in order to generate reports.
Generally, in a business organization, the activities are divided into two parts: online transaction processing and online analytical processing. The daily activities are collected and created, which falls under OLTP. These activities are stored in the database known as a data warehouse. Hence, it contains old or historical data. These systems are known as OLAP. The data stored in the OLAP systems is used for analysis purposes. OLAP systems are explored in the data mining domain for various applications. The input to OLAP systems comes from OLTP systems.
The following table summarizes the differences between OLTP and OLAP systems.
Features | OLTP | OLAP |
---|---|---|
Full form | Online Transaction Processing | Online Analytical Processing |
Characterization | Large number of short transactions online | Low volume of transactions |
Statements used | Insert, Update, Delete | Select |
Database type | Normalized (should be fast) | De-normalized |
Number of tables | More | Less |
Number of Indices or Indexes | Limited | More |
Type of system or source of data | Source System (data origination point, OLTPs are main source of data) | Target System (data to OLAP comes from OLTP databases) |
Used by whom? | End users | Business Analysis |
Function | Day-to-day transaction data | Analyzed data/reporting |
Queries | Simple, which returns few records | Complex, which involves aggregations |
Processing speed or latency | Usually very fast | It depends on the amount of data, can be few seconds to hours |
Space | Small | Large due to aggregate data structures and historical data |