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

Fact Table Natural ID

Go down

Fact Table Natural ID Empty Fact Table Natural ID

Post  tim_goodsell Thu Mar 01, 2012 12:22 am

Hi

I have a fact table that is populated from one data source and I store the source systems row unique id (single value) in the fact table. However this fact table will now be populated from two data sources, the second data source has a unique key comprising of four fields. Is it best to have another four fields in the fact table to store the unique key or is it best to use the existing field and cocatenate the four together.

Regards

Tim

tim_goodsell

Posts : 49
Join date : 2010-09-21

Back to top Go down

Fact Table Natural ID Empty Re: Fact Table Natural ID

Post  Vishy Thu Mar 01, 2012 4:44 am

no dimensions ???

Vishy

Posts : 75
Join date : 2012-02-28
Location : India

Back to top Go down

Fact Table Natural ID Empty Re: Fact Table Natural ID

Post  ngalemmo Thu Mar 01, 2012 11:33 am

I assume this is a degenerate dimension value.

I usually always define degenerate dimension columns as varchar because you never know what you are going to get. If you can, convert the column and contents to varchar and store the new values as concatenated strings.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Fact Table Natural ID Empty Re: Fact Table Natural ID

Post  tim_goodsell Thu Mar 01, 2012 7:35 pm

Yes a field that identifies where the transaction has come from.

The transactions will come from two data sources, transactions up to the end last financial year (History, 2011-06-30) will come from Source System A whereas all transactions after that will come from Source System B. The reason for this is that they are replacing A with B but only migrating current data from A to B not history.

As i see it I can either have two transaction fact tables (one for each system) and then create a view, or combine both sources into one fact transaction table.

Regardless of which way I still need to identify where the transaction comes from. The unique key of transactions from Souce System A is pretty simple - one unique field, but the unique key for transactions from Source System 4 comprises of four fields

tim_goodsell

Posts : 49
Join date : 2010-09-21

Back to top Go down

Fact Table Natural ID Empty Re: Fact Table Natural ID

Post  BoxesAndLines Fri Mar 02, 2012 11:01 am

Depending on the volumetrics of the fact table, I would look into managing the keys in a staging table and possibly replacing the 4 column PK with a single surrogate key that you could use a degenerate dimension.
BoxesAndLines
BoxesAndLines

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

Back to top Go down

Fact Table Natural ID Empty Re: Fact Table Natural ID

Post  bciampa Sat Mar 10, 2012 5:11 pm

Hi BoxesAndLines,

I'm just wondering why you would suggest that (not being critical, just wondering). Is it to reduce the space needed in the database to store each fact table row or is there some other reason as to why that approach might be advantageous? I'm always trying to add to my knowledge so I just wanted to ask!

Thanks,
Brian

bciampa

Posts : 8
Join date : 2012-02-24

http://valuabledata.blogspot.com

Back to top Go down

Fact Table Natural ID Empty Re: Fact Table Natural ID

Post  BoxesAndLines Sat Mar 10, 2012 8:52 pm

I'm not a big fan of mashing multiple columns into a single column. Especially a primary key. The OP didn't delve into the datatypes of the columns but one can imagine the problems associated with a mish-mash of numeric and text data. Anytime you have to rationalize disparate data from multiple systems you inevitably end up with a need to manage different primary keys from each system. I typically manage this type of logic in persistent staging tables to keep the dimensional model clean.
BoxesAndLines
BoxesAndLines

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

Back to top Go down

Fact Table Natural ID Empty Re: Fact Table Natural ID

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top


 
Permissions in this forum:
You cannot reply to topics in this forum