Dealing with Surrogate Keys

Apr 9, 2009 at 3:30 PM
I have an issue with the way that your component only seems to partially support surrogate keys maintained on the destination dimension table by the database.  I can have the control ignore my surrogate key, but then when I want to handle the Expired SCD2 records I can't use the surrogate key to set the invalid_date column.  I could get around this by using the business key and a where invalid_date is null, but this is not going to be as fast as using the surrogate key and if the handling of the new records happens to load before the expired SCD2 ole dbcommand runs, the results are going to be questionable.  The other way to get around it is to create a variable, set it to 0 and give that variable to your control to use as a surrogate key.  Then when I handle the new records, I can just insert into the database and ignore the surrogate key passed; when I handle the expired scd2 records I can use the surrogate key passed in.  This seems to work nicely, however it seems like there should be some option in the surrogate key section that says something like 'check this box if you want the control to simply trust that you know what you are doing with the surrogate keys'.

Obviously this isn't a show stopped, but something you may want to consider in future development.  Thanks for the work you have put in thus far!
Coordinator
Apr 9, 2009 at 10:18 PM
Edited Apr 9, 2009 at 10:19 PM
Thanks for the thanks - you're welcome - come back with more suggestions!

On to your topic - let me see if I understand what you're getting at.

You are:
1. Letting the database maintain surrogate keys.  (For sake of argument, I'll assume it's SQL Server and an identity field.  But you could have another RDBMS, and/or a triggered sproc or something generating keys - doesn't matter.)
2. You have a "race" condition where the "New" records (in particular the new version of an SCD2-changed record) are prone to being updated by the process you have in place to UPDATE the expired version of those records.

I think I get it.

Yes, your "workaround" should do the trick - but I see that it's not "intuitive".  Definitely not as nice as I'd want it.

I think you're looking for an option in the Surrogate Key options that says "yes, I have a surrogate key in the existing dimension flow, but leave all that stuff up to me to deal with."  The effect of turning this option on would be to:
a) Disable the other Surrogate Key options (cause they're for managing SK generation, which doesn't apply).
b) Requires that you specify a field as an Surrogate Key on the Existing Dimension
c) Prevents linking any Source System or Special Member columns to that Existing Dimension column
d) Passes the Existing Dimension SK through the component untouched to the Unchanged, SCD1 Update, and SCD2 Expired outputs.
e) Does not pass through an SK column on the New output.
Apr 9, 2009 at 11:06 PM
Spot on!
Apr 15, 2009 at 2:46 PM
I'd also like this to be handled as you have indicated, Todd.
Essentially, this enables the database engine to be in contol over the assignment and management of key values thus disabiling any of the components Surrogate Key options.
I must admit, however, that I've got to break free and try the components Surrogate Key options (that you put so much work into).
Coordinator
Apr 21, 2009 at 5:45 PM
OK, gents - watch Issue 2708...