Data Warehouse vs Operational Database
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.
Before comparing and contrasting both databases an understanding of each one should be established.
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)
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.
OLTP vs OLAP
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)
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)
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.
There are several differences that separate an operational database from a data warehouse.
- A data warehouse is usually located on a separate system to increase performance.
- Operational database is designed for real time business operations while data warehouses are designed for analysis of business measures.
- Data warehouses are optimized for bulk loads and large complicated queries while operational databases are designed for common single-line transactions.
- Data warehouses require no real time data validation while operational databases uses data validation tables on a consistent basis.
- Data warehouses support few concurrent users while operational databases support thousands of concurrent users.
Benefits of a Data Warehouse
- Data warehouses allow for operational databases to offload data as to not slow the system.
- Separation of operational database from data warehouse allows for scalability
- Allows for direct increase in quality of analysis
- Reduced cost-per-analysis
Inmon, W.H. Tech Topic: What is a Data Warehouse? Prism Solutions. Volume 1. 1995.
On-Line Transaction Processing, OLTP. Business Intelligence. 
OLAP Council White Paper (English). OLAP Council (1997). Retrieved on 2008-04-22. 
Database vs. Data Warehouse. Nishith. 2005.