Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


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

Attend these FREE MSSQLTips webcasts >> click to register


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

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.

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


Last Update:


signup button

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, July 03, 2014 - 2:17:23 AM - Koen Verbeeck Back To Top

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 (http://cwebbbi.wordpress.com/2006/03/23/redundant-attribute-relationship-health-warning/), so I would try to avoid it.

Koen


Wednesday, July 02, 2014 - 11:02:08 AM - Alex Back To Top

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?


Thanks,

Alex.


Learn more about SQL Server tools