Data Warehouse Objectives

A data warehouse or data mart, depending on its size and scope, is a collection of important business data, usually obtained from different sources. For the purposes of this chapter, these terms will refer collectively to any type of decision-support database as a data warehouse.

Raw data or operational transaction data represent the raw material for the information required and desired for a business intelligence solution. Operational data has many characteristics that make it a poor option for direct access within a business intelligence solution architecture. Some of these characteristics include:

• Volatile, changing data values.

• Data that is not maintained for historical reporting.

• Data that is stored only at the detail level, requiring all grouping and aggregation to be performed in live queries.

• Multiple business operational systems not integrated and that must be correlated to expose visibility of the complete business context.

• Proprietary data formats that might be difficult to navigate and access.

These are the primary reasons it is necessary to extract and transform the raw data to be managed into data warehouses. SQL Server 2005 Integration Services provides the ability to extract, transform, and load (ETL) as well as maintain data warehouses. The first consideration of business intelligence (BI) implementation will require access to or creation of a data warehouse that contains the data transformed into BI formats and structures.

One of the most significant differences between a data warehouse and an operational database is that, in a data warehouse, the data isn't perpetually changing. A data warehouse is a read-only copy of integrated operational data. Unlike a transactional or operational system that might be in a state of flux from time to time, the data warehouse is consistent and reliable. If you were to run a report against the live operational invoicing system, you might be reading newly added account changes before credits or adjustments have been applied. If your timing is just right, you might also report on data in the middle of a maintenance routine. Perhaps a group of records has been deleted before correcting records have been inserted or after duplicate records have been added before an operation is completed. Given all of the day-to-day operations in a large business, there is a very good chance that something somewhere in the database will be out of balance.

Operational systems are also big and complicated. To maintain transactional consistency, it is necessary to store records in several different tables related through primary and foreign key relationships. To query even the most simple business information requires several table join operations. These complex queries can be slow and difficult to design and debug. By contrast, a data warehouse structure is simplified and doesn't typically store the same level of detail, which would otherwise be unnecessary for analytical reporting.