Oltp vs olap
Oltp (online
transaction processing )is a data modeling approach typically used to
facilitate and manage business applications. This kind of data are retrieved in
daily, weekly or monthly basis. Usually staff interact with this kind of
processing which include (insert ,update ,delete etc) . Short fast query are
retrieved from the database. The queries is not very complex and is east to
understand.
Olap (online
analytical processing) is a data modeling a approach used to analyses large
amount of historical data. Historical data are acquired in a large span of
time. Complex queries are needed to
retrieve data in olap.
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 decision support
|
|
|
|
|
|
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 data refreshes 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
|
|
Database Design
|
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
|
No comments:
Post a Comment