Fact Table Natural ID

View previous topic View next topic Go down

Fact Table Natural ID

Post  tim_goodsell on 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

View user profile

Back to top Go down

Re: Fact Table Natural ID

Post  Vishy on Thu Mar 01, 2012 4:44 am

no dimensions ???

Vishy

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

View user profile

Back to top Go down

Re: Fact Table Natural ID

Post  ngalemmo on 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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Fact Table Natural ID

Post  tim_goodsell on 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

View user profile

Back to top Go down

Re: Fact Table Natural ID

Post  BoxesAndLines on 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.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Fact Table Natural ID

Post  bciampa on 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

View user profile http://valuabledata.blogspot.com

Back to top Go down

Re: Fact Table Natural ID

Post  BoxesAndLines on 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.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Fact Table Natural ID

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