Sorting Dimensions in SSAS

One of the most common tasks that is relatively easy to do in all Microsoft data applications is to sort data. Unfortunately, this is not true when developing cubes in SSAS. You’ll think there would be a simple “sort” option or something where you right-click and sort by XYZ. Nope! Instead, you have to configure sorting using several steps.  

Date dimension data will likely be the most commonly needed type of data to sort. Sorting won’t be an issue for numeric data, but it will be for the alphanumeric data like month names. For example, when I browse my Data dimensions for that represent month names, I get the following months names in this specific order: April, August, December, February….You get the idea, it would be in alphabetical instead of chronological order.. This is of course is useless in the real word.

To fix this we need to specify that the Month Name be sorted by the Month number.  When you design your data source view, it is important that when you bring in a column that needs to be ordered outside of the natural alphabetic or numeric order, then that column needs an associated column just for ordering. In the case we have Month name (that cannot be ordered alphabetically) and Month number (that we’ll use to sort Month name). Here is the process to sort by Month Name by Month number.

1.      Select the Month (or whatever represents the column used for sorting) attribute in the Dimension Structure and Select Properties.

      2.      Change the following properties for the Month Name. 

a.      AttributeHierarchyEnabled property to False  

                    b.      AttributeHierarchyOptimizedState property to NotOptimized
                    c.      AttributeHierarchyOrdered property to False

 

 

3.      We then need to establish Attribute Relationships. In the diagram, right-click the Month Name attribute and then select New Attribute Relationship.

       4.      In the Create Attribute Relationship dialog box, the Source Attribute is Month Name. Set   the Related Attribute to Month.

5.      Since we expect this type of relationship to not change, in the Relationship type list, set the relationship type to Rigid. Select OK to apply changes.

 

6.      Go back to the Dimension Structure tab. Select the Month Name in Attributes. Change the Orderby property to Attribute Key and change the property OrderByAttribute to Month.

You can now reprocess the Date dimension and apply the changes. If now go back into the Dimension browser we can see that the Month names appear in the correct order.

 

We can use this method to sort anything that has an associated sort column. Any item dimension that must be order in some way other than alphabetically, we have to use this type of method.