Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Analysis Server (SSAS) KeyColumn vs NameColumn vs ValueColumn


By:   |   Read Comments (14)   |   Related Tips: > Analysis Services Dimensions

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


Problem

What is the difference between the KeyColumn, the NameColumn, and the ValueColumn for a dimension attribute in SQL Server Analysis Services?  How do they affect the data presented to the end user?  When should you use the KeyColumn, the NameColumn, and the ValueColumn for a dimension attribute?  Check out this tip to learn more.

Solution

It would seem that both the NameColumn and the KeyColumn should be one in the same; however each serves a specific and distinct purpose. At its most basic level the NameColumn field is what appears to end users in reports and spreadsheets. To the contrary, the KeyColumn field may or may not be seen by end users and is what attaches the dimension attributes to the dimension key and ultimately the fact table. The KeyColumn field can actually be made up of a composite key (a composite key is a key made up of more than one field); sometimes a composite key is required as some hierarchies require a composite key in order to display correctly and not error during processing. Likewise the ValueColumn allows the NameColumn to display a "friendly" version of the member attribute, but for actual use in filtering, for example, a different value is used. A good example of this situation is the NameColumn for the date attribute.  This value can be set to a friendly name such as "May, 2, 2015" which is a string, but the ValueColumn can be set to use the date field and utilize the benefits of a date field for filtering purposes. We will show several examples of these scenarios later in the tip.

We will use the AdventureWorks 2012 Multidimensional SQL Server and Analysis Services databases for our examples. These databases are available on CodePlex at: http://msftdbprodsamples.codeplex.com/releases/view/55330. Once you have the SQL Server and OLAP databases installed, you will start SQL Server Data Tools (SSDT); SSDT is the new name for Business Intelligence Development Studio in SQL Server 2012.

Key, Name, Value Properties

All three of the column properties are filled in on the attribute properties window as shown below. It should be mentioned that these fields actually work in a hierarchy of application. The KeyColumn field must be populated and is actually automatically filled in when the attribute is placed on the dimension. If no NameColumn is specified than the KeyColumn is displayed to the end user. Secondly, if a NameColumn is filled in, but no ValueColumn is populated then the ValueColumn defaults to the field from the NameColumn. Of course all three can be different, or two can be the same and the other can be different. Furthermore, if a composite KeyColumn is used (multiple values linked to the key), then the name column must be filled in.

Attribute Prop

For our first example, let us show where we need to use a composite key which necessitates the need for a NameColumn value. One of the better examples of this situation is a city name. Of course the same city name can be found within different states, providences or regions within a country. That situation is ok until a hierarchy, as shown below, is created which leads to a situation where two cities belonging to a different state-providence that have the same name. 

city attribute

This condition will lead to errors when the dimension or cube is processed. At this point if the cube is processed, a duplicate attribute error, shown below, is returned when attempting to process the dimension.

duplicate error

The fix for this particular problem is pretty straight forward in that we actually need to define the key for the city field to actually be a collection made up of the City and the State-Providence, as displayed next.

city state

Now that we defined the composite key, we must define a NameColumn as shown in the screen shot below.

Composite Key and Name

Of course it is not a requirement that a composite key be defined before utilizing a distinct NameColumn. Another common use for this situation is in the Product dimension of AdventureWorks.  The main key for the Product Dimension is the Product Attribute which is an integer field. Although that attribute as a number may be helpful, more often than not, you will want to return a "friendly" name to the end user, and thus the Productname field is used to display the name field opposed to the product number. This setup is shown below.

Name and Key Field

The NameColumn and KeyColumn field can also be used specifically to set the Orderby property for the attribute. Dates are a perfect example; if you use the date name field, which is a string, then the April dates will come first. To get around this issue, we can use the key field in the OrderBy property, shown next, to have the dates appear in the correct order. For more details on using the Order by function, please see Koen Verbeeck's tip on Sorting a dimension attribute by another dimension attribute in SQL Server Analysis Services.

Orderby

The date attribute is a perfect transition for the use of the ValueName field. The ValueColumn field allows us to still display a "user friendly" label while at the same time providing details to the client application instructing it to implement any special filter functionality. In the date example, additional predefined filters, such as "This Year" or "Last Year" are available when the field is defined as a date. However, if NameColumn is defined as a string, which is most common, these filters will be absent. However, we can define the ValueColumn as a date field, which in turns tells the application to use the special filters. The below screen shot shows how the date field looks when attempting to filter on the date field without the ValueColumn populated.

No Date Filters

Name field string

Now let us change the ValueColumn to use the date field as displayed in the following illustration.

ValueName Date

Now when we explore the filters for the date field, as illustrated next, the date filter functionality is now available based on the ValueColumn setting.

Date Filter

Conclusion

Within a dimension, each attribute has three column properties which greatly impact the various settings that are displayed to the end user application. The KeyColumn controls how the attribute is joined back to the dimension key. The NameColumn is the actual text displayed to the user. Both of these values can be used to control the default ordering sent to the client. Furthermore, the ValueColumn is used to control how the attribute interacts with the client used to display the data, for instance special data filters.

Next Steps 


Last Update:


signup button

next tip button



About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Monday, March 20, 2017 - 11:38:35 PM - Cyrus Back To Top

 

 Thanks Scott figured that out. Learning: Key column need not be ID/primary key, can be any name if it links with other table.

 

Thanks


Monday, March 20, 2017 - 9:17:27 AM - Scott Murray Back To Top

 Cyrus.. There are a couple ways to do this.. but it should be simply just adding the name with the UPPER function.


Monday, March 20, 2017 - 2:15:46 AM - Cyrus Back To Top

 Hi Scott, I ave a situation as below

ID:Name:Amt

1: AA: 5

2: aa: 10

3: Aa: 5

i understand my Key Column is ID and Name Column is Name, however when i put this in excel pivot from SSAS browsing i want all of it group undersame name i.e: i Should be getting only AA:20 as value is it possible to do this in SSAS with some inbuilt functionality

 


Monday, January 23, 2017 - 6:51:02 AM - sezer Back To Top

 

 Good article.Thanks it solved my problem :))


Wednesday, August 26, 2015 - 7:03:00 AM - Scott Murray Back To Top

Yes.. the key can be multi-column...you need to use the collection option.


Wednesday, August 26, 2015 - 12:05:21 AM - Cheung Tat Ming Back To Top

Hi Scot,

Can i understand the Key columns is act as many-to-many relations table which contain multi KEY to regonzie serval table?
(e.g. states, providences , regions , country)

 

Thanks you.


Tuesday, February 03, 2015 - 8:03:36 AM - Scott Back To Top

You should be able to adjust your query to request the .Currentmember.Member_Key


Tuesday, February 03, 2015 - 6:46:43 AM - rahul Back To Top

Hi,

I am accessing the attribute from mdx wjose member name is different like fro AccountId it display Account , this is fine.

 

When i access Select AccounId from mdx it gives me Account name.

but instead i actually need AccountId a key, a primary . so what is the syntax to get it.

can you please help.


Saturday, July 19, 2014 - 3:48:14 PM - Scott Back To Top

The whole dimension needs to be a date dimension to show the data filters in Excel (not PowerPivot).  I just report it not make it:-)


Friday, July 18, 2014 - 9:18:03 PM - cpv Back To Top

Under the Dimension Properties> Basic>Type there is a Time option, but not a date option.

Am I to understand that even though the whole dimension is not a date dimension, I have to formulate it that way just so a date field in that dimension will appear as a date in PowerPivot (connecting via Analysis Services).


Friday, July 18, 2014 - 9:53:53 AM - Scott Back To Top

The date column must have a dimension type of date.  This item is under the dimension properties.


Friday, July 18, 2014 - 2:14:42 AM - cpv Back To Top

I have been trying for some time to get the date column to show the date filters, with no success.  You have the best article i have seen on the use of the three columns, but I still can't get it to show. (BTW your writing is very good - and simple)

I see a warning in the dimension Structure Tab of Visual Studio, saying that 'date' is not compatible with a regular dimension type.  Is this my reason?   We have several date dimensions, but some of out other dimensions just have a date field to avoid having a snowflake dimension; in other words the date is an 'informational' attribute of the dimension, and not the fact table.

 

 


Tuesday, July 15, 2014 - 9:35:58 AM - Scott Murray Back To Top

It is SSAS 2012.


Tuesday, July 15, 2014 - 4:40:29 AM - Ash Shah Back To Top

Thanks for this informative article. I tried this with SSAS 2008 R2 and Excel 2010, but could not get the Date filters to appear. Is this an SSAS limitation or Excel?

Thanks,

Ash


Learn more about SQL Server tools