Contrasting a Data Warehouse with an OLTP System?

Below figure illustrates some of the key differences between a data warehouse’s model and an

OLTP system’s. Figure Below Contrasting OLTP and Data Warehousing Environments

One major difference between the types of system is that data warehouses are not usually in third-normal form.

Data warehouses and OLTP systems have vastly different requirements.

Here are some examples of the notable differences between typical data warehouses and OLTP systems:

1. Workload. Data warehouses are designed to accommodate ad hoc queries. The workload of a data warehouse may not be completely understood in advance, and the data warehouse is optimized to perform well for a wide variety of possible query operations. OLTP systems support only predefined operations. The application may be specifically tuned or designed to support only these operations.

2.  Data Modifications. The data in a data warehouse is updated on a regular basis by the ETT process (often, every night or every week) using bulk data-modification techniques. The end users of a data warehouse do not directly update the data warehouse. In an OLTP system, end users routinely issue individual data-modification statements in the database. The OLTP database is always up-to-date, and reflects the current state of each business transaction.

3. Schema Design. Data warehouses often use denormalized or partially denormalized

schemas (such as a star schema) to optimize query performance. OLTP systems often use fully normalized schemas to optimize update/insert/delete performance, and guarantee data consistency.

4. Typical Operations. A typical data warehouse query may scan thousands or millions of rows. For example, "Find the total sales for all customers last month." A typical OLTP operation may access only a handful of records. For example, "Retrieve the current order for a given customer."

5. Historical Data. Data warehouses usually store many months or years of historical data. This is to support historical analysis of business data. OLTP systems usually store only a few weeks' or months' worth of data. The OLTP system only stores as much historical data as is necessary to successfully meet the current transactional requirements.