Tracking the removed dimensional records in source data
5 posters
Page 1 of 1
Tracking the removed dimensional records in source data
We have a case when we need to track the removed records in source data.
Actually in dimension table there are attributes of SCD type 2 and I was proposed to reuse the ROW_END_DATE column to mark the deletion date (date when the records has been identified as not existing) but I am not sure if it correct.
Any advise?
Thanks
Actually in dimension table there are attributes of SCD type 2 and I was proposed to reuse the ROW_END_DATE column to mark the deletion date (date when the records has been identified as not existing) but I am not sure if it correct.
Any advise?
Thanks
andriy.zabavskyy- Posts : 18
Join date : 2011-09-12
Re: Tracking the removed dimensional records in source data
If there is not a business end date available, you can always create a logical delete column to indicate the data is no longer being received from the source.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: Tracking the removed dimensional records in source data
It sounds okay to use Row_End_Date to logically delete the dimensional Record
sgudavalli- Posts : 29
Join date : 2010-06-10
Age : 40
Location : Pune, India
Re: Tracking the removed dimensional records in source data
Without an additional delete indicator, how do you differentiate a deleted dimension record from SCD2 changed record at any point in time. You might end up having a costly subquery everywhere to exclude deleted records. To be thorough, you may also need a delete date attribute as well, so that you don't have to check other rows to know if and when the record has been marked deleted.
hang- Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia
Re: Tracking the removed dimensional records in source data
The best practice I have seen is to use a separate indicator column that flags records deleted from the source data. This helps (a) distinguish between actual end dates in source vs. deletes and (b) trace back all the original attributes including the end date that the source record has.
Part of this can also be driven by business requirements, if there are multiple downstream systems that are going to use this information, it is of great advantage to be able to accurately present the source system data (including the end dates).
Part of this can also be driven by business requirements, if there are multiple downstream systems that are going to use this information, it is of great advantage to be able to accurately present the source system data (including the end dates).
vickyejain- Posts : 7
Join date : 2012-08-20
Re: Tracking the removed dimensional records in source data
Thanks for advises. Since the records in source system could be removed and added again, I'm going to add a deleted attribute in dimension as SCD type 2. Does it sound reasonable?
andriy.zabavskyy- Posts : 18
Join date : 2011-09-12
Similar topics
» Tracking of historical data using SCD2 in a non-dimensional data model
» Using the Dimensional Data Warehouse as source data for the OLTP process
» Dimensional Model from a Hierarchical Data Source
» Designing data marts from an EAV data source
» Best method to track deleted records on source in Oracle
» Using the Dimensional Data Warehouse as source data for the OLTP process
» Dimensional Model from a Hierarchical Data Source
» Designing data marts from an EAV data source
» Best method to track deleted records on source in Oracle
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum