Data Warehouse vs Operational Database

From eBiz-Wiki

Jump to: navigation, search

Contents

Overview

When managing data it is highly important to ensure you make efficient use of the databases and that the data is organized and ready for access. There are two main types of databases used in most businesses, the operational database and the data warehouse. Though both very similar there are several differences that makes each one uniquely important to the business.

Definitions

Before comparing and contrasting both databases an understanding of each one should be established.

Operational Database

An operational database is the database that is accessed and updated on a continual basis and usually handles the daily transactions for a business. Operational databases use an OLTP approach and are designed to be write-optimized. (Nishith, 2005)

Data Warehouse

A data warehouse is a repository of an organization's electronically stored data. Data warehouses are designed to facilitate reporting and analysis and typically answer "who" and "what" types of questions. (Inmon, 1995) Data warehouses use an OLAP approach and perform in conjunction with the operational database on a constant basis. The main objective of a data warehouse is to provide prompt, efficient analytical reporting. This is achieved by ETL, extraction, transformation, and loading, in which the data is cleaned and organized into a meaningful manner. Data warehouses are designed to be read-optimized. (Nishith, 2005) The following diagram illustrates the flow of data for a data warehouse.


Data Warehouse database and OLTP database

Source: Exforsys, Inc., Data Warehouse database and OLTP database
http://www.exforsys.com/tutorials/msas/data-warehouse-database-and-oltp-database.html

OLTP vs OLAP

OLTP

On-Line Transaction Processing (OLTP) is the process in which systems facilitate and manage data entry and retrieval on a frequent basis. The transaction is almost immediately processed and is the main strategy of operational databases. An example of an OLTP system would be an ATM machine. The benefits of using OLTP is that it is fast and efficient and simplifies the process of accessing data. (Business Intelligence)

OLAP

On-Line Analytical Processing (OLAP) refers to systems that are used to answer analytical queries that are multi-dimension in approach. OLAP makes heavy use of data mining and relational reporting. (OLAP Council)

Similarities

Both an operational database and data warehouse are similar in structure. Both are databases that contain tables with data. Both databases also have keys, indexes, views etc.

Differences

There are several differences that separate an operational database from a data warehouse.

Benefits of a Data Warehouse

References

Inmon, W.H. Tech Topic: What is a Data Warehouse? Prism Solutions. Volume 1. 1995.

On-Line Transaction Processing, OLTP. Business Intelligence. [1]

OLAP Council White Paper (English). OLAP Council (1997). Retrieved on 2008-04-22. [2]

Database vs. Data Warehouse. Nishith. 2005. [3]

Personal tools
Namespaces
Variants
Actions
Navigation
Toolbox