This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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. Using the Slowly Changing Dimension Transform in SSIS Creating an SCD transform - type 0 (fixed attributes) Creating an SCD transform - type 1 (changing attributes) (this blog) Creating an SCD transform - type 2 (historical attributes) Inferred Members in Slowly Changing Dimensions The disadvantages of SCD transforms

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.

Using the Slowly Changing Dimension Transform in SSIS
Creating an SCD transform - type 0 (fixed attributes)
Creating an SCD transform - type 1 (changing attributes) (this blog)
Creating an SCD transform - type 2 (historical attributes)
Inferred Members in Slowly Changing Dimensions
The disadvantages of SCD transforms

Posted by Andy Brown on 05 July 2014

Creating an SCD transform - type 1 (changing attributes)

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

Create a new package.
Create a new data flow task within it.
Add an OLEDB source to take data from the vwPromotions view.
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 ... ?

Parts of this blog
Using the Slowly Changing Dimension Transform in SSIS Creating an SCD transform - type 0 (fixed attributes) Creating an SCD transform - type 1 (changing attributes) (this blog) Creating an SCD transform - type 2 (historical attributes) Inferred Members in Slowly Changing Dimensions The disadvantages of SCD transforms

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC