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

View previous topic View next topic 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

View previous topic View next topic Back to top

- Similar topics

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