Expert SQL Server™ 2005 Integration Services

4.1. Dimension ETL Overview

Arguably, when looking at the development time investment required for a data warehouse ETL solution, dimension processing takes the longest, and is the most complex component. You may have experienced this, especially when the requirements call for tracking the history of changes that a dimension goes through.

Does this analyst requirement sound familiar: "I want to be able to look at the history of list price changes for a product to be able to understand the supply and demand curve"? In other words, what the business user is really asking is how the change in list price affects sales historically. To handle this type of request, a single product must have a new version created in the dimension table any time the list price changes. Furthermore, the sales activity (tracked in the appropriate fact table) must be associated with the right dimension record for the sales (the one with the list price at that point in time). Sound complicated? Add to that a second or third dimension attribute change (like department) that must be tracked historically, and pretty soon you will be pulling you hair out!

The good news is that SSIS comes with out-of-the-box functionality to handle dimension scenarios just like this. Specifically, it comes with a data flow transformation called the Slowly Changing Dimension (SCD) Wizard. As you will see, there are advantages to using the wizard, as well as some limitations you must consider, and ways to work through those limitations ...