Order by – MDX v.s SQL

One of the most used SQL syntax in reporting is Order by, so it is normal to expect a similar functionality in MDX with a simple keyword.

How to order the results based on single column in SQL?

Let us first execute a simple query against AdventureworksDB

 SELECT  geo.City
        ,sum(SalesAmount) InternetsalesAmount
FROM  dbo.FactInternetSales fis
 join DimCustomer cus on fis.CustomerKey = cus.CustomerKey
 join dbo.DimGeography geo on cus.GeographyKey = geo.GeographyKey
 join dbo.DimDate dt on fis.OrderDateKey = dt.DateKey
where dt.CalendarYear = 2005
 group by geo.City
 order by  City asc

The above query filter the records for 2005 and order the results based on city in ascending order

How to get the similar results in MDX?

In MDX we need to

• Remove the empty (null) values using non empty function
  
• order the values by
  
  
  
  • using the Order function
    
  • with non hierarchy ascending order (basc)
    
  • based on the city names derived using membervalue(used to convert the values to string) function.

  SELECT
{ [Measures].[Internet Sales Amount] } ON COLUMNS,  
order(nonempty( [Customer].[Customer Geography].[City]
                , [Measures].[Internet Sales Amount]
               )
         ,[Customer].[Customer Geography].currentmember.membervalue
         ,basc
       )

ON ROWS
FROM
[Adventure Works] 
WHERE
( [Date].[Calendar].[Calendar Year].&[2005] ) ;

How to order the results based on multiple columns in SQL?

SELECT  geo.City,sum(SalesAmount) InternetsalesAmount
FROM  dbo.FactInternetSales fis
 join DimCustomer cus on fis.CustomerKey = cus.CustomerKey
 join dbo.DimGeography geo on cus.GeographyKey = geo.GeographyKey
 join dbo.DimDate dt on fis.OrderDateKey = dt.DateKey
where dt.CalendarYear = 2005
 group by geo.City
 order by 2 asc, City asc

The above query first sort the results by internetsales amount and if we have the same total amount for multiple cities then they get sorted in ascending order. 

Note: check the sort order in the order by class, it is internet sales amount followed by city

How to order the results based on multiple columns in MDX?

In MDX we need to

• Remove the empty (null) values using non empty function
  
• order the values by
  
  
  
  • using the Order function
    
  • in non hierarchy ascending order (basc)
    
  • based on the city names derived using the membervalue function.
  
  
• Order the values by
  
  
  
  • using the Order function
    
  • with non hierarchy ascending order (basc)
    
  • based on internet sales amount.

  SELECT
{ [Measures].[Internet Sales Amount] } ON COLUMNS, 
order (order(nonempty( [Customer].[Customer Geography].[City], [Measures].[Internet Sales Amount])

             ,[Customer].[Customer Geography].currentmember.membervalue
             ,basc
       )
       ,
       [Measures].[Internet Sales Amount]
       ,basc
       )
ON ROWS
FROM
[Adventure Works]
WHERE
( [Date].[Calendar].[Calendar Year].&[2005] ) ;

Note: check the sort order, it is city followed by internet sales amount

Why should we eliminate the Null values in the MDX query? Won’t that degrade query performance?

SSAS cubes never store the Null values so it is merely a representation. So by including the Nonempty function you are actually increasing the performance of the query

Why we are reversing the order of columns in MDX?

In SQL we are using a single order by keyword which accepts multiple columns. It orders the results by first column and if the values are same then it further order the rows based on second column. So in our example the results are ordered by internet salesamount and when the values are same (e.g 3374.99) it has further been ordered by CIty

In MDX we are using two order by function, so we are ordering the results twice. The query works only because of the stable sort algorithm which is used in MDX order function

E.g. let us use Bubble sort to prove this theory

In the below example I have already sorted the results by City and using the bubble sort to sort the results based on Amount.

Bubble sort is a stable sort and as you can see, whenever amount are equal between multiple rows, it remember and retain the position of pre-Sorted CITY order.