Concepts - How do we maintain Primary key in Fact Table ?

GeekInterview.com > Interview Questions > Data Warehousing > Concepts

Go To First | Previous Question |

Concepts | Question 31 of 31

How do we maintain Primary key in Fact Table ?

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

Total Answers and Comments: 3

Last Update: May 10, 2009 Asked by: Reddeppa_DWH

Sponsored Links

Best Rated Answer

—

Submitted by: ravikiranrali

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.

Above answer was rated as good by the following members:
alokjee

October 14, 2008 15:48:02

Mamta11

Member Since: July 2008 Contribution: 12

RE: How do we maintain Primary key in Fact Table ?

Surrogate key is teh best option

Is this answer useful? Yes | No

November 05, 2008 03:40:05

srimanta.mandal

Member Since: November 2008 Contribution: 2

RE: How do we maintain Primary key in Fact Table ?

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.

Is this answer useful? Yes | No

0 1

Overall Rating: -1

May 09, 2009 14:06:03

ravikiranrali

Member Since: May 2008 Contribution: 1

RE: How do we maintain Primary key in Fact Table ?

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.

Is this answer useful? Yes | No

1 0

Overall Rating: +1

Go To Top

User Name		Remember Me?
Password

Question Bank