Data Warehouses

A data warehouse is a decision support system for business decision making, designed to execute queries from users as well as running reporting and analytical applications. It is also structurally different from an online transaction processing (OLTP) system, which focuses on operational transaction processing (we'll look at some of these differences in a just a moment). Because of these different purposes, both systems also have different workloads: a data warehouse will usually have to support complex and large queries, compared to the typically small transactions of an OLTP system.

Another main difference between OLTP databases and data warehouses is the degree of normalization found in them. An OLTP system uses normalized databases, usually at a third normal form, while a data warehouse uses a denormalized dimensional model. An OLTP normalized model helps to remove data redundancies, and focus on data integrity; it benefits update operations as data needs to be updated in one place only. On the other hand, a data warehouse dimensional model is more appropriate for ad hoc complex queries, and will usually have fewer tables and require fewer joins.