Wednesday, September 2, 2009

SSIS - Kimball SCD Component vs. SCD Component

Some of you certainly know the performance issue of using the Slowly Changing Dimension (SCD) SSIS component.
With a small or medium amount of records, this component works fine but with a bigger amount of data, I’m speaking here about more than 10000 records (which is not huge J), this component “sucks”.

Here at Mobistar I encountered this kind of issue because I had to import more or less 80000 records per day.

By using the SCD component, after 40 minutes I decided to stop the loading and starting to find another solution.
By using the Kimball SCD component, the loading takes a bit more than 2 minutes.

The Kimball SCD component receives input from 2 sorted sources, compares the data and updates, inserts or deletes the records into the destination table. Furthermore, the Kimball SCD applies an insert or update against all rows at the same time. The normal SCD applies the update or the insert against a single row at the time. This is why the normal SCD is slower than the Kimball SCD.

I guess this component could interesting you a lot in your future developments.

Feel free to send me your experience with this component.

http://kimballscd.codeplex.com/

Kind regards,
Jérémie
PS: you can also have a look to http://www.sqlbi.eu/Projects/TableDifference/tabid/74/language/en-US/Default.aspx for information about another improved SCD component

1 comment:

Todd McDermid said...

Thanks for the review! Glad you are having good luck with the component - make sure you keep up with newer versions...