Creating an SCD transform - type 1 (changing attributes)

We start in the same way as for the previous part of this blog.  That is:

1. Create a new package.
2. Create a new data flow task within it.
3. Add an OLEDB source to take data from the vwPromotions view.
4. Add a Slowly Changing Dimension transform.

This should leave you with this:

The data flow we'll start with. Time now to configure the SCD transform!

Configuring the SCD transform

Double-click on the SCD transform, and follow the steps through the wizard (passing straight through the first information message, if it's still appearing) as below.  Firstly, as before you are trying to load data into the DimPerson dimension table:

Choose to load data into the DimPerson table.

For the input columns, we're not interested in capturing when the first name or surname change (at least, not for this example), so remove these, and tell SSIS that the GradeName input column is the same as the Grade dimension column:

We'll dispense with the first name and surname columns, but we do want to capture changes in the grade.

So that SSIS can know which imported data row contains changes for which person, we need to specify which is the business key (usually this will be the unique key in the transactional database table);

The business key is the column which allows SSIS to match up rows.

You can specify lots of columns in an SCD transform for which you want to capture changes, and they don't all have to have the same SCD type, but here things are very simple:

Make the Grade column have SCD type 1.

On the next stage of the wizard, you would normally want to capture changes (it's surely the whole point of creating the SCD transform in the first place):

Tick this box to update the grade whenever a change is found in a person with matching PersonId.

Don't choose inferred member support:

We'll cover inferred members later in this blog.

Confirm your choices on the last stage of the wizard and finish.  SSIS will create for you a diagram similar to this:

The SCD wizard generates two additional tasks.

The left-hand path will add any people found in the imported data who don't already exist in the dimension table; the right-hand path will update the grades for those people who do already exist in the dimension table.

Running the SCD transform

Time now to run your transform!  This is what our dimension table looks like to begin with:

The initial dimension table.

Running the package should give this:

Four rows have been updated, according to this.

The resulting dimension table looks like this:

Rita has been promoted twice, but because no history is kept only the last one shows.

As a reminder, here was the view of changes which caused these updates:

The changes which we've passed on to the dimension table.

The drawback of type 1 SCD transforms

You'll already have spotted the obvious problem: we've lost any history of grades.  So if you want to know what Rita Smith earned in the first quarter of 2001, you can't.

Wouldn't it be nice if there was some way to capture the change history ... ?