Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
548 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers 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
Search our website
We also send out useful tips in a monthly email newsletter ...
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 |
---|
|
Some other pages relevant to the above blogs include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.