Surrogate Keys RRS feed

  • Question


    Should I use surrogate key in the Fact Table... I mean Fact generated surrogate key. I am keeping surrogate keys of all  dimensions but do i need to genearte a surrogate key in the fact table for uniquness or use composite key (based on all dim surrogates key) for uniquness..


    how the performance will impact?


    Thank you

    Wednesday, April 23, 2008 7:51 PM


  • If you have a degenerate dimension built off your fact table, then that would improve performance of processing the degenerate dimension. (Adventure Works uses a composite key for the degenerate dimensions which is not ideal.)


    The other situation in which I find an identify field on the fact table handy is when I have bridge tables which foreign key to the fact table.


    But other than degenerate dimensions, SSAS doesn't really care about a surrogate key for the fact table as it won't use it for anything. So it won't impact performance one way or the other (except for the fact that your fact tables are 4 bytes wider because of the surrogate key field.)


    Wednesday, April 23, 2008 8:09 PM