SSAS dimension key as compound key vs char field

Question

For anyone working with SSAS 2008, a question:

I have a rather large dimension whose key attribute is a combination of two integer fields. I have the key attribute's Key Columns set up as a collection consisting of the two integer fields, and for the name column I have a WChar field which concatenates the two integer fields like so ("Field1 - Field2"). My question is: would I get better performance using the WChar field as the Key Column rather than the compound key? Or are two integer fields still better than one WChar field when it comes to Key Columns?

Thanks

Answer 1

In theory, a single integer "surrogate key" would be fastest. However I suspect that since the size of the concatenated field is a relatively small string, there won't be much difference between using the compound key and a concatenated field. It would probably begin to make a difference if the concatenated string was significantly larger.

Another problem you might run into with large dimensions that have large string keys is the analysis services key store has a limit of 4gb.

Check this whitepaper out, it has a lot of good information about optimizing the dimensional design and general perf tuning:

http://sqlcat.com/whitepapers/archive/2009/02/15/the-analysis-services-2008-performance-guide.aspx

This book has some of the best coverage on the analysis services storage engine and physical data structures: http://www.pearson.ch/1471/9780672330018/Microsoft-SQL-Server-2008-Analysis.aspx

Hope this helps