Tracking the removed dimensional records in source data

View previous topic View next topic Go down

Tracking the removed dimensional records in source data

Post  andriy.zabavskyy on Tue Aug 21, 2012 10:48 am

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

andriy.zabavskyy

Posts : 18
Join date : 2011-09-12

View user profile

Back to top Go down

Re: Tracking the removed dimensional records in source data

Post  BoxesAndLines on Tue Aug 21, 2012 12:26 pm

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.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Tracking the removed dimensional records in source data

Post  sgudavalli on Wed Aug 22, 2012 5:33 am


It sounds okay to use Row_End_Date to logically delete the dimensional Record

sgudavalli

Posts : 29
Join date : 2010-06-10
Age : 32
Location : Pune, India

View user profile

Back to top Go down

Re: Tracking the removed dimensional records in source data

Post  hang on Wed Aug 22, 2012 7:00 am

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

View user profile

Back to top Go down

Re: Tracking the removed dimensional records in source data

Post  vickyejain on Wed Aug 22, 2012 8:04 am

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).

vickyejain

Posts : 7
Join date : 2012-08-20

View user profile

Back to top Go down

Re: Tracking the removed dimensional records in source data

Post  andriy.zabavskyy on Wed Aug 22, 2012 10:20 am

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

View user profile

Back to top Go down

Re: Tracking the removed dimensional records in source data

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