What is Difference Between OLAP and OLTP ?

OLTP

 

Online transaction processing takes care of the day-to-day transaction related data.
The data keeps changing every day. The focus for OLTP is to be able to access every single record. Replicating the data is very difficult as OLTP has a very complex data model compared to OLAP.

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

 

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.

 

OLAP

 

Online analytical processing stores historic data which is useful for analysis and reporting. It contains snapshot and event (fact) tables.   Replication of data is easy here as the data model is simplified.

 

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.

 

I have listed below some of the basic differences between OLTP and OLAP:

Points	OLTP	OLAP
Speed	Fast	Depends on the data
Storage requirements	Less if old data is archived	Large
Data Source	Operational Data	From OLTP databases
Normalization	Highly normalized	Less normalized
Queries	One tuple at a time	Aggregate queries
Query type	Insert, update, delete	Select

 

 

1)      OLAP can be designed using the data from OLTP by using ETL processes

      2)      Data marts or data warehouse can be maintained as OLAP systems

      3)      OLTP is used for business reporting