BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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)
- Creating an SCD transform - type 2 (historical attributes)
- Inferred Members in Slowly Changing Dimensions (this blog)
- The disadvantages of SCD transforms
Posted by Andy Brown on 05 July 2014
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Inferred Members in Slowly Changing Dimensions
This tick box appears in every SCD transform wizard:
The tick box to enable inferred member support.
What does it mean?
First, another change to our dimension table
To illustrate inferred dimension members, run this script to change your DimPerson table:
-- get rid of old copy of dimension table
BEGIN TRY
DROP TABLE DimPerson
END TRY
BEGIN CATCH
END CATCH
-- recreate the dimension table, with inferred column
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,
IfInferred bit NOT NULL DEFAULT 0
)
-- add in 3 people
SET IDENTITY_INSERT dbo.DimPerson ON
INSERT dbo.DimPerson (
SurrogateKeyId,
PersonId,
FirstName,
Surname,
Grade,
IfInferred
) VALUES (
1, 1, N'Rita', N'Smith', N'Office junior',
0
)
INSERT dbo.DimPerson (
SurrogateKeyId,
PersonId,
FirstName,
Surname,
Grade,
IfInferred
) VALUES (
2, 2, N'Bob', N'Brown', N'Office junior',
0
)
-- 3rd person is an inferred row
INSERT dbo.DimPerson (
SurrogateKeyId,
PersonId,
FirstName,
Surname,
Grade,
IfInferred
) VALUES (
3, 3, NULL, NULL, NULL,
1
)
SET IDENTITY_INSERT dbo.DimPerson OFF
go
[RecreateDimPerson]
-- show what we've created
SELECT * FROM DimPerson
This is what you should get:
The third person exists just as a placeholder.
The idea behind inferred members is that you can load placeholders for dimension members, so that a slowly changing dimension transform will modify existing rows, rather than adding new ones.
Creating and running the inferred member transform
To enable inferred member support, go through one of the procedures described in the previous 3 parts of this blog to create an SCD transform, and configure the penultimate stage of the wizard as follows:
Here we'll assume that a dimension table row is an inferred member if the Boolean column IfInferred says that it is.
When you run your package, you can see that it shows what is happening:
The numbers of rows are explained below.
Here's what's happening:
- Wally Owl (person number 4) isn't found in the DimPerson table, and is added as a new output (the bottom path above).
- Person number 3 is found in the DimPerson table as an inferred member, and so her details are updated from the fact table (the middle path above).
- The other people (numbers 1 and 2) are found in the dimension table, and their details are updated in the usual way (the top path: there are 3 rows because Rita undergoes two promotions).
And having explained at length what slowly changing dimension transforms are, it's time to consider whether you should be using them!
- Using the Slowly Changing Dimension Transform in SSIS
- Creating an SCD transform - type 0 (fixed attributes)
- Creating an SCD transform - type 1 (changing attributes)
- Creating an SCD transform - type 2 (historical attributes)
- Inferred Members in Slowly Changing Dimensions (this blog)
- The disadvantages of SCD transforms