Order and Sort with MDX in SSAS

Problem

Sometimes when working with SQL Server Analysis Services (SSAS) and MDX queries, the ordering does not seem to work. Is this situation a bug or do I need to adjust my MDX queries?

Solution

As with most applications there are many different ways to complete a task and sorting and ordering in SSAS and MDX is no exception. Furthermore, Analysis Services’ utilization of hierarchies further complicates the method and implementation of sorting routines. Additionally, sorting can be defined for dimension attributes or measures or both.

Basic Sorts

SSAS actually has several potential layers of sorting; we can set the sorting at the dimension level or force sorting during query run time. Examples are the best way to way to convey how sorting works.  Thus, we will utilize the AdventureWorks2012 SSAS database.

For dimension attributes, we can actually set the Order By property in SQL Server Data Tools (SSDT aka BIDS) to either a dimension attribute Name or a Key as displayed below for the Education attribute under the Customer dimension.  These values will generally be honored by most client applications, but not always.  Additionally, the sort order defined on the dimension attribute property screen will only be the name or the key and is always in ascending order.

SSAS Sort Order

An MDX query run in Management Studio ( as will an Excel Pivot Table ) will honor this sort order. Thus the following query shows the Education and Internet Sales Amount sorted in ascending order.

Default Sort Order

But what happens if the sort needs to be in Descending order. We need to employ the MDX Order function. The exact syntax for the order function is listed below; however it is certainly not as straight forward as its SQL Order by cousin.

The function requires three arguments with the first argument being the set expression. The set expression is the group of values (or set ) upon which to do the sorting, i.e. a set of values.

The second argument, the numeric or string expression, is the values used to complete the sort.

The third argument determines two items about the direction of the sort. First, the sort direction of ascending ( ASC / BASC ) or descending ( DESC / BDESC) is defined. Second, the “B” prefix to the ASC or DESC determines if the hierarchy should be “broken” or ignored when ordering the list.

Numeric expression syntax
Order(Set_Expression, Numeric_Expression 
[ , { ASC | DESC | BASC | BDESC } ] )
String expression syntax
Order(Set_Expression, String_Expression 
[ , { ASC | DESC | BASC | BDESC } ] )

As you can see, ordering in MDX is no simple order! For instance, say using the same example above, we need to sort by the Internet Sales Amount in descending order. The below screen print shows how we can complete this sort. First we define what is being sorted (Customer Education); next how the sort should evaluated (by Internet Sales Amount) is noted, and ultimately what order should be used (Descending) is listed.

Sort Internet Sales Order Desending

What happens if you actually want to sort by the Education value and not by the amount. That gets a little more tricky as we have to use the same attribute for the first two arguments, as noted in the below screen print. The first argument again tells what to sort using the member property, whereas the second argument tells it to sort by the Member_Name.

Sort Education

To add more complexity, notice in the above screen print that the “All Customers” value shows in the results as we did not specify to exclude this total row.  For the Education attribute, the “All Customers” row is actually the highest level of the system defined hierarchy for the Education attribute, as displayed below. Below the “All Customers” level are the individual attribute values which make up the Education attribute.

Education Hierarchy

We can actually “break” the hierarchy by using either the BASC or BDESC sort order argument. Thus modifying the above query with the BDESC argument results in the following query results.

Education by BDESC

The BDESC (and BASC) arguments ignore or “break” any hierarchy positioning for the members of the attribute whereas DESC and ASC first sort the attributes by the hierarchy and then by the individual values specified by the set. Using BDESC and BASC provides a true picture of the ordering scheme. Another common use of the BASC and BDESC is when querying date dimensions. The below screen print shows the use of the BDESC argument and thus breaks or removes the hierarchy sorting.

BDESC

Previewing the above screen print, you will notice that using the BDESC sorts all the values irrespective of the hierarchy that attribute values belongs to. If we use just DESC, as displayed below, then each Quarter is actually retained within its respective year.

Date DESC

Notice in the above illustration that now each year is its own “ordering” set, and the Internet Sales Amount is sorted by quarter within the Year Hierarchy. The below screen print shows how the hierarchy for the Calendar date dimension is setup in SSAS.

Calendar Hierarchy

Suppose you would like to sort by two columns within a MDX query. Although there are few alternate methods of sorting multiple columns, the basic premise is the same. In the below examples, we will sort first on Education, descending, and then on promotion Ascending. In both cases we are using the “break” arguments, BDESC and BASC. Notice how we are using the Member_Name to sort the dimension attributes.

Sort Two Columns

Alternately, you could nest Order functions as displayed in the below screen print; however, this method seems to be a bit more complicated to read and trouble shoot.

Nested Order

Conclusion

Sorting in MDX is no simple matter especially when compared to using the Order By clause in SQL. In MDX, you must define the sort order while defining the axis value to be included on the rows and columns; the Order function actually requires three expressions: 1) the set expression to sort over, 2) the numeric or string item to sort on and 3) the direction of the sort order and whether to break the tout of the hierarchy during sorting or not.

Next Steps

Related Posts

One comment

Leave a Reply

Your email address will not be published. Required fields are marked *