Running the Wizard

Unlike other SSIS Data Flow components, when you drop the Slowly Changing Dimension Transform onto the design surface in BIDS, a wizard pops up and walks you through the steps of setting up your SCD processing.

The first page of the Wizard (Figure 13-1) allows you to select the dimension you’ll be updating, and select the column or columns that make up the business key (also known as the natural key).

On the next page (Figure 13-2) you specify the columns that you’ll be processing, and determine how you’d like the wizard to treat them. You have the three choices (as shown in Table 13-1).

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, such a Start and End Date columns, an expiry flag, and the surrogate key. The SCD Transform does not support 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 not 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; using a single column, or using start and end date columns to indicate whether the record is current or expired. 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 logic.

The final page of the wizard (Figure 13-5) lets you enable support for inferred members. An inferred member is created with minimal information, typically just the business and surrogate keys. It’s expected that the remaining fields will be populated in subsequent loading of the dimension data. Although the wizard enables inferred member support by default, most forms of SCD processing will not need it.

Using the Transformations

When the wizard completes, it will output a number of different data flow components in addition to the main “Slowly Changing Dimension” component (Figure 13-6). The main component checks incoming data against the destination table, and sends incoming rows down one of its outputs if the record is new or modified. Records without any changes are ignored. The components connected to these outputs will be configured according to the options you selected in the wizard dialogs.

You can further customize the SCD processing logic by modifying these components. Double clicking the main “Slowly Changing Dimension” transform will re-launch the wizard. The wizard remembers your settings from the previous run; however, it will overwrite any changes or customizations you’ve made to the existing transforms. This includes any layout and resizing changes you might have done.

Optimizing Performance

The components output from the SCD Wizard are not configured for optimal performance. By changing some settings and moving to a set based pattern, you can drastically improve the performance of your SCD processing.

Handling Type 1 Changes

Listing 13-1 shows the first MERGE statement we’ll run to update all of our Type 1 columns in the destination table. The ON ( ) section specifies the keys that we’ll be matching on (in this case, the business key for the table). In the WHEN MATCHED section, we include DEST.EndDate is NULL to ensure that we are only updating the current record (this is optional – in many cases you do want to update all records, and not just the current one). The THEN UPDATE section contains the list of our Type 1 columns that we want to update.

Listing 13-1.  MERGE Statement for Type 1 Columns

MERGE INTO [DimProduct] AS DEST
USING [Staging] AS SRC
ON (
        DEST.ProductAlternateKey = SRC.ProductAlternateKey
)
WHEN MATCHED AND DEST.EndDate is NULL -- update the current record
THEN UPDATE SET
         DEST.[ArabicDescription] = SRC.ArabicDescription
         ,DEST.[ChineseDescription] = SRC.ChineseDescription
         ,DEST.[EnglishDescription] = SRC.EnglishDescription
         ,DEST.[FrenchDescription] = SRC.FrenchDescription
         ,DEST.[GermanDescription] = SRC.GermanDescription
         ,DEST.[HebrewDescription] = SRC.HebrewDescription
         ,DEST.[JapaneseDescription] = SRC.JapaneseDescription
         ,DEST.[ThaiDescription] = SRC.ThaiDescription
         ,DEST.[TurkishDescription] = SRC.TurkishDescription
         ,DEST.[ReorderPoint] = SRC.ReorderPoint
         ,DEST.[SafetyStockLevel] = SRC.SafetyStockLevel
;

Handling Type 2 Changes

Since the MERGE statement allows a single statement for each action, updating Type 2 columns is a little more challenging. Remember, for Type 2 changes we need to perform two operations: 1) mark the current record as expired and 2) insert the new record as current. To accomplish this, we’ll use the MERGE inside of a FROM clause, and use its OUTPUT to feed an INSERT INTO statement (as shown in Listing 13-2)

Listing 13-2.  MERGE Statement for Type 2 Columns

INSERT INTO [DimProduct] ([ProductAlternateKey],[ListPrice],[EnglishDescription],[StartDate])
SELECT [ProductAlternateKey],[ListPrice],[EnglishDescription],[StartDate]
FROM (
        MERGE INTO [DimProduct] AS FACT
        USING [Staging] AS SRC
        ON ( FACT.ProductAlternateKey = SRC.ProductAlternateKey )
        WHEN NOT MATCHED THEN
        INSERT VALUES (
         SRC.ProductAlternateKey
         ,SRC.ListPrice
         ,SRC.EnglishDescription
         ,GETDATE() -- StartDate
         ,NULL -- EndDate
         )
        WHEN MATCHED AND FACT.EndDate is NULL
        THEN UPDATE SET FACT.EndDate = GETDATE()
        OUTPUT $Action Action_Out
         ,SRC.ProductAlternateKey
         ,SRC.ListPrice
         ,SRC.EnglishDescription
         ,GETDATE() StartDate
) AS MERGE_OUT
WHERE MERGE_OUT.Action_Out = 'UPDATE'