SCD2 date handling using source table LastModified column

Mar 15, 2010 at 10:01 PM

I have source table srcConfiguration and audit table srcConfigurationHistory. CRUD reflected in the history table by insert of previous version of the source row like a typical audit. Both tables maintain a LastModified column that captures the time when user modified the configuration.

My question is,  in the KSCD wizard, SCD date handling tab offers date stamping using SSIS system variable using fixed expiration policy (old expires yesterday..) However, to maintain the accurate trail of the user actions and audit, shouldn't the source system LastModified time be used.

If yes, how do I achieve that using KSCD?



Mar 17, 2010 at 12:27 AM

You're not using the KSCD as intended.  Typical dimension processing does NOT load history - it differentially loads a snapshot of the "current" state of the operational system and determines what historical "version" records have to exist in order to reconstruct that history.

You should not be feeding your srcConfigurationHistory table to the KSCD, you should only be feeding in your srcConfiguration table.

Mar 17, 2010 at 4:08 PM

Todd, I agree with the post and if supported would add much more flexibility to the KSCD processing.While normal operation after an intial load might only look at changed records (which could be done from an audit structure) it is a common scenario for an inital load to have to review historical changes recorded in audit structures.

So the delimma, in it's current state KSCD operates much like a record set diff and does not support using operational datetime values for marking effective/start and expiration/end date time values. I'd really like this to be considered along with other related things like supporting processing more that one change per load (again more targeted at an initial load not a rapidly changing dimension scenario).

Mar 18, 2010 at 9:32 PM

I hear you.

I consider this as part of implementing a "change stream" type of processing - that's my term for it anyway.  Not high on my personal list of things I need - but I can see the appeal, and it's been voted on a lot.

In the meantime, I should probably post a page about how to accomplish a load like this using the current component and a Foreach Loop... it's not actually that hard.  Actually - look for that in my blog shortly...

Mar 18, 2010 at 9:35 PM

Thanks Todd. Knowing an alternate will help immensely. Will wait for your blog post.