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

[pgali]

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

[ngalemmo]

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.

[LAndrews]

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

[BoxesAndLines]

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

[pgali]

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.