Knight's Microsoft® Business Intelligence 24-Hour Trainer: Leveraging Microsoft SQL Server® Integration, Analysis, and Reporting Services with Excel® and SharePoint®

Loading Dimensions

An integral part of a data warehouse is the dimension tables. If you have a data warehouse you’ve

probably been anticipating a section that will assist you in designing an SSIS package that will

load your dimension tables. Before SSIS, building the logic to load a dimension would require

thousands of lines of code. However, because SSIS includes a Slowly Changing Dimension Wizard

in the data flow, many of the challenges have been removed from designing and creating the

extraction, transformation, and loading of your dimension table.

If you don’t have an existing warehouse and are about to get started building, we would

like to provide you with a bit of terminology for slowly changing dimensions (SCDs). The

SCD task on the data flow of an SSIS package can handle Type 0, 1, and 2 dimensions. One

specific advantage of the task is that these types can be set on a column-by-column basis.

A Type 0 dimension, which is specified as a fixed attribute, does not make any changes to

the column. For example, you may want to ensure that all TaxIDs for a customer or vendor

remain the same; therefore, you would set the column as a fixed attribute. A Type 1 dimen-

sion (changing attribute) will update the value of a column, but does not track the history

of the change. Finally, a Type 2 dimension (historical dimension) will track column data

changes. For example, if the discount perc ...