560 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
What does the SCD transform in SSIS do, and should you use it?
Part three of a six-part series of blogs
Integration Services include a Slowly Changing Dimension transform, which is designed to make loading dimension data easier. This blog explains how it works, and discusses whether it achieves its objectives.
We start in the same way as for the previous part of this blog. That is:
This should leave you with this:
The data flow we'll start with. Time now to configure 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.
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.
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 ... ?
|Parts of this blog|
25 Aytoun Street