In this SSIS Tutorial I will show you how to use Slowly Changing Dimension (SCD) Transformation and I will focus on Type 0 Fixed Attribute, there are several other related tutorials and videos that cover type 1 and 2 and other options . For the purpose of this tutorial I will use SSIS 2012
- SSIS 2012
- This video has been created using SSIS 2012
- SSIS 2008
- The examples we used should work in SSIS 2008
- SSIS 2005
- We suspect that this will work in SSIS 2005 as well
SSIS Slowly Changing Dimension (SCD) Transformation is used to help you implement Kimball Data Warehouse methodology related to SCD and this tutorial we cover Type 0 - Fixed Attribute which is a kind of dimension attribute that once populated should not change (with exception of inferred members).
In Visual Studio I have got OLE DB source with connection to AdventureWorksDW2012.
In OLE DB Editor I have got some SQL with data. I click Preview.
Preview Query has four columns (CustomerID, LastName, BirthDate, DateFirstPurchase) with one row.
In Management Studio I have got DimCustomer Which is the dimension we will use; I sorted it based on CustomerKey (Surrogate Key) and CustomerAlternateKey is our Business Key.
I drag Slowly Changing Dimension Transformation from toolbox and create path between source and SCD Transformation.
I double click SCD and I get welcome screen I click Next
I connect to AdventureWorksDW2012 and I select DimCustomer table. We have dimension columns which come from dimension from data warehouse and Input has some columns which have been mapped base on the column name and CustomerAlternateKey which is our BusinessKey and it maps to CustomerID which I selected manually. Business Key is the main aspect of slowly changing dimension based on the input and destination rows relationship is established based on Business Key and all the SCD logic is performed. I click next
In the Slowly Changing Dimension Columns I select the columns and in the Change Type we have Fixed attribute, Changing attribute and Historical Attribute. In this article we will focus on Fixed attribute which is type 0. On the left we have Fixed Attribute description which says: select this type when the value in a column should not change. Changes are treated as errors. I click Next.
In Fixed and Changing Attribute Options I select the Fail the transformation if changes are detected in fixed attribute.
I unselected the Enable inferred member support this option will be discussed in a separate article. I click Next.
and I click Finish.
So SSIS generated output with insert destination and I just run it.
We currently provide data that is exactly the same as in dimension so we get one row, Slowly Changing Dimension didn't detect any changes so output has no rows.
So let's see what happens if we change DateFirstPurchase to fourteenth. That is the fixed attribute so it should never change but let's say for some reason it changed.
Let's run the package again and it SCD fails because it detected a change in Type 0 - Fixed Attribute column.
So let's go through Slowly changing Dimension Wizard but I unselected the Fail the transformation if changes are detected in a fixed attribute.
So lets run the package again
Slowly changing dimension didn't failed but It didn't perform an Insert or Update. So If we decide to deselected that means that nothing will happened even if the value changed.
Performance: Build-in SCD is criticized for poor performance. If you want to make it run fast than check our SSIS SCD Checklist
I hope it helps