SSAS - Which way to order an attribute?

Our SSAS cube has a date dimension, which for the DayName string member displays alphabetically instead chronologically. I added a DayOfWeek integer 1-7 to use for ordering. I have two options to accomplish this, not sure of the advantages/disadvantages of either.

• Make DayName attribute visible, and use DayOfWeek as OrderBYAttribute

• Make DayOfWeek attribute visible, and use DayName as NameColumn

The attributes are both functionally the same when setup like this. I'm leaning towards #2, since it's an int.

You have your terminology a bit wrong which makes it hard to tell what exactly you mean by your 2 options but you should use the OrderByAttribute (which is what I think you mean by option 1). The OrderByAttributeis meant to be used for exactly that, ordering the attributes by something else than their Key or Value.

If by option 2 you mean you create the attribute using the DayOfWeek column (essentially creating an attribute with a KeyColumn of DayOfWeek) and then set the NameColumn to DayName you would in this case essentially have the same end user experience but that is not what those attributes are meant to do.

The KeyAttribute should be used to define which members are unique and the NameColumn should be used to define what you want to be shown to the users.
Each of these properties have their own purpose and you should use them as such.

Abusing KeyColumn to define an order will get you in trouble when you will actually need that property to resolve issues with duplicates and so on.

Some excerpts from the documentation:


KeyColumns: Contains the column or columns that represent the key for
the attribute, which is the column in the underlying relational table
in the data source view to which the attribute is bound. The value of
this column for each member is displayed to users unless a value is
specified for the NameColumn property.


NameColumn Identifies the column that provides the name of the
attribute that is displayed to users, instead of the value in the key
column for the attribute. This column is used when the key column
value for an attribute member is cryptic or not otherwise useful to
the user, or when the key column is based on a composite key. The
NameColumn property is not used in parent-child hierarchies; instead,
the NameColumn property for child members is used as the member names
in a parent-child hierarchy.


OrderBy Describes how to order the members that are contained in the
attribute hierarchy. The default value is Name, which specifies that
ordering of the attribute members is based on the value of the
NameColumn property, if any. Otherwise, members are ordered by the
value of the key column. The available options are as follows:

StackExchange Database Administrators Q#173932, answer score: 7