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.

Continue with  Data Warehousing Tutorial Part - 3 ..... :)