SSAS – Which way to order an attribute

ssas

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.

Best Answer

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:

Related Solutions

SSAS: Move tempdb database

You need to alter the location using ALTER DATABASE tempdb MODIFY FILE location and restart SQL Server. SQL Server will automatically create the tempdb in the new location but you may have to go to the old location to delete the (old) files.

See Moving the tempdb database in http://msdn.microsoft.com/en-us/library/ms345408.aspx

SSAS / MDX Query – Add an extra dimension when using Order

I'm not quite sure what you're asking, but I think you want to do the following (correct me if I'm wrong):

You want to see a breakdown of sales by month, one row for each month, with a total for all months at the bottom.
You want to see the items across the columns, ordered by the item with the largest sales volume to the smallest.

select non empty
       order ([Item].[Item].Children
             ,([Measures].[Total Line Value]
              ,[Date].[Month].[All])
             ,BDESC)
    on Columns
       non empty
      ,[Date].[Month].Children
    on Rows
  from [Sales Analysis]
 where [Measures].[Total Line Value]

The columns are each individual item, sorted by the total Line Value over all months, i.e. from all-time best seller to all-time lowest seller, with items having no sales at all excluded.

The rows are the months for all time where data exists. You can use a set definition to specify any date range you want. Note that this assumes the existence of a dimension called [Date].

This query will show the items in order of total sales across the columns and the sales for each month by item down the rows. I'm not 100% sure if this is what you want; feel free to clairfy your question if I'm not on the right track.

Best Answer

Related Solutions

SSAS: Move tempdb database

SSAS / MDX Query – Add an extra dimension when using Order

Related Question