Correction of Type-2 SCD Fields' Historical Values Results in Effective Date Range Splits with no Changes
2 posters
Page 1 of 1
Correction of Type-2 SCD Fields' Historical Values Results in Effective Date Range Splits with no Changes
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
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
Re: Correction of Type-2 SCD Fields' Historical Values Results in Effective Date Range Splits with no Changes
Leave as-is. Everything still works like it should.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Similar topics
» scd2 effective date, end date data type
» Type -2 - Effective From Date...Audit field(Update_TS) from OLTP or System Time ?
» Initial date of effective date column for SCD 2 implementation
» Loading dimension when source already has effective to and from dates
» Handling a correction vs a change in SCD type II dimension.
» Type -2 - Effective From Date...Audit field(Update_TS) from OLTP or System Time ?
» Initial date of effective date column for SCD 2 implementation
» Loading dimension when source already has effective to and from dates
» Handling a correction vs a change in SCD type II dimension.
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum