We know that OLTP has also been
used to refer to processing in which the system responds immediately to user
requests. In OLTP databases, there are detailed and current data, and schema
used to store transnational databases is the entity model.
OLAP stands for On-line Analytical Processing which was
described by Edgar Codd in 1993. OLAP based systems are used for management
decisions and these system known as Decision
Support System (DSS). OLAP-applications operate with large amounts of data
accumulated in the operational databases OLTP. Typical applications of OLAP
include business reporting for sales, marketing, management reporting, business
process management (BPM), budgeting and forecasting, financial reporting and
similar areas.
Collaboration of OLTP and OLAP systems
Following work flow structure is capable to show the importance of the each system based application and how are they link with each -
Major
differences between OLTP and OLAP
Contents
|
OLTP System
|
OLAP System
|
Source of data
|
Operational data; OLTP databases are meant
to be used to do many small transactions, and usually serve as a "Single
Source Of Truth"
|
Consolidation data; OLAP data comes from
the various OLTP Databases which is used for analytics, data mining, less
queries but they are usually bigger (they operate on more data).
|
Purpose of data
|
To control and run fundamental business tasks, facilitate and
manage transaction-oriented applications
|
To help with planning, problem solving, and decision
support, report writing and data mining.
|
What the data
|
Reveals a snapshot of ongoing business processes, used to
refer to processing in which the system responds immediately to user
requests.
|
Multi-dimensional views of various kinds of business
activities to help out the better decision making.
|
Inserts and Updates
|
Short and fast inserts and updates initiated by end users.
|
Periodic long-running batch jobs refresh the data by the help
of ETLs processes.
|
Queries
|
Relatively standardized and simple queries Returning
relatively few records which are not more than 2 years
|
Often complex queries involving aggregations which are based
on the large volume of the data more than 2 years.
|
Processing Speed
|
Typically very fast and always hit the main databases.
Provides primitive and highly detailed data.
|
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. Provides summarized and consolidated data.
|
Space Requirements
|
Can be relatively small if historical data is archived. Mostly
Database size is from 100 MB to 100 GB.
|
Larger due to the existence of aggregation structures and
history data; requires more indexes than OLTP. Database size is from 100 GB
to 100 TB
|
Database Design
|
Highly normalized with many tables and provides high
performance.
|
Highly flexible. 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
|
Please visit to know more on -
