Slowly Changing Dimensions - SQL Server Integration Services Design Patterns - page 352

Change
Type

Dimension
Type

When to Use

Historical
attribute

Type 2 Historical attributes are columns for which you need to maintain

history. These are frequently numeric columns that are used in
time-sensitive reporting queries, such as a Sales Price, or Weight.

On this page, you should not map columns that will not be updated as part of your
SCD processing, such as foreign keys to other dimension tables or columns related to
the tracking of historical changes—for example, Start and End Date columns, an expiry
flag, or the surrogate key. The SCD transform does not support Large Object (LOB)
columns (columns that would be treated as DT_IMAGE , DT_TEXT , and DT_NTEXT
types in the SSIS data flow), so these columns should be handled separately and also
should not be mapped here.
The next pages of the wizard allow you to configure options for how you'd like to
handle fixed attributes ( Figure 13-3 ), as well as Type 1 and Type 2 changes ( Figure
13-4 ) . When dealing with historical attributes, the wizard knows how to generate the
logic needed to update the dimension in two different ways: by using a single column
or by using Start and End Date columns to indicate whether the record is current or ex-
pired. If your table is using a combination of these, or some other method of tracking
the current record, you will need to update the generated transforms to contain this lo-
gic.