Tuesday, February 22, 2011

Composite keys in dimensions

Question:
I have a table with a composite key, but when I create a dimension from this table only 1 column is used in the key. How do I make a composite key in the dimension table?

Answer:
Add multiple columns to the KeyColumns and then add a calculated "Name Column"

1. Create a named calculation on the table in the Data Source View, concating all composite keys. For example: OrderId + CONVERT(nchar, OrderType)

2. In the dimension, click on one of the key attributes. In Properties scroll down to KeyColumns and click "...", then add all columns to this

3. While still looking at Properties add the calculated field (from 1. above) to the Named Column. You may need to do this for each key field

4. If you run into any problems with this, please post a comment detailing it, so I have it for future reference to help others.

Reference
http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/028226ac-39c9-4d5c-b02d-9d1096272f35/#1c606588-0824-4533-8696-62f49b05354e

No comments:

Post a Comment

There was an error in this gadget