Knight's 24-Hour Trainer: Microsoft® SQL Server® 2008 Integration Services

Chapter 48. Loading a Fact Table

A fact table is generally much easier to load than a dimension table. Typically, you only insert into the table and don't update. 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 in the fact table. The first thing you do is perform a lookup against the dimension table to pass in the natural key (also known as the alternate key) and retrieve the surrogate key (the dimension table's primary key).

Additionally, you may have to apply a formula to the data prior to it being loaded into the destination. For example, your fact table may have a Profit column but your source data only has a Cost and SellPrice column. In that case in the Data Flow Task you would create a Derived Column Transform that applies a formula in the expression, creating the new Profit column.

The last type of example you see often is where you have to roll the granularity of the data up to a higher grain. For example, say you have a source file that includes columns for CustomerID, Date, ProductID, and SalesAmount but your fact table only includes ProductID, Date, and SalesAmount. In this case, you will want to roll the data up by using an Aggregate Transform. You would do a Group By ProductID and set the operation on Date to Max ...