- OLAP (On-line Analytical Processing) is characterized by relatively low volume of transactions. Queries are often very complex and involve aggregations. For OLAP systems a response time is an effectiveness measure. OLAP applications are widely used by Data Mining techniques. In OLAP database there is aggregated, historical data, stored in multi-dimensional schemas (usually star schema).
The following table summarizes the major differences between OLTP and OLAP system design.
|
OLTP System
Online Transaction Processing (Operational System) |
OLAP System
Online Analytical Processing (Data Warehouse) |
|
|
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 decisionsupport
|
|
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 datarefreshes 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
|
|
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
|










