Expert SQL Server™ 2005 Integration Services

5.4. Fact Table ETL Basics

The primary components in a fact table ETL process include acquiring the dimension surrogate keys, managing measure calculations, and identifying records for changes.

5.4.1. Acquiring the Dimension Surrogate Key

As you process fact source records (such as transactions or inventory), you must pull the surrogate key from the dimension. To accomplish this, you must have the business key from the transactional source that maps to the business key in the dimension table. This process of loading a dimension table involves looking up the surrogate key from the matching business key.

5.4.1.1. Type 2 Historical Changes and Late-Arriving Facts

If you are doing history on the dimension, it's important to pull the current surrogate key. In some situations, even the current surrogate key is not the most accurate association to make. Much like a late-arriving dimension, there's a situation called a late-arriving fact. Some systems hold a transaction for reporting until a pre-defined situation. In this case, the transaction may arrive in the ETL with an older transaction date than a strong majority of the records. When the related dimension record has gone through a Type 2 change since the transaction date, then the current dimension surrogate key would not be aligned with the transaction date of the fact record.

5.4.1.2. Missing Dimension Records or Inferred Members

Recall the Chapter 4 discussion on loading dimensions that reviewed the support for updating the ...