Best Practice SCD Tool for Microsoft BI

Hi

Handling SCD logic in SSIS dimension load packages can be done in a number of different ways as suggested by many on the web:
1. Out of the box SCD Component
2. Using other Lookup Components
3. In SQL 2008 using the Merge Command
4. Using the Kimball/Dimensio Merge SCD Component from Codeplex and now via Pragmatic works Task Factory.

We started our project using option 4 (version 1.5) and apart from some minor glitches were relatively happy with the end result. Then we had to migrate to SQL 2008 and found that there was no clean migration of the inner workings of the component from SQL 2005 to SQL 2008. A combination of the effort involved to redo all the dimension packages using the SQL 2008 version of the component together with the move over to a paid version of the product caused us to make a call to use the MERGE approach instead. It would take the same amount of time to redo the wizard than develop a stored procedure per dimension to do the SCD via the MERGE. We use more or less the same code logic as written about by Warren in one of the design papers on this site.

I would like to know which method the Kimball group suggests for SQL 2008 and even SQL 2012. Our new approach is to stay away from 3rd Party Dependencies and rather go for an approach that is part of the main BI toolset that we are using.

Thanks

I always recommend that people start with the Microsoft component; it's easy, it's reasonably functional, it requires no dependencies on third party components, and it's zero incremental license cost. It meets most projects' needs. No, it's not perfect.

The main reason a project would use a different approach is performance, which is not even remotely a strength of the Msft component. Options 4, 3, and 1 could easily beat the performance of the Msft component. The PragmaticWorks component is the nicest (supported) option that I've seen. I have no issues with it, other than taking a dependency on a (small) third party company.

My personal preference is to do this work in the SSIS data flow, rather than use the MERGE statement. That's because the error flow architecture in SSIS lets us handle errors in an elegant and complex way, in a single pass through the data. That said, if your error handling is simple, or your data volumes are particularly huge, the MERGE approach in many cases could be the best performing. Remember though that you have to calculate the performance comparison (relative to one of the options within the data flow) as:
- Stage the data to a table
- Possibly index the staging table
- Do the MERGE
- Do the error handling (RI violations)

Good luck.

Thanks for the reply Joy.

We were also relatively happy with the 3rd Party component until we tried to migrate from SQL 2005 to SQL 2008. Our error handling is dealt with before we do the SCD so the MERGE for us works fine.