How to handle SCD type 2 changes - when source records are archived?

View previous topic View next topic Go down

How to handle SCD type 2 changes - when source records are archived?

Post  ian.coetzer on Tue Sep 27, 2011 2:39 pm

Hi Everyone

I have a problem - I have a dimension made up of 4 source tables.
so - i merge all 4 source tables (starting with a core table and left joining the rest in succession).

Now one of the 4 source tables have been archived!

when i left join all is fine - BUT the attributes coming from this 4th source table is defined as SCD type 2!

So because they are no longer found (they are now NULL = "Unknown" where it was previously something like "House")
The original dimension record is expired! and a new one is inserted - which is fine! as per design - BUT in the new one the specific attribute is 'Unknown' and in the expired dimension record it is 'House' ! I would rather have 'House' appear in the newest record?

avatar
ian.coetzer

Posts : 57
Join date : 2010-05-31
Age : 36
Location : South Africa

View user profile

Back to top Go down

Re: How to handle SCD type 2 changes - when source records are archived?

Post  BoxesAndLines on Tue Sep 27, 2011 4:00 pm

I keep the old row and mark as logically deleted, meaning it no longer showed up in the source. It still is the current record for joining purposes.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: How to handle SCD type 2 changes - when source records are archived?

Post  ngalemmo on Tue Sep 27, 2011 5:16 pm

Another option is to propagate those attributes from the current row to the new row.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: How to handle SCD type 2 changes - when source records are archived?

Post  hang on Tue Sep 27, 2011 7:23 pm

Is it about handling deletes in dimension table? If the dimension record is not found in the source, you should not insert a new row for the deletion. Instead, you mark the current record as "deleted" by a flag, possibly without even expiring the record, so that you can always leave the last record open but marked for filtering purpose.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: How to handle SCD type 2 changes - when source records are archived?

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