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
546 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 five 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.
|
In this blog
This tick box appears in every SCD transform wizard:
The tick box to enable inferred member support.
What does it mean?
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.
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:
And having explained at length what slowly changing dimension transforms are, it's time to consider whether you should be using them!
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.