Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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
Author
Message
Posted Friday, June 13, 2014 10:08 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 12, 2014 4:50 AM
Points: 78, Visits: 448
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.

Thanks.
Post #1580614
Posted Friday, June 20, 2014 1:40 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 12, 2014 7:24 AM
Points: 272, Visits: 612
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
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 5:09 AM
Points: 2,059, Visits: 1,431
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.

Regards
Lempster
Post #1584993
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse