Surrogates Keys RRS feed

  • 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

    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.



    Thomas Ivarsson


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

    Monday, April 28, 2008 6:51 PM