question

Kumar Magesh avatar image
Kumar Magesh asked

SCD alternative

SCD has performance issue and eats up lot of memory. i need to use alternative to that transformation. can anyone suggest????
ssisslowly-changing-dimensiontransformation
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
According to [BOL][1] >**Slowly Changing Dimension Transformation** >The Slowly Changing Dimension Wizard and the Slowly Changing Dimension transformation are general-purpose tools that meet the needs of most users. However, the data flow that the wizard generates is not optimized for performance. >Typically, the slowest components in the Slowly Changing Dimension transformation are the OLE DB Command transformations that perform UPDATEs against a single row at a time. Therefore, the most effective way to improve the performance of the Slowly Changing Dimension transformation is to replace the OLE DB Command transformations. You can replace these transformations with destination components that save all rows to be updated to a staging table. Then, you can add an Execute SQL task that performs a single set-based Transact-SQL UPDATE against all rows at the same time. >Advanced users can design a custom data flow for slowly changing dimension processing that is optimized for large dimensions. For a discussion and example of this approach, see the section, "Unique dimension scenario," in the white paper, Project REAL: Business Intelligence ETL Design Practices. You can find that white paper at Also review these Technet sources - [SQL Server 2005 Integration Services: A Strategy for Performance]( http://technet.microsoft.com/en-us/library/cc966530.aspx) - [Integration Services: Performance Tuning Techniques]( http://technet.microsoft.com/en-us/library/cc966529.aspx) [1]: http://msdn.microsoft.com/en-us/library/ms141031.aspx--
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pavel Pawlowski avatar image
Pavel Pawlowski answered
you can try to use: [SSIS Dimension Merge SCD Component (Dimension Merge SCD)][1] or [SSIS Dimension Merge SCD Component (Kimball Method SCD)][2]. On SQL Server 2008+ you can use also the MERGE statement to implement the SCD which is the fastest method for merging the dimensions. [1]: http://dimensionmergescd.codeplex.com/ [2]: http://dimensionmergescd.codeplex.com/releases/view/25249
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I'm a huge fan of the second link (SSIS Dimension Merge SCD). Like everything Kimball-method, you can't jump into it willy-nilly, but if you are following a Kimball method design, Todd McDermid's control does an excellent job.
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.