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...