Tuesday, July 29, 2014

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.

No comments :

Post a Comment