What if Natural Key changes in a Slowly Changing Dimension Type 2?

View previous topic View next topic Go down

What if Natural Key changes in a Slowly Changing Dimension Type 2?

Post  pgali on Tue Jun 12, 2012 7:45 pm

Hello,

We have recently created a Slowly Changing Dimension of Type 2 and now wondering what happens if the Natural Key itself changes over a period of time? If this happens, then there is no "glue" holding the dimension and the fact together. Is there a way to handle a situation like this?

Any advice is much appreciated.

-PG

pgali

Posts : 6
Join date : 2012-06-12
Location : USA

View user profile

Back to top Go down

Re: What if Natural Key changes in a Slowly Changing Dimension Type 2?

Post  ngalemmo on Tue Jun 12, 2012 8:35 pm

What you do depends on what you want to do. Natural keys can change for a variety of reasons. If they change because the item is sufficiently different, then you don't need to do anything. Older transactions would continue to reference the obsolete items.

If the problem is source systems changed and you need to report current attributes, you would need to provide some form of cross reference so that old NK rows can reference current versions of new NK rows (so that the historical record can be maintained). One way to do this is to keep an additional non-unique surrogate key in the dimension table. This would be a 'type 1' key that serves the same purpose as the natural key when locating current versions of rows. Because it is surrogate, you can control how the value is assigned. You could assign the surrogate to one or more different NK rows and use the surrogate rather than the true NK to locate the current or point in time dimension 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: What if Natural Key changes in a Slowly Changing Dimension Type 2?

Post  LAndrews on Tue Jun 12, 2012 8:53 pm

Do you have an example? Is this a one-time event or part of the normal business processes?

Typically during normal processes, there is no way to distinguish between a new natural key and a changed one ... the ETL logic would treat both as "new" and create a new dimension record

A one time event (e.g. a new source system or corporate merger) gives you some other options.

For example, during a merger, all your physical assets may get re-assigned asset numbers to align with the new corporation. If you just allow the new ones to come into your warehouse via normal processing, then your asset dimension will get all new records (with the new numbers), and as you mentioned, no connection with the old records (with the old numbers).

Alternately, because it is a controlled event, you could also apply the re-assignment logic to your dimension, effectively mapping the old ID's to the new ones. Retain the old_id in the dimension as well (Type-3 attribute).

hope this helps....

LAndrews

Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada

View user profile

Back to top Go down

Re: What if Natural Key changes in a Slowly Changing Dimension Type 2?

Post  BoxesAndLines on Wed Jun 13, 2012 8:57 am

Nice. I'm always looking out for uses for the type 3 dimension. This looks like a good fit.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: What if Natural Key changes in a Slowly Changing Dimension Type 2?

Post  pgali on Thu Jun 14, 2012 6:04 pm

thank you Sirs.

LAndrews - to answer your question, this is not a business process and more like a one time event. We knew this is going to happen.

pgali

Posts : 6
Join date : 2012-06-12
Location : USA

View user profile

Back to top Go down

Re: What if Natural Key changes in a Slowly Changing Dimension Type 2?

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