How do we maintain Primary key in Fact Table ?

Is there any option other than Surrogate key or concatenated key?

Questions by Reddeppa_DWH

Editorial / Best Answer

ravikiranrali  

  • Member Since May-2008 | May 9th, 2009

Two main reasons to generate and maintain a surrogate key on DW side:


1. If your DW has multiple sources for a dimension or fact, the PK ID fields can have same values from different sources. The only way you can handle this is by maintaining the composite primary key on these columns. Now lets imagine that you have some 10 dimensions in a subject area, you would expect to have only 10 keys in the Fact, but by having the composite keys you would endup creating 20 or more keys on the fact. This would inturn adversely affect your query performance.

2. Another case, suppose some data migration activities take place on the source side -- which is quite possible if the source system platform is changed or your company acquiered another company and integrating the data etc -- if the source side architect decides to change the PK field value itself of a table in source, then your DW would see this as a new record and insert it and this would result in data inconsistency /discrepency between the source and DW and it could be a nightmare to fix the issue. By having a separate surrogate key on DW side that is generated based on the grain of the source table (not on the IDs), you are immune to any such PK value changes on the source side.

Showing Answers 1 - 4 of 4 Answers

srimanta.mandal

  • Nov 5th, 2008
 

In data warehousing we are used surrogate keys by which we can change the value of primary key.
Suppose you have two table emp and dept, and empno is the primary key of dept. table and also it is used in emp table as fk. In this case if we cannot modify the pk.
because it is used as a foreign key in dept table. Thats why we need a extra colums which have no actual meaning.
Here we have to take a extra columns ID as surrogate key in both table which have no meaning.
But it can perform the joins between two tables.

ravikiranrali

  • May 9th, 2009
 

Two main reasons to generate and maintain a surrogate key on DW side:


1. If your DW has multiple sources for a dimension or fact, the PK ID fields can have same values from different sources. The only way you can handle this is by maintaining the composite primary key on these columns. Now lets imagine that you have some 10 dimensions in a subject area, you would expect to have only 10 keys in the Fact, but by having the composite keys you would endup creating 20 or more keys on the fact. This would inturn adversely affect your query performance.

2. Another case, suppose some data migration activities take place on the source side -- which is quite possible if the source system platform is changed or your company acquiered another company and integrating the data etc -- if the source side architect decides to change the PK field value itself of a table in source, then your DW would see this as a new record and insert it and this would result in data inconsistency /discrepency between the source and DW and it could be a nightmare to fix the issue. By having a separate surrogate key on DW side that is generated based on the grain of the source table (not on the IDs), you are immune to any such PK value changes on the source side.

mundravijay

  • Jan 21st, 2011
 

In fact table because of we are using summerized data, we will be including all dimensional's table's primrary key as a Foreign key for their relationship.

The key should me in the Lowest Level Grain of that table.

If we still need a seprate primrary key for the face table, we just have to use a Surrogate Key in the table.

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions