Sorting a dimension attribute by another dimension attribute in SQL Server Analysis Services

By:   |   Comments (2)   |   Related: > Analysis Services Dimensions


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?


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

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

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

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

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

After these changes, the dimension needs to be processed. The products are now sorted by color:

Products are 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.

The attribute relationships for the Product 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.

Adding a new attribute to the dimension

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

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

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

This results in the following:

Spanish Category Product Name is now linked to the Category

Now the Category attribute can be sorted against their Spanish names.

Sort categories by their Spanish names

After processing the dimension, this is the result:

Categories are sorted according to their Spanish name

Components is translated to Componente in Spanish and Clothing is Prenda, so they are switched.


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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Koen Verbeeck 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 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

Comments For This Article

Thursday, July 3, 2014 - 2:17:23 AM - Koen Verbeeck Back To Top (32525)

Hi Alex,

thanks for reading the tip.
The reason for the redundant relationship is probably because there are multiple attribute relationships required because you need to sort multiple attributes by the same attribute. I can't say more without knowing about the actual model.

However, having a redundant relationship can have nasty side effects (, so I would try to avoid it.


Wednesday, July 2, 2014 - 11:02:08 AM - Alex Back To Top (32514)

Hi, Koen!

Nice article! I have one question: what if I need several attributes to be sorted by the same attribute.

Are there any concerns using the same attribute or may I add new one (with another name, but the same source)?

I tried to use the same attribute, but server raised warning about redundant relationship (why???), but everything was worked as expected (at least on the test environment). Then I decided to add another attribute with diff. name to avoid this redundant-message, but this may cause additional movements during support process.

So, the question is: how to deal with sorting several attributes by one attribute?



get free sql tips
agree to terms