Search code examples
mdxolap-cubemdx-query

MDX Query performance issue when using DECENDANTS

I was using the below query without issues

SELECT NON EMPTY { [Measures].[Value] } ON COLUMNS, 
 NON EMPTY { ([Account].[Account List].[Account List].ALLMEMBERS ) } 
 DIMENSION PROPERTIES MEMBER_CAPTION ON ROWS 
 FROM [My Cube]

There is a requirement to get the parent of the Account (it's fixed to be 4 levels). So I re wrote it as below

SELECT NON EMPTY { [Measures].[Value] } ON COLUMNS, 
 NON EMPTY { (DESCENDANTS([Account].[Account].[Level 02].ALLMEMBERS, , LEAVES) ) } 
 DIMENSION PROPERTIES MEMBER_CAPTION ON ROWS 
 FROM [My Cube]

Above given is a simplified version, but my ROWS is a product of around 20 Dimensions and the query using the "DESCENDANTS" wont return and times out. The query in which I don't use the descendants comes back in less than 5 seconds.

How can I achieve the desired output in a better way like in the seconds query or how do I proceed to check where is the bottle neck?

Solution

  • Try replacing this expression:

    Account].[Account].[Level 02].ALLMEMBERS

    For the All member of the hierarchy, as I think DESCENDANTS will be a lot quicker if you use a member rather than a set as the first argument