4

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.

  1. Make DayName attribute visible, and use DayOfWeek as OrderBYAttribute
  2. 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.

6

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:

|improve this answer|||||
  • Thanks for the reply. I updated the wording in the question, does that make more sense? I think I'm misunderstanding your response, because your first paragraph seems to agree with option 1, and your third paragraph seems to agree with option 2. – moniker131 May 18 '17 at 18:36
  • 1
    I'm not sure how my 3rd paragraph agrees with option 2. A key is not always a sorting method. A key in datawarehouses is often autogenerated so in this specific scenario your dayofweek would obviously be unique but in a lot of dimensions it makes sense to use the properties the way they were intended to be used and it's a good habit to follow those best practices even in very simple cases where another approach could also work – Tom V - try topanswers.xyz May 18 '17 at 18:45
  • @moniker131 forgot to ping you in the previous comment – Tom V - try topanswers.xyz May 18 '17 at 18:54
  • The source table is a complete date dimension (1980 - 2020 by day) so both DayName and DayOfWeek are not unique. They have the same Cardinality, DayOfWeek = 1 & DayName = Sunday, etc. So isn't it an arbitrary decision to use one or the other as the Key Column? – moniker131 May 18 '17 at 19:54
  • The key column of an attribute is not the same as the key attribute. On mobile now but I'll try to clarify the answer later – Tom V - try topanswers.xyz May 22 '17 at 14:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.