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 ...

Get Knight's Microsoft SQL Server 2012 Integration Services 24-Hour Trainer now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.