Creating a Dimension with Multiple Column Key

I’ve come across this question three times, the latest one being today. So I thought I better write it down. The question is: in SSAS, how do we create a dimension with multiple columns as the key? Say you want to make 2 physical columns as the key of your AS dimension.

In the DSV, mark those 2 columns on the named query as logical primary key. Use control to block the columns, then right click on either and choose “set as logical primary key”.

On the Solution Explorer, right click Dimensions, New Dimension. BIDS will offer those 2 columns as the key of the dimension. On that first page (of the dimension wizard), BIDS also gives a warning that we need to have a name for this attribute, so choose a column as the name.

The column that we use for name isusually created on the DSV select statement as a concatenation of the 2 columns. For example, if you have col1 and col2 on dim1 table, your DSV named query is like this:
select col1, col2, col1 + ‘|’ + col2 as name
from dim1

The rest of the dimension creation process is like normal.

Then, after you created the dimension, on the dimension relationship tab of the cube, link the dim to the MG on that multiple column key, using the attribute name.

As they say, a picture speaks a thousand words, so I’ll add the DSV, the Properties of the Key Attribute, and the Define Relationship window screen shots below.