Sorting and Paging in MDX Query (MS Analysis Services)

Question

I built an MDX query, to retrieve specific articles from an OLAP Cube. Basically it returns articles below a specific article-category node that are produced by a specific manufacturer:

SELECT NON EMPTY
(
 Hierarchize
 (
  {
   DrilldownLevel
   (
    {
     [T DAT Article].[Ar ID].[All]
    }
   )
  }  
 ) 

)

DIMENSION PROPERTIES PARENT_UNIQUE_NAME,
[T DAT Article].[Ar ID].[Ar ID].[Ar Key],
[T DAT Article].[Ar ID].[Ar ID].[Ar LongName] 
ON COLUMNS 
FROM [Catalog_2009]

WHERE 
(
 [T DAT Structure].[St St ID FK].&[193066], -- specific article-category node
 [T DAT Firm].[Fi ID].&[86] -- specific manufacturer
) 

CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

Now I want enhance this query to support paging and sorting. Meaning I can supply:

• Page index (like 0)
• Page size (like 30)
• Sort column (like Ar LongName)
• Sort direction (like ascending)

What approach should I take? I looked at the Subset and order clause. But those basically restricted the results from the 'hierarchize' part of the query, meaning they cut off the hierarchies instead of the end result..

Could anyone give me a hint how to get paging and sorting to work?

Answer 1

Sorting is a pain in MDX, or at least thats how I always felt about it. If your query has just one dimension on rows and one on columns, it will make life much simpler.

The ORDER keyword can be applied to a set, and you just need to give it a number to sort on, and tell it which way to go with ASC or DESC.

The ASC or DESC issue gets confusing as you can also specify BASC or BDESC as well. The "B" means "break" and it's saying whether you want the rows (or columns) to be sorted within their heirarchical groups, or do you want to break the hierarchy altogether when you sort, and totally reorder the rows.

Here's a simple example

ORDER({[Time].[Month].members}, [Measures].[Gross Sales], BASC)

This means you want to list all Months in ascending order of Gross Sales value. You are breaking the hierarchy, so that the Months do not remain grouped within their parent Years, and can be completely intermingled by the sort operation.