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 123»»»

Capturing the Reason For Change for Type 2 changes Expand / Collapse
Author
Message
Posted Wednesday, January 29, 2014 9:11 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 9:12 AM
Points: 1,887, Visits: 1,179
If I want to populate a ReasonForChange column in a dimension table for Type 2 changes, is it best practice to populate that column in the new row (the one with an EndDate of 9999-12-31 and IsCurrent = 'Y') or the old row (with EndDate = the date the attribute changed and IsCurrent = 'N')?
Or both perhaps?

I'm fine with writing the code to identify the ReasonForChange, just not sure where to write the value.

Thanks
Lempster
Post #1535965
Posted Wednesday, February 26, 2014 1:03 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, March 15, 2014 9:30 AM
Points: 191, Visits: 512
I'm by no means an expert of any kind on data warehousing so take this as my opinion.

I would put it in the expired record since the change is the reason why the record expired. The current record has no reason for change since it hasn't changed.
Post #1545558
Posted Wednesday, February 26, 2014 1:32 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 11, 2014 12:58 PM
Points: 291, Visits: 517
It depends.....mostly on the level of tracking data that you want to keep. If you are storing something minor, then it is probably not an issue to keep it in the table. Just make sure you document and stay consistent on which record has the change info. Normally, I would put it in the new record and try not to touch the old record.
We have systems where they want to search for which column changed, so we have a second table to store the changed data in column/value format. It may seem redundant, but it makes the searches run fast.
I have seen approaches where the keep the change data in XML format. Any design that would use blob data should use a separate table.
Post #1545567
Posted Wednesday, February 26, 2014 9:33 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:30 PM
Points: 35,959, Visits: 30,251
Lempster (1/29/2014)
If I want to populate a ReasonForChange column in a dimension table for Type 2 changes, is it best practice to populate that column in the new row (the one with an EndDate of 9999-12-31 and IsCurrent = 'Y') or the old row (with EndDate = the date the attribute changed and IsCurrent = 'N')?
Or both perhaps?

I'm fine with writing the code to identify the ReasonForChange, just not sure where to write the value.

Thanks
Lempster


You don't need the pain of maintaining an "IsCurrent" column for Type 2 SCDs. If you have both a start and end date per TYPE 2 SCDs, the dates are good enough to tell you what is current especially since you were smart enough to NOT use a NULL end date. I'll also recommend that you don't actually use 9999-12-31 as an end date. Instead, use 9999-12-30 or even just '9999' (which will auto-magically convert to 9999-01-01) so you have at least 1 day of "headroom" for certain range calculations.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1545691
Posted Thursday, February 27, 2014 2:14 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 9:12 AM
Points: 1,887, Visits: 1,179
Thanks for the replies folks. As JustMarie and EricEyster made opposing arguments, I guess the most important point is to be consistent!

Jeff, I don't see it as much of a pain to maintain a IsCurrent flag; I agree that it could be seen as superfluous, but I'm just following Kimball best practice which states:

...the current-flag provides a rapid way to isolate exactly the set of dimension members that is in effect at the moment of the query.

So it's for ease of querying more than anything else. Thanls for the tip about the EndDate value though - I think I can afford to lose most of the year 9999 . (Although thinking in that vein lead to the Y2K problem didn't it? )
Post #1545765
Posted Thursday, February 27, 2014 5:41 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 11, 2014 12:58 PM
Points: 291, Visits: 517
Lempster (2/27/2014)
Thanks for the replies folks. As JustMarie and EricEyster made opposing arguments, I guess the most important point is to be consistent!

Jeff, I don't see it as much of a pain to maintain a IsCurrent flag; I agree that it could be seen as superfluous, but I'm just following Kimball best practice which states:

...the current-flag provides a rapid way to isolate exactly the set of dimension members that is in effect at the moment of the query.

So it's for ease of querying more than anything else. Thanls for the tip about the EndDate value though - I think I can afford to lose most of the year 9999 . (Although thinking in that vein lead to the Y2K problem didn't it? )


As long as you are consistent on assigning the end of time value, that becomes your isCurrent flag.
where endDate = '99991230'
Post #1545809
Posted Thursday, February 27, 2014 6:31 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 9:12 AM
Points: 1,887, Visits: 1,179
EricEyster (2/27/2014)

As long as you are consistent on assigning the end of time value, that becomes your isCurrent flag.
where endDate = '99991230'


Yeah, I get that.
Post #1545840
Posted Friday, February 28, 2014 7:43 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 7:28 AM
Points: 788, Visits: 1,915
I also maintain a pair of dates as well as a current flag. On the current records the end date is null however. On my agent tables, which are SCD2 because agent numbers are reused, I expose the flag as an active attribute. THere may be others where that is also usefully exposed as an attribute.


Post #1546344
Posted Friday, February 28, 2014 10:10 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, March 15, 2014 9:30 AM
Points: 191, Visits: 512
After reflection I'm going to change my answer to agree with EricEyster.

People aren't going to look in the previously expired record to see why it expired. The current record is the one that's getting all the attention so it should have the necessary info as to why it's the current record.
Post #1546464
Posted Friday, February 28, 2014 2:00 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, March 27, 2014 2:39 PM
Points: 2,141, Visits: 486
It is common practice to include both RowStartDate, RowEndDate, and RowIsCurrent. Here is Kimball Group's (Warren Thornwaite) thinking regarding "RowChangedReason" along with the code to do it:

http://www.kimballgroup.com/2006/06/01/design-tip-80-adding-a-row-change-reason-attribute/



Post #1546570
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse