Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Recording why a change happened using MERGE with Type 2 SCD Expand / Collapse
Posted Friday, June 13, 2014 10:08 AM


Group: General Forum Members
Last Login: Thursday, November 24, 2016 9:51 AM
Points: 151, Visits: 694
I have a few fields in my staging DimProducts that are type 2 scd. I've got my merge statement up and run after a little bit of confusion.

Is it possible/necessary to track why a scd occurred or should we just be concerned with the audit trail i.e.

"these are the old records for this product, this is the new active record, they old ones could have been retired for three reasons,but i don't care why they just did!!!"

I was originally included the three status in my dim (product active, color active, size active) and it was confusing me as the merge wasn't updating them as i expected.

Post #1580614
Posted Friday, June 20, 2014 1:40 PM


Group: General Forum Members
Last Login: 2 days ago @ 10:59 AM
Points: 485, Visits: 1,093
I'm not sure how you define "why something changed". That sounds more like a business rule.
Post #1584496
Posted Monday, June 23, 2014 3:42 AM


Group: General Forum Members
Last Login: Wednesday, January 27, 2016 7:06 AM
Points: 2,657, Visits: 1,657
Do you mean why something changed from a business perspective or from a logical perspective? Using Lookup and Conditional Split transforms it's quite easy to track whether column values have been Inserted, Updated or Deleted and then you could populate a 'ReasonForChange' column accordingly. If it's the business reason for change that you want to capture, that would likely need to come from the source system, otherwise you'd just be guessing I'd have thought.

Post #1584993
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse