What are KeyColumns and NameColumn properties of an Attribute? What is the different between them?

KeyColumns is a property of an SSAS Dimension Attribute and it forms the Key (Unique) for the attribute. It can be bound to one or more columns in the underlying database table. When User Defined Hierarchies are created in the dimension (Attribute Relationships defined), setting this property becomes very critical and often requires setting this to a combination of more than one column from the Data Source View. For Example, say you have a Date Dimension and a hierarchy called Calendar Hierarchy (Year -> Quarter -> Month). Now what happens is that, Month gets repeated across different quarters and quarters get repeated across different years making the attribute as non-unique (like January can belong to Q1 of any year and similar Q1 can belong to any year). So to make the attribute unique, KeyColumns for Month should be set to something like Year and Month and similarly for Quarter should be set to Year and Quarter.

A NameColumn is a property of an SSAS Dimension Attribute and it is used to identify the column from the underlying Data Source View which provides the name of the attribute which is displayed to the end user by making it more user friendly instead of displaying the Key Column value. For Example, you might have ProductCategoryKey as 1, 2, 3, & 4, and ProductCategoryName as Bikes, Components, Clothing, & Accessories respectively. Now, NameColumn will be set to ProductCategoryName so that user sees them as Bikes, Components etc. even though the data in the background is processed/retrieved using the Key Column values as 1, 2 etc.

Here are some of the highlights/differences of KeyColumns and NameColumn properties:

• KeyColumns property is defaulted to the Attribute itself, and the NameColumn property is defaulted to Key Column (when the KeyColumns is set to only one column).
• Column(s) provided in the KeyColumns should be able to uniquely identify all the values of the respective attribute, whereas NameColumn need not be unique.
• KeyColumns can contain one or more columns whereas NameColumn can contain only one column.

SSAS Interview Question; SSAS Interview , Interview Questions for SSAS