Fact Table Natural ID

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

no dimensions ???

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.

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

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.

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

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.