DW Key Vs Natural Key ?

View previous topic View next topic Go down

DW Key Vs Natural Key ?

Post  VTK on Wed Aug 31, 2011 11:55 pm

I would like to know your opinion of using DW Key is the Data warehouse or dimension tables instead of actual key from OLTP.
Please keep in mind that I am not talking about surrogate keys. I work in environment where the OLTP system has surrogate key meaning every table has one field which identifies the unique record. so we have used that field to create a DW_ID and used that ID to create versions in DW. but when we bought another system we had issues as that system did not have a single field unique key which forced us to put the composite keys in the Dim tables. so I am wondering if anyone else used this approach and their thoughts.

VTK

Posts : 50
Join date : 2011-07-15

View user profile

Back to top Go down

Re: DW Key Vs Natural Key ?

Post  ngalemmo on Thu Sep 01, 2011 12:12 am

It doesn't matter what the source system uses, all dimensions should have their own surrogate primary key.

The question should be: What do I use as the natural key for the dimensions? The general answer to that is that is should be the business key. Sure, a lot of OLTP systems use surrogate keys and they do it for much the same reasons why you should do the same in the DW... it isolates the system from the business keys. But, a data warehouse needs to take the long view and maintain dimensions around business keys. This protects from from source system changes.

To your specific problem, change the natural key to a text field of suitable length and populate new rows with the new source keys (concatenate fields if you have to). The old rows will remain static and be referenced by facts from the old system. You could build an old/new system cross reference (separate from the actual end user tables) and use it to maintain the old system rows with data from the new system.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: DW Key Vs Natural Key ?

Post  VTK on Thu Sep 01, 2011 10:32 pm

Thanks for the reply. We always use Surrogate keys in all dim tables and use that to tie to fact table.

Question is that we built DW_Keys using a cross reference table and used that to create versions in our dim tables but now the new system has composite keys so we can't use the xref tables(as it built with with 1 field) we built already for the new system so we decided to put the actual keys into the dim table. Now we get data from both old and new system. In future if we buy something else and if that has different set of keys then should we add those keys also into the dim tables ? Does not seem like a good idea.

Lets say, as per your suggestion, if we cat the key fields and put it as one field in dim table then it wll be cumbersome for anyone to trace that record back to source system. Correct ?

VTK

Posts : 50
Join date : 2011-07-15

View user profile

Back to top Go down

Re: DW Key Vs Natural Key ?

Post  ngalemmo on Fri Sep 02, 2011 2:21 pm

No. You have a varchar column designated as the natural key, which contains whatever it needs to contain to identify the source and business key related to the row. The dimension would also contain attributes that may include some or all of the fields associated with the natural key. What this does is standardize how the natural key is represented in the dimension so that the same surrogate key lookup process can locate a row regardless of how the natural key is represented in the source system.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: DW Key Vs Natural Key ?

Post  datamodeller on Mon Sep 05, 2011 1:31 am

It would not be advisable to use the OLTP SK as the natural key in the DW dimension table as it would break if you would need to different instances of source system or multiple source systems or ETL complications around handling of deletion and recreation of the same business entity in the source system. Business Key would be always handy for the above scenarios.

Also, it would be good to have default standard column (even if it is redundant) for the dim natural key and populate with the business key (simple/concatenate) rather declaring the composite key.

datamodeller

Posts : 9
Join date : 2010-07-25

View user profile

Back to top Go down

Re: DW Key Vs Natural Key ?

Post  hang on Mon Sep 05, 2011 4:42 am

I guess the key point for a natural key (NK) in a dimension table is to uniquely identify a dimension record in the source OLTP system for change detection, and I don't think NK has to always be a textual field or in a single column. If an integer key or multiple fields happen to be a PK in the source table, so be it. I wonder if there is any need to try to convert them into another form of NK. Would it be much simpler and yet effective just to put NK as is in the dimension table.

I think the integer SK in the dimension table serves additional purposes, part from its role as PK in general RI sense. Firstly as a PK, it enables SCD in the dimension with the same NK value being repeated for SCD changes. Secondly it saves space as a FK in the fact table compared to composite FK. The third reason is that it can still keep referential integrity between fact and dimension tables even if the NK changes.

hang

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

View user profile

Back to top Go down

Re: DW Key Vs Natural Key ?

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