Knight's Microsoft SQL Server 2012 Integration Services 24-Hour Trainer

Chapter 61

Fact Table Load

A fact table is generally much easier to load than a dimension table. Typically, you only insert into the table and don’t delete or update rows. Additionally, the components you use for a fact table load are much simpler than the Slowly Changing Dimension Transform you used in the previous lesson.

In a fact table load, the source data coming in contains the natural keys (also known as alternate or business keys) for each of the dimension attributes associated with the fact. You want to replace the business key with the key used in the dimension table. You look up the business key in the dimension table and retrieve the surrogate key (the dimension table’s primary key). Then the fact is stored with its dimension keys.

You may want to add additional, derived columns to the fact table. For example, you may want to provide consumers with a Profit column in the fact table, but your source data only has Cost and SellPrice columns, which you will bring into the fact table. These two columns, Cost and SellPrice are enough to determine profit. In the Data Flow Task you would create a Derived Column Transform that applies a formula in the expression, creating the new Profit column.

Another common task is summarizing fact data. Perhaps you have a requirement for a fact that contains ProductID, Date, and SaleAmount. Your source data for this fact contains an additional column—CustomerID. You will need to add up all of the SaleAmounts for each product, for each date, ...