Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Tracking the removed dimensional records in source data

5 posters

Go down

Tracking the removed dimensional records in source data Empty Tracking the removed dimensional records in source data

Post  andriy.zabavskyy 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

Back to top Go down

Tracking the removed dimensional records in source data Empty Re: Tracking the removed dimensional records in source data

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

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

Back to top Go down

Tracking the removed dimensional records in source data Empty Re: Tracking the removed dimensional records in source data

Post  sgudavalli 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 : 40
Location : Pune, India

Back to top Go down

Tracking the removed dimensional records in source data Empty Re: Tracking the removed dimensional records in source data

Post  hang 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

Back to top Go down

Tracking the removed dimensional records in source data Empty Re: Tracking the removed dimensional records in source data

Post  vickyejain 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

Back to top Go down

Tracking the removed dimensional records in source data Empty Re: Tracking the removed dimensional records in source data

Post  andriy.zabavskyy 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

Back to top Go down

Tracking the removed dimensional records in source data Empty Re: Tracking the removed dimensional records in source data

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