Using the Slowly Changing Dimension Transform in SSIS
This blog shows how you can use the Slowly Changing Dimension
transform in SSIS (hereafter mercifully abbreviated to SCD) to load
data into a dimension.
If you don't use cubes or Analysis Services, you can almost certainly stop
reading now and spare yourself a lot of pain!
One of my pet hates is training courses or blogs which use the AdventureWorks
database as a source of example data, as I think examples should be easy to
understand. With that in mind, let's create a sample relational database,
and then a dimension which we want to load.
Our example relational database
To use the data shown in this example, run the following script in SQL:
CREATE DATABASE WiseOwlScdBlog
GO
USE WiseOwlScdBlog
GO
CREATE TABLE tblGrade(
GradeId int IDENTITY(1,1) PRIMARY KEY NOT NULL,
GradeName varchar(50) NOT NULL
)
CREATE TABLE tblPerson(
PersonId int IDENTITY(1,1) PRIMARY KEY NOT NULL,
FirstName nvarchar(255) NULL,
Surname nvarchar(255) NULL
)
CREATE TABLE tblGradePerson(
GradePersonId int IDENTITY(1,1) PRIMARY KEY NOT NULL,
GradeId int NOT NULL,
PersonId int NOT NULL,
PromotionDate date NOT NULL
)
SET IDENTITY_INSERT tblPerson ON
INSERT tblPerson (PersonId, FirstName, Surname) VALUES (1, N'Rita', N'Smith')
INSERT tblPerson (PersonId, FirstName, Surname) VALUES (2, N'Bob', N'Brown')
INSERT tblPerson (PersonId, FirstName, Surname) VALUES (3, N'Sue', N'Jones')
INSERT tblPerson (PersonId, FirstName, Surname) VALUES (4, N'Wally', N'Owl')
SET IDENTITY_INSERT tblPerson OFF
SET IDENTITY_INSERT tblGrade ON
INSERT tblGrade (GradeId, GradeName) VALUES (1, N'Office junior')
INSERT tblGrade (GradeId, GradeName) VALUES (2, N'Associate')
INSERT tblGrade (GradeId, GradeName) VALUES (3, N'Senior Associate')
INSERT tblGrade (GradeId, GradeName) VALUES (4, N'Manager')
INSERT tblGrade (GradeId, GradeName) VALUES (5, N'Director')
INSERT tblGrade (GradeId, GradeName) VALUES (6, N'TAIAP God')
SET IDENTITY_INSERT tblGrade OFF
GO
This should create 3 tables. The two populated ones will contain a list
of 3 employees of a fictitious company, and a list of salary grades that they
might aspire to:
|
|
|
Our 4 people |
Our 6 possible grades |
The third table contains a table linking these two together, which I'm now
going to ask you to populate:
Right-click on the table called
tblGradePerson table to edit its contents.
Don't forget to refresh your databases and then
tables first!
I'm using SQL Server 2012 for this blog, but everything will work just as
well in SQL Server 2005, 2008, 2008 R2 or 2014!
Now just type in a few promotions:
Type in the four promotions above.
These represent the following promotions (clearly Rita is a bit of a star
performer, and the company has a strange policy of promoting people on
meaningful dates only):
|
Person
|
Grade |
Date |
|
Rita |
Associate |
New Year's Day, 2014 |
|
Sue |
Associate |
New Year's Day, 2014 |
|
Bob |
Associate |
New Year's Day, 2014 |
|
Rita again |
Senior Associate |
April Fool's Day, 2014 |
|
Wally |
Manager |
Christmas Day, 2013 |
Creating a view to combine tables
We'll want to be able to combine the tables we've created, so run the
following script to create a view:
USE [WiseOwlScdBlog]
GO
CREATE VIEW vwPromotions
AS
SELECT
p.PersonId,
p.FirstName,
p.Surname,
g.GradeName,
gp.PromotionDate
FROM
tblPerson AS p
INNER JOIN tblGradePerson AS gp
ON p.PersonId = gp.PersonId
INNER JOIN tblGrade AS g
ON g.GradeId = gp.GradeId
GO
SELECT * FROM vwPromotions
When you run this, it will create a view called vwPromotions,
and display the results of running this on screen:
The 5 promotions which have taken place.
Our example dimension
We now need a dimension to load, so run the following script:
USE WiseOwlScdBlog
GO
CREATE TABLE dbo.DimPerson(
SurrogateKeyId int IDENTITY(1,1) NOT NULL,
PersonId int NOT NULL,
FirstName nvarchar(255) NULL,
Surname nvarchar(255) NULL,
Grade varchar(50) NULL
)
SET IDENTITY_INSERT dbo.DimPerson ON
INSERT dbo.DimPerson (SurrogateKeyId, PersonId, FirstName, Surname, Grade) VALUES (1, 1, N'Rita', N'Smith', N'Office junior')
INSERT dbo.DimPerson (SurrogateKeyId, PersonId, FirstName, Surname, Grade) VALUES (2, 2, N'Bob', N'Brown', N'Office junior')
INSERT dbo.DimPerson (SurrogateKeyId, PersonId, FirstName, Surname, Grade) VALUES (3, 3, N'Sue', N'Jones', N'Office junior')
SET IDENTITY_INSERT dbo.DimPerson OFF
Excellent! We now have a dimension table:
What we now want to do is to
reload this table from our latest transactional data.
If you're worried about the fact that Wally Owl is nowhere to be seen, don't
be - he'll be brought in as an inferred row.
Time to get to the point of this blog, and create our SCD transform!
