Difference between OLAP and OLTP
OLAP vs OLTP
Main Differences
|
OLAP
|
OLTP
|
Definition
|
OLAP is known as online analytic processing.
|
OLTP is known as online transaction processing.
|
purpose
|
The most important purpose is to plan, problem-solving, decision support.
|
The most important purpose is to run business tasks.
|
Queries
|
Queries of OLAP are often complex, therefore it is not much easier
|
OLTP queries are Relatively standardized and simple, therefore, it is easier.
|
Focus
|
Importantly the main focus is on Reporting and retrieval of information.
|
Importantly the main focus is on updating data.
|
Inserts and updates
|
It refreshes the data because of Periodic long running batch jobs
|
It does not need to refresh again and again because of Short and fast inserts
|
USE
|
It is mostly used for Querying processing
|
It is mostly used for transaction processing
|
Database design
|
It is based on Star and snowflake schema
|
It is ER-based and application-oriented
|
Users
|
This is used by Managers, executives and data scientists
|
OLTP is used by Clerk, DBA and database professionals
|
Data task
|
Main tasks are Reporting and data analysis
|
The operational task and business task
|
Processing speed
|
Complex queries may take many hours because it depends on the amount of data, batch data and
|
This is very fast because it does not depend on the data amount.
|
Data source
|
It mostly used Historical data
|
It uses mostly Operational data
|
Database Size
|
Database size is 100 Gb to Tb
|
100 Mb to Gb size of data
|
Access
|
Only read
|
Read/write
|
More Differences
Space requirements
|
It requires more space for historical data.
|
It requires less space to stored operational data.
|
Volume of transactions
|
Low volume of transactions
|
The high volume of transactions
|
Maintenance
|
It maintains information about the complete organization
|
It maintains data particular to a location or branch.
|
Data
|
It stores historical and old data.
|
It stores current and latest data.
|
Time period
|
It basically deals with year to year transactions or long.
|
It deals with day to day and short transactions.
|
Orientation
|
OLAP is market-oriented
|
OLTP is customer oriented
|
Storage form
|
It stores the data in dimensional
Form
|
It stores the data in relational form
|
Very useful for a short read, but I recommend for a more detailed vision as well for a an insight how both OLAP and OLTP can be related, to check out this article https://www.imaginarycloud.com/blog/oltp-vs-olap/ .