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

Chapter 47. Loading a Dimension Table

If you have a data warehouse, you've probably been thumbing through this book looking for a way to load your dimension tables. Luckily, what used to take thousands of lines of code is now done with a simple wizard in SSIS. This Slowly Changing Dimension Wizard is a Data Flow object that takes all the complexity out of creating a load process for your dimension table. (Note: This lesson does not cover a step-by-step example on how to build a data warehouse from a design perspective because that is a book in itself.)

Before we discuss the Slowly Changing Dimension Wizard, you must understand a bit of terminology. The wizard can handle three types of dimensions: Type 0, Type 1, and Type 2. Each of these types is defined on a column-by-column basis.

• A Type 0 (Fixed Attribute) dimension column will not allow you to make updates to it, such as a Social Security number

• A Type 1 (Changing Attribute) dimension handles updates but does not track the history of such a change

• A Type 2 (Historical Dimension) dimension tracks changes of a column. For example, if the price of a product changes and it's a Type 2 column, the original row is expired, and a new row with the updated data is created

The last term you need to be familiar with because you see it in the wizard is inferred members (also called late arriving facts). These happen when you load a fact table and the dimension data doesn't exist yet, such as loading a sale record into the fact table when the ...