By: Koen Verbeeck | Comments (2) | Related: > Analysis Services Dimensions
Problem
SQL Server Analysis Services – SSAS – sorts dimension attributes alphabetically by default. The tip Change Sort Options on Dimension Attributes in SQL Server Analysis Services explains how you can change this sort order by adjusting the key columns and by sorting on this newly created key. A good example is sorting months chronologically instead of alphabetically. But what if you want to sort a dimension attribute by something that cannot be represented in the key? How do you handle this situation?
Solution
We will explain the concept using the AdventureWorks 2008R2 sample cube, which you can download here. In the product dimension, we want to sort the products according to their color. First you would have black products, than blue products and so on.
![Products sorted by color](/tipimages2/3254_datapreview.jpg)
Color is not an attribute that we would like to include in the product key in the dimension, so we need to use another solution. Let’s take a look at the product dimension first. The product attribute has the column ProductKey as the key column and the EnglishProductName as the name column. By default, the OrderBy property, responsible for the sort order of the attribute – is set to the name column.
![Initial properties](/tipimages2/3254_initialproperties.jpg)
If an attribute doesn’t have a name column, the OrderBy property is set to the key column. When we browse the dimension, we can see the products are nicely alphabetically sorted.
![Products are initially sorted alphabetically](/tipimages2/3254_productlist_alphabetically.jpg)
When we want to sort the attribute by another attribute, we need to change the OrderBy property from Name to AttributeName (or AttributeKey, if you want to sort by the key value of another attribute).
![Options for the OrderBy property](/tipimages2/3254_orderby_dropdown.jpg)
Next, set the OrderByAttribute property to the attribute you wish to sort on. In our case, this is the Color attribute.
![Choose an attribute to sort on](/tipimages2/3254_orderbyattribute_dropdown.jpg)
After these changes, the dimension needs to be processed. The products are now sorted by color:
![Products are sorted by color](/tipimages2/3254_productlist_bycolor.jpg)
SQL Server Analysis Services Attribute Relationships
In order to make this work, the attribute used for sorting – color in our example - must be related to the attribute that needs to be sorted. This is done by creating attribute relationships. Since product is the key attribute of the dimension, color already has an attribute relationship with product. This can be verified in the Attribute Relationship tab of the dimension.
![The attribute relationships for the Product dimension](/tipimages2/3254_attribute_relationships.jpg)
However, if there is no attribute relationship, you cannot use the AttributeName value for the OrderBy property. For example, let’s try to sort the Category using the Spanish Product Name. First we need to add this attribute to the dimension.
![Adding a new attribute to the dimension](/tipimages2/3254_add_attribute.jpg)
Automatically, there is an attribute relationship created between Spanish Product Category Name and the Product attribute.
![New attributes are automatically linked to the key attribute](/tipimages2/3254_attribute_relationships_2.jpg)
However, it is not possible to select Spanish Category Name in the dropdown for the OrderBy property on the Category attribute, as there is no attribute relationship between them.
![You can only sort on attributes linked by an attribute relationship](/tipimages2/3254_emptydropdown.jpg)
Let’s create an attribute relationship in order to enable the sorting. Right-click on the canvas of the attribute relationship tab and select New Attribute Relationship...
![Create a new attribute relationship](/tipimages2/3254_create_attribute_relationship.jpg)
This results in the following:
![Spanish Category Product Name is now linked to the Category](/tipimages2/3254_attribute_relationships_3.jpg)
Now the Category attribute can be sorted against their Spanish names.
![Sort categories by their Spanish names](/tipimages2/3254_orderbyspanishnames.jpg)
After processing the dimension, this is the result:
![Categories are sorted according to their Spanish name](/tipimages2/3254_categories_byspanishnames.jpg)
Components is translated to Componente in Spanish and Clothing is Prenda, so they are switched.
Conclusion
This tip has shown that it is straight forward in SSAS to sort a dimension attribute by another dimension attribute. Attribute relationships play a key role in this configuration.
Next Steps
- Try it out yourself! Take a look at your own cubes and see if any attribute can be given another sort order.
- Take a look at another tip about sorting attributes in a dimension: Change Sort Options on Dimension Attributes in SQL Server Analysis Services
- For more tips about SSAS dimensions: Analysis Services Dimensions Tips.
About the author
![MSSQLTips author Koen Verbeeck](/images/Koen-Verbeeck-2018-2.png)
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips