Data Warehousing Tutorial Part - 2

Contrasting a Data Warehouse with an OLTP System

some of the key differences between a data warehouse's model and an OLTP system's.

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:

• 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.
• 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.
• 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.
• 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."
• 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.