Problem
Within most BI projects you need to in some way handle slowly changing dimensions in your ETL solution. Be it Type 1, Type 2, they are unavoidable because truncating the dimension and fact tables and reloading from scratch is not an option unless the data warehouse is very small. On most of my projects I have struggled with how to handle Slowly Changing Dimensions in SSIS. This is not because I don’t know how to use them; it is because the Slowly Changing Dimension task in SSIS does not perform well with large datasets and is very difficult to use.
For example, if you want to change a column or add logic into a SCD using the SCD Wizard found in SSIS, everything you add after that component is recreated each time you make a modification to the task. This means that any changes to queries or mappings after the component will essentially be replaced and left unused within the package. This creates a lot of rework and retesting!
Due to all of this, I have found myself adding logic into SSIS packages to create my own SCD logic. Additionally, when using SQL 2008 I have used the Merge syntax to do the same thing by writing some complex SQL.
Solution
Rather than writing a bunch of custom SSIS or SQL, I offer this alternative:
http://kimballscd.codeplex.com/
It is a “Kimball Method SSIS Slowly Changing Dimension Component”. I have found this to be much faster than the SCD Wizard found natively in SSIS. For the most part, it gracefully handles all changes to the SCD and allows you to make modifications without breaking your ETL and wasting a bunch of time recreating SSIS data flows you have already developed once.
Download it, try it, and let me know what you think!
Within most BI projects you need to in some way handle slowly changing dimensions in your ETL solution. Be it Type 1, Type 2, they are unavoidable because truncating the dimension and fact tables and reloading from scratch is not an option unless the data warehouse is very small. On most of my projects I have struggled with how to handle Slowly Changing Dimensions in SSIS. This is not because I don’t know how to use them; it is because the Slowly Changing Dimension task in SSIS does not perform well with large datasets and is very difficult to use.
For example, if you want to change a column or add logic into a SCD using the SCD Wizard found in SSIS, everything you add after that component is recreated each time you make a modification to the task. This means that any changes to queries or mappings after the component will essentially be replaced and left unused within the package. This creates a lot of rework and retesting!
Due to all of this, I have found myself adding logic into SSIS packages to create my own SCD logic. Additionally, when using SQL 2008 I have used the Merge syntax to do the same thing by writing some complex SQL.
Solution
Rather than writing a bunch of custom SSIS or SQL, I offer this alternative:
http://kimballscd.codeplex.com/
It is a “Kimball Method SSIS Slowly Changing Dimension Component”. I have found this to be much faster than the SCD Wizard found natively in SSIS. For the most part, it gracefully handles all changes to the SCD and allows you to make modifications without breaking your ETL and wasting a bunch of time recreating SSIS data flows you have already developed once.
Download it, try it, and let me know what you think!
Hello Mike,
do you have any plans to update this so that it works with the latest versions of SQL server ?