Compiled by Deepa Bhatia
Distinguish between Operational Database and Data Warehouse?
On a very basic level, the difference between the “operational” and the “data warehouse” database is in the way the database is intended to be used. The purpose of the operational database is to provide the ability to quickly store, retrieve, edit and delete the data as required in the daily operation of the business. The purpose of the data warehouse (or, its smaller version, called a “data mart”) is to store large amounts of the historical information and support analytical processing of this data. The data warehouses are populated either with the raw operational data, or some form of the aggregate extraction of the operational data.
Operational Database
|
Data Warehouse
|
It is based on transactional (OLTP) system
|
It is based on analytical (OLAP) system
|
It contains current data
|
It contains archival information
|
Updated on regular basis (daily)
|
Not necessary to update on regular basis (may be weekly or monthly)
|
It is record oriented and optimized for performance
|
It is optimized for queries to retrieve data for analysis
|
Used to control and run business task
|
Used to help in planning, analyzing and decision making
|
Fast inserts and updates
|
Periodic long running batch updates. Optimized for fast retrievals
|
Initiated by end users
|
Initiated by employees
|
Relatively small in size
|
Relatively huge in size
|
Highly normalized with many tables
|
Typically de normalized
|
Data can be updated as and when required
|
Usually a read only data
|
Usually the end users operation is the source of data
|
Consolidated operational data becomes the source for this database
|