SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SSAS - Understanding KeyColumns Dimension Property

For many Analysis Services developers building dimensions with many attributes and hierarchies is something that comes as second nature to them.  However, I’ve found for those that are new to Analysis Services changing the KeyColumns property can be particularly difficult to understand why and when it is necessary. 

In a previous post I discussed the need for defining attribute relationships in a dimension.  The side effect of have attribute relationships is that you will often, depending on your data, need to define multiple KeyColumns for the selected attribute.  For example, think about a date dimension, which just about every cube will have.  Your date dimension likely has a hierarchy of Year-Quarter-Month in it and after you define attribute relationships the dimension no longer will process successfully.  The warning message you receive looks something like:

Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: 'dbo_DimDate', Column: 'CalendarQuarter', Value: '4'. The attribute is 'Quarter'.

So what is this trying to tell us is wrong with the Quarter attribute?  Let’s take a look at the data to find out. 

Notice in the table that the Quarters 1, 2, 3 and 4 appear multiple times in different years.  Quarter 1 appears in both 2006 and 2007.  This is a problem because after defining attribute relationship we told Analysis Services that Quarter relates to Year but because our data shows quarter can relate to multiple years we have to be more specific.  For this date dimension that means having both Year and Month as the key.  This is where KeyColumns come in.  KeyColumns allow you to define how each attribute will work internally within Analysis Services.  Because Quarter is not unique by itself now we have to create a composite key between Year and Quarter to no longer receive the duplicate attribute key warning.  To make the composite key you select the attribute to alter, in this case Quarter and go to the properties (F4).  Navigate to the KeyColumns property and this hit the ellipsis.

This opens the Key Columns dialog box.  Simply add Year to your key column and then hit OK.  You can change the order of the Key Columns but that is generally done just for sorting purposes. 


After making a composite key you will see a red line under the attribute (shown below) which, is stating that you must define the NameColumn property.  Because we now have a composite key it doesn’t know what to show the users so this property will define what the users should see.  Select the desired column to display to users then hit OK.

The duplicate attribute key warning will also occur with the Month attribute in my situation.  I would have to follow the same method that I did above to solve the problem for Month. 

The other way to solve this problem would be to change the data.  So instead of have 1, 2, 3 and 4 for Quarter I could instead have 012005, 022005, 032005, 042005, 012006, 022006, 032006, 042006, etc...


Devin Knight

Devin is a BI consultant at Pragmatic Works Consulting. Previously, he has tech edited the book Professional Microsoft SQL Server 2008 Integration Services and was an author in the book Knight's 24-Hour Trainer: Microsoft SQL Server 2008 Integration Services. Devin has spoken at past conferences like PASS and at several SQL Saturday events. He is a contributing member to the Business Intelligence Special Interest Group (SIG) for PASS as a leader in the SSIS Focus Group. Making his home in Jacksonville, FL, Devin is a participating member of the local users’ group (JSSUG).


Posted by jaavii on 26 June 2013

Nice article! Thanks!!

Is there any constraint or does it affect performance to use a composite key instead of a single field key?

Thanks again!

Posted by x1029cloud on 5 March 2014

Absolutely useful !!!

I especially appreciate you explain it from program's view  what we did doesn't make sense to it

Posted by arcus on 11 February 2016

Unfortunately, any screenshots / picture files posted here were removed, had its name changed, or are temporarily unavailable...

Leave a Comment

Please register or log in to leave a comment.