Search

SSIS Slowly Changing Dimension (SCD) component performance unusably slow by BI Monkey

Closed
as Won't Fix Help for as Won't Fix

26
0
Sign in
 to vote
Type: Suggestion
ID: 632052
Opened: 12/16/2010 3:36:02 PM
Access Restriction: Public
1
Workaround(s)
The SSIS SCD transform is effectively useless when processing much more that 1000 rows of data, due to the RBAR approach of validating each row passing through.

This means most developers (where possible) end up using workarounds such as the T-SQL Merge or the codeplex component "Kimball SCD" (which is not usually acceptable in enterprise scenarios as it is not supported).
Details (expand)
Product Language
English

Category

Integration Services (DTS)

Proposed Solution

In order to improve performance there should be the ability to use a memory cached Dimension table for comparison, rather than checking against the physical table - this could be done in the same way as the Lookup Cache preloading.

Benefits

Improved Performance

Other Benefits

 
File Attachments
0 attachments
Sign in to post a comment.
Posted by Katie and Emil on 1/25/2012 at 2:23 AM
I must admit "microsoft" response made me laugh.... SSIS which is primary tool for building data warehouses does not provide a proper tranformation for THE most common task.

There was so much investment in 2012 DQS and MDS which are very important but I suspect only small % of people will start using it and it will take years before it goes mainstream but now we have to wait several years to get proper foundamental transformation in place (if we get it).

But I suspect the priority was to make rounded boxes :)
Posted by samtny on 1/4/2012 at 12:46 PM
"The SSIS SCD transform is effectively useless when processing much more that 1000 rows of data, due to the RBAR approach of validating each row passing through."

I have SSIS SCD's processing 50k+ rows in under a minute, which is I think quite tolerable. I had to add indexes on my business key/s to get this result. Also my attributes are Type 1 only, not sure if it makes a difference.
Posted by Cliff Buckley on 7/28/2011 at 12:40 PM
" If you feel that you could provide us with more data to change that judgment, we welcome any further information".
Try a google search, oh sorry Bing. It is generally agreed upon by SSIS Developers that quite frankly the SCD component is worthless. I haven't met a single developer who uses SCD over doing the work manually in stored procedures or using a lookup as a workaround. Please improve the performance or get rid of the component. If you can improve the lookup there is no reason you can't improve the SCD.
Posted by TheBiConsultant on 6/28/2011 at 3:45 AM
Are you people for real?? This is utterly important!
Posted by Microsoft on 2/16/2011 at 1:24 PM
Thank you for contacting us. After analyzing this issue and prioritizing it in the context of our current development plans, we have found regretfully that it does not meet the bar to be included in an upcoming product release. If you feel that you could provide us with more data to change that judgment, we welcome any further information. Your feedback continues to be invaluable to us and we take all your communication seriously. Please continue to contact us with any questions, suggestions and issues as they arise.

     The SSIS Team
Sign in to post a workaround.
Posted by SSISJoost on 2/22/2011 at 1:42 AM
Here are some alternatives:
http://microsoft-ssis.blogspot.com/2011/01/slowly-changing-dimension-alternatives.html

But I still hope Microsoft will improve the standard SCD component.
© 2012 Microsoft Corporation. All rights reserved. Microsoft Connect Terms of Use | Trademarks | Privacy Statement