• Home
  • Reddeppa_DWH Questions

Submitted Questions

  • Sep 29 2008
    12:09 PM
    8854
    12

    How do we maintain Primary key in Fact Table ?
    • Reddeppa_DWH

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

    Star Read Best Answer

    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.