Expert SQL Server™ 2005 Integration Services

5.1. Fact Table Overview

Instead of containing attributes about an entity as dimension tables do, a fact table contains the metrics or numbers that the report presents and that the cubes aggregate, as well as trends. Measures include sales amount, account balance, discount amount, and shipping quantity. Figure 5-1 shows the Internet sales fact table, which contains several measures related to the direct sales to consumers, such as OrderQuantity, UnitPrice, ExtendedAmount, and so on.

In addition to the measures, a fact table also contains the relationships between the measures and the dimensions. As Figure 5-1 shows, the fact table contains the foreign key relationship from the dimension tables. Essentially, the primary key in the dimension table is the dimension's surrogate key. The surrogate keys from the related dimension tables are included in the fact table. This fact table also contains degenerate dimension values and lineage columns, which often overlap, to provide the transaction IDs to allow tracing back to the source data and filtering by transaction numbers.

Figure 5-1. Internet sales fact table

The three types of columns in the fact table are dimension keys, measures, and metadata. These columns map directly to the ETL processing requirements for fact table ETL, mapping dimension keys, calculating measures, and adding metadata.

5.1.1. Mapping Dimension Keys

Data warehouse ...