Data Warehousing, BI and Data Science

20 June 2010

Creating a Dimension with Multiple Column Key

Filed under: Analysis Services — Vincent Rainardi @ 7:07 pm
Tags:

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.

 

Advertisements

4 Comments »

  1. What column is used in the MG when defining the dimension relationship? It appears only one column can be selected. Do I need to create a named query for the MG too?

    Comment by Jake — 8 March 2011 @ 4:06 am | Reply

    • Hi Jake, in the Define Relationship window, the Measure Group Columns are key1 and key2. We can select 2 columns* for the Measure Group Columns. The Dimension Columns are also key1 and key2. The grain is KeyName though. I’ve added the screen shots in the post above. Hope this helps.
      No you don’t need to create a Named Query, you can use a physical table or a view. Whether it is a NQ, table or view, you must have a column for the key name, and 2 columns for the key values.
      *If you still get 1 column only, check the properties of the key attribute of the dimension. The key attribute should have 2 key columns and 1 name column, as per above screen shot.

      Comment by Vincent Rainardi — 8 March 2011 @ 6:54 am | Reply

  2. […] it is always better to create a surrogate key which can be used as the dimension key (Read this post to learn how to create a dimension with multiple column key). For this scenario, the following […]

    Pingback by When and How to Snowflake Dimension Sources : SSAS Design Part 1 - Some Random Thoughts — 4 June 2011 @ 1:15 pm | Reply

  3. […] it is always better to create a surrogate key which can be used as the dimension key (Read this post to learn how to create a dimension with multiple column key). For this scenario, the following […]

    Pingback by When and How to Snowflake Dimension Sources : SSAS Design Part 1 — 29 December 2015 @ 4:23 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: