What is an olap and oltp?


What is an OLAP (Online Analytical processing)  and OLTP (Online transactional processing): IT systems are generally divided in to two types OLAP and OLTP. While OLAP are more analytical while OLTP are more transactional.  OLTP acts as source to data warehouse while OLAP systems help to analyze it. Data warehouse architecture consist of both OLTP as source and OLAP as destination of data.




Below are the measure differences between OLTP and OLAP


OLTP system

(Online transaction processing)

Operational System

OLAP system

(Online analytical processing)

Analysis system

Data Source Operational systems (e.g. ERP, Inventory management systems) Consolidated data from OLTP i.e. After applying ETL to OLTP
Data purpose To run Update,Delete and inserts Data analysis
Business purpose To run daily business tasks To analyze data for planning, problem solving and DSS
Insert and Updates Short and fast execution on daily basis by end user Periodic refresh of large data
Queries Relatively simple and short queries returning small result set. Very large and complex queries involving aggregation
Processing speed Very fast For complex queries can take hours. Indexes are created for faster execution
Space for data storage Very small if historical data is archived Large due to more aggregation and historical data. Indexes are more than OLTP.
Database design Normalized with number of tables Typically de-normalized ,using few tables designed by star schema or snowflake schema
Backup and recovery Done very neatly as operational data loss can lead to monetary loss Reloading of OLTP data is done instead of backup
Database type Relational Multi-dimensional database
Performance measurement Number of transactions per second Response time to queries

Leave a Reply

Your email address will not be published. Required fields are marked *