Correction of Type-2 SCD Fields' Historical Values Results in Effective Date Range Splits with no Changes

Go down

Correction of Type-2 SCD Fields' Historical Values Results in Effective Date Range Splits with no Changes

Post  isabpf on Tue Mar 25, 2014 3:55 pm

Please forgive the rambling topic title above.  My question will need a little set up.

We have many years of history in a Type-2 SCD, but have found that some historical values being tracked were loaded incorrectly.  Here is a sample:

Table: CS_Claims_Delta (Please assume that this is a complete list of columns)

Claim_ID     Claim_Key       Row_Eff_Date   Row_Exp_Date    Claim_Status    Claim_Type
--------     ----------      --------------  --------------    ------------    -----------
100            A00001          01/01/1900      02/01/2010        Active             Medical
501            A00001          02/01/2010      03/01/2010        Active             Indemnity
601            A00001          03/01/2010      12/31/9000        Active             Medical

Suppose for this example that we discovered that the second row (with Claim_ID = 501) should not have had the value of "Indemnity" for Claim_Type, and it should instead have been Medical.  We can fix it easily enough by simply updating the second row:

Claim_ID     Claim_Key       Row_Eff_Date   Row_Exp_Date    Claim_Status    Claim_Type
--------     ----------      --------------  --------------    ------------    -----------
100            A00001          01/01/1900      02/01/2010        Active             Medical
501            A00001          02/01/2010      03/01/2010        Active             Medical
601            A00001          03/01/2010      12/31/9000        Active             Medical

But what this leaves us is three rows with effective/end dates that could be combined into just one row:

Claim_ID     Claim_Key       Row_Eff_Date   Row_Exp_Date    Claim_Status    Claim_Type
--------     ----------      --------------  --------------    ------------    -----------
100            A00001          01/01/1900      12/31/9000        Active             Medical

But if we were to combine those three rows, we would need to re-establish dimensionality of any impacted Fact table rows where they were connected to either Claim_ID 501 or 601.  

What is the preferred choice in this case?  To leave rows in the Type-2 table despite their tracking no changes, or to clean up the Type-2 table, combining effective dates where possible, and then re-assign Claim_ID in associated Facts?

Thanks for your insight,

Bryan

isabpf

Posts : 2
Join date : 2014-03-25

View user profile

Back to top Go down

Re: Correction of Type-2 SCD Fields' Historical Values Results in Effective Date Range Splits with no Changes

Post  BoxesAndLines on Tue Mar 25, 2014 4:24 pm

Leave as-is. Everything still works like it should.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Thank you

Post  isabpf on Thu Mar 27, 2014 12:24 pm

Thanks for your input. That's what we'll do.

isabpf

Posts : 2
Join date : 2014-03-25

View user profile

Back to top Go down

Re: Correction of Type-2 SCD Fields' Historical Values Results in Effective Date Range Splits with no Changes

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum