How Is a Data Warehouse Really Different from a Normal Database

http://nexus.realtimepublishers.com/content/?tip=how-is-a-data-warehouse-really-different-from-a-normal-database

by Don Jones

An operational data store (ODS), which you may also call an Online Transaction Processing (OLTP) or transactional database, has several key features:

• It contains detailed information. For example, it not only contains summary information such as the total amount of an order but also detailed information such as how much each item costs.
• It is designed to process transactions, meaning it’s typically dealing with one piece of data at a time: one order, one product, one customer. It may be used to generate basic reports from this data, but it’s structure is optimized to support rapid access to small chunks of data.
• The schema is rigid and unchanging.
• It contains up-to-date information, and is updated in real-time.
• The quality of input data is often very high, meaning applications and other elements ensure that correct data goes into the data warehouse.

A data warehouse, in contrast, often includes these features:

• Some data may be summarized, meaning detail is not available. You may be able to tell the total amount of a given order but not the cost of each product contained in the order.
• Its purpose is to drive analysis and decisions. Access is usually for large quantities of data, in order to see trends.
• The schema may be loosely structured and may change over time to support different analysis scenarios.
• The information is historical and may not be entirely up to date. The emphasis is on past data and trends, more so than immediate, real-time data.
• Data is often “cleaned,” meaning errant or “edge” data may be removed to make trends clearer. Data warehouses tend to focus on sets of data rather than on individual elements.

Companies with a data warehouse will always have one or more “normal” databases that feed the data warehouse.