Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Microsoft SSIS BIDS SCD Type 2 Development

Go down

Microsoft SSIS BIDS SCD Type 2 Development Empty Microsoft SSIS BIDS SCD Type 2 Development

Post  blynch Fri Mar 09, 2012 2:47 pm

Are there thoughts on which path would be best to take in terms of which tool to use for Type 2 SCDs?

So far I see:
1) Microsoft's canned transformation.
2) Hand building transformations and T-SQL.
3) Dimension Merge SCD transformation from codeplex.

I am having a little trouble with the Dimension Merge transformation in terms of specifying Expiry Timestamps and it runs successfully once with row expiration and such, but then it fails the next time and just inserts all the rows as new again; thereby errantly ballooning the dimension table.

Thank you for your insights.

blynch

Posts : 18
Join date : 2011-10-16

Back to top Go down

Microsoft SSIS BIDS SCD Type 2 Development Empty Re: Microsoft SSIS BIDS SCD Type 2 Development

Post  Mike Honey Tue Mar 13, 2012 12:03 am

Hi blynch,

I've built several reliable and reasonably performant solutions on "Microsoft's canned transformation". Here are the caveats:
- doesn't properly handle multiple Business Keys in one execution (only detects one change) - need to filter out duplicates or loop through them
- need to manually tweak the SqlCommand property after running the wizard to avoid considering non-Current rows (e.g. WHERE Row_Is_Current = 'Y')
- tweak the Insert Destination to Fast Load if supported (but don't lock the table)
- can typically handle up to 500,000 dimension change candidates in an hour or so on a commodity server
- Business Key and Row_Is_Current columns in target dimension need an index
- ensure column names in data flow match target table for automatic matching - usually easiest to implement in your Data Flow Sources / Lookup SQL e.g. PRODCOD1 AS Product_Code

On the plus side, the SCD component is very quick to develop, test and maintain - zero code. Considering the volume and complexity of code required for alternatives, that's a huge plus in my book.

Good luck!
Mike
Mike Honey
Mike Honey

Posts : 185
Join date : 2010-08-04
Location : Melbourne, Australia

http://www.mangasolutions.com

Back to top Go down

Microsoft SSIS BIDS SCD Type 2 Development Empty Re: Microsoft SSIS BIDS SCD Type 2 Development

Post  blynch Thu Mar 15, 2012 9:21 am

I want to thank you for your very helpful reply. I am still keeping the Microsoft transform in mind. The tone at the Kimball classes (not an endorsement) was that this transform from codeplex had a lot of pros over the Microsoft transform; though what exactly in practice I am still learning.

I do find that this codeplex transform works best if you let it manage all of the fields and not insert any Derived Columns after it. That seems to make it upset.

Also, I find that I am not able to test it during the same day. So I have to load, wait a day, load and test the SCD flows. Makes it a bit slower to develop.

I'm curious how the Microsoft SCD transform performs in this regard.

blynch

Posts : 18
Join date : 2011-10-16

Back to top Go down

Microsoft SSIS BIDS SCD Type 2 Development Empty Re: Microsoft SSIS BIDS SCD Type 2 Development

Post  Mike Honey Tue Mar 20, 2012 7:57 pm

Hi blynch,

I'm quite sure what you are asking - can you clarify?

If you mean how the Start & End Dates work in the Microsoft SCD, they get loaded with a full date-time stamp. The wizard generates a Derived Column transformation that references the system variable @[System::StartTime]. So you can run multiple executions in one day.

Mike

Mike Honey
Mike Honey

Posts : 185
Join date : 2010-08-04
Location : Melbourne, Australia

http://www.mangasolutions.com

Back to top Go down

Microsoft SSIS BIDS SCD Type 2 Development Empty Re: Microsoft SSIS BIDS SCD Type 2 Development

Post  blynch Wed Mar 28, 2012 2:29 pm

I've seen posts (on codeplex I think) where the developer says that the Dimension Merge component (from Codeplex) is not designed to be ran more than once on the same day.

This seems to make development a little slow if I am understanding it correctly.

If you do run it more than once on the same day, it erroneously creates a bunch of "New" records that should be either "Unchanged" or "SCD 1 Update Rows".

I have since confirmed the transformation works better if I only run it once per day. I'm a little confused as to why.

It sounds like you would recommend the Microsoft SCD transformation, correct?

Thank you for your insights.
Brad

blynch

Posts : 18
Join date : 2011-10-16

Back to top Go down

Microsoft SSIS BIDS SCD Type 2 Development Empty Re: Microsoft SSIS BIDS SCD Type 2 Development

Post  Mike Honey Fri Mar 30, 2012 7:52 am

Hi Brad,

I've never tried the Dimension Merge component - that limitation put me off and I generally try to stick to the out-of-the-box components if I can. The Microsoft SCD component does seem to work fairly reliably and predictably (within the limitations I outlined previously in this thread).

On the plus side, it handles most SCD scenarios with no coding required.

Good luck


Last edited by Mike Honey on Thu Apr 12, 2012 1:59 am; edited 1 time in total (Reason for editing : changed "below" to "previously in this thread")
Mike Honey
Mike Honey

Posts : 185
Join date : 2010-08-04
Location : Melbourne, Australia

http://www.mangasolutions.com

Back to top Go down

Microsoft SSIS BIDS SCD Type 2 Development Empty Re: Microsoft SSIS BIDS SCD Type 2 Development

Post  blynch Mon Apr 02, 2012 11:52 am

I took one of my dimension transform-load packages and pulled out the custom Dimension Merge component from codeplex and re-developed it using the Microsoft Slowly Changing Dimension transformation and have to report that once I got the hang of it, it is much easier (and faster) to develop and use. Maybe this will change in the future, but for now I will be going with the Microsoft transformation for the rest of my dimensions.

Thank you Mike.

Brad

blynch

Posts : 18
Join date : 2011-10-16

Back to top Go down

Microsoft SSIS BIDS SCD Type 2 Development Empty Re: Microsoft SSIS BIDS SCD Type 2 Development

Post  Mike Honey Thu Apr 12, 2012 2:03 am

Hi Brad, that's good news and thanks for the update. Mike.
Mike Honey
Mike Honey

Posts : 185
Join date : 2010-08-04
Location : Melbourne, Australia

http://www.mangasolutions.com

Back to top Go down

Microsoft SSIS BIDS SCD Type 2 Development Empty Re: Microsoft SSIS BIDS SCD Type 2 Development

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top


 
Permissions in this forum:
You cannot reply to topics in this forum