By: Koen Verbeeck | Updated: 2014-06-17 | 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.
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.
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.
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).
Next, set the OrderByAttribute property to the attribute you wish to sort on. In our case, this is the Color attribute.
After these changes, the dimension needs to be processed. The products are now sorted by color:
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.
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.
Automatically, there is an attribute relationship created between Spanish Product Category Name and the Product attribute.
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.
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...
This results in the following:
Now the Category attribute can be sorted against their Spanish names.
After processing the dimension, this is the result:
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
Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2014-06-17
