none
Surrogates Keys

    Question

  •  

    If I have surrogate keys defined in the EDW environment, do i need to defined surrogate keys at my end in the Data Mart. The only one reason which I can see down the line is to shiled my self from the structural changes from EDW environment.. is there any other reason to use surrogate keys....

     

     

    Friday, April 25, 2008 4:46 PM

All replies

  • Hi,

     

    In addition to protection from the structure changes you mentioned, there are following important reasons on top of my head.

     

    1. Keep track of history (for SCD - slowly changing dimension)

    2. Reduce storage in fact table

    3. Improve preformance (when joining tables)

    Friday, April 25, 2008 7:12 PM
    Answerer
  •  

    Tks you for the respone. BUt my question again will be I am consuming data from my Enterprise Datawarehouse which suppose to be properly architect. So why should I generate Surrogate keys at my end. I am already pulling sources key and surrogate keys from the EDW. So r u recommeneding that I should generate additionaly keys for the Data Mart. What will be the use except shielding my self from the strcutures changes in the EDW. If history question comes-up then I should capture histroy from the OLTP system not from the EDW. I also think the EDW should keep the history and we should only consume. I know the importance of surrogates key in the EDW but not in the data mart which will consume data from the EDW. I assume the ETL process is going to be more challenging if I go ahead with Data Mart surrogates keys.

     

    If we combine 2 tables in one from the EDW environment then use of surroagtes key is reasonable in the DM.

     

    Appreicate if someone share his own thought and help me in the direction for the surrogate keys

     

    Thank you

     

    Sunday, April 27, 2008 5:25 AM
  • Hi! You do not need to create new surrogate keys in your data mart. Copy the surrogate keys from the EDW.

     

    HTH

    Thomas Ivarsson

     

    Sunday, April 27, 2008 7:32 AM
    Moderator
  • Totally agree.

    Monday, April 28, 2008 6:51 PM
    Answerer