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

Section 10

Loading a Warehouse

• Lesson 60: Dimension Load
• Lesson 61: Fact Table Load

Chapter 60

Dimension Load

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 (SCD) 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 does not allow you to make updates to it, such as a Social Security number. Even if the source value changes, the change is not propagated to a fixed attribute.
• A Type 1 (Changing Attribute) dimension handles updates, but does not track the history of such changes.
• 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 is inferred members (also called late arriving ...