0

First of all let me just say, I know, there is such thing as SCD0 in Kimball's definition... however, the SSIS SCD wizard has a provision for "Fixed Attribute" and I have a design case where I legitimately need what has been unofficially dubbed SCD0.

Would there be any negative impact to defining "Fixed Attribute" columns as SCD2 and then just not wiring up the SCD2 output as a workaround for the lack of SCD0 functionality?

This question pertains specifically to SSIS Dimension Merge SCD Component.

tlum
  • 913
  • 3
  • 13
  • 30

2 Answers2

0

Your post states you want a slow changing dimension type 0. My understanding is that SCD0 just means you retain the original definition of a row and never update it or set an end date to it. If that's the case, then why use a Slow Changing Dimension wizard at all? Wouldn't you just make your ETL be insert where not exists? Or do you mean that some of the columns will be retained as the original value and you will use higher order types for other columns?

For instance, maybe you have a source table with ProductId, ProductName, and ProductDescription. Perhaps you want to retain ProductId as it is your key and the original ProductName but you want to show newer ProductDescription values. If that's the case, you could either overwrite ProductDescription with new values and leave the other columns alone (Type 1), or you could set an end date for your current row and create a new row but inset the original ProductId and ProductName (Type 2), or you could include a PreviousProductDescription in your new row while retaining the original ProductName (Type 3).

Registered User
  • 8,357
  • 8
  • 49
  • 65
  • That would be true if the "row" were considered to be SCD0, however, this question refers to a row where certain columns are SCD0 and certain columns are SCD1. – tlum Jan 04 '12 at 20:51
  • OK, then why use the Slowing Changing Dimension task? You could stage the data in a table and run a MERGE statement to only update the changed fields or insert new records. Or if this isn't a SQL Server 2008+ box, just run a separate UPDATE and INSERT WHERE NOT EXISTS statement. If you aren't making Type 2 or Type 3 changes, the Slow Changing Dimension task is overkill. – Registered User Jan 04 '12 at 23:02
  • DMSCD is part of the overall ETL strategy across multiple packages where it also captures and logs statistical data as well as maintaining audit data on the rows. Merge may be what I resort to but first I'm going to try and beat DMSCD into submission. – tlum Jan 05 '12 at 13:49
  • ALSO, the SCD0 and SCD1 columns do not exist in the source. They are all the result of multiple lookup operations. – tlum Jan 05 '12 at 14:16
0

Yes, there is a negative impact. SCD2 is a two part process, first inserting a new row and second expiring the old one. SCD1 updates are handled during the expiring of the SCD2 row. As a result, if an SCD0 column changes - and is defined as SCD2 - at the same time the SCD1 column changes the SCD1 update output won't receive the row since it will go to the SCD2 expire output. Wiring up both outputs to do the SCD1 update results in many unnecessary updates being executed when an SCD0 column changes but the SCD1 column hasn't. So, this is not an effective workaround for lack of SCD0 support in DMSCD component.

tlum
  • 913
  • 3
  • 13
  • 30