MDX query optimization while using CrossJoin

Question

I am writing an MDX query in which i am selecting some Measures and while selection i have a where condition in which i am doing a cross join two facts , one is date and another a unique id and i am passing around 2000 unique ids and the query is taking around 20 minutes to execute and give the result.

Please find below query for the same

    SELECT {[Measures].[TOTAL1], [Measures].[TOTAL2], [Measures].[TOAL3]} ON  COLUMNS,
            " + " {TOPCOUNT(FILTER([ID].[Ids].MEMBERS, 
                            [ID].CurrentMember  > 0),
                              5,[Measures].[TOTAL])} " + "ON ROWS 
    FROM [CHARTS] 
    WHERE({[Date].&[2015-09-01 00:00:00.0]}*{[NUM].[1],[NUM].[10],"
                + "[NUM].[18],[NUM].[47],[NUM].[52],[NUM].[105],[NUM].[126],[NUM].[392],"
                + "[NUM].[588],[NUM].[656],[NUM].[995],[NUM].[1005],[NUM].[1010],[NUM].[1061]})";

The straight mdx without the string manipulation operators (+) is as follows:

SELECT 
  {
    [Measures].[TOTAL1]
   ,[Measures].[TOTAL2]
   ,[Measures].[TOAL3]
  } ON COLUMNS
 ,{
    TopCount
    (
      Filter
      (
        [ID].[Ids].MEMBERS
       ,
        [ID].CurrentMember > 0
      )
     ,5
     ,[Measures].[TOTAL]
    )
  } ON ROWS
FROM [CHARTS]
WHERE 
    {[Date].&[2015-09-01 00:00:00.0]}
  * 
    {
      [NUM].[1]
     ,[NUM].[10]
     ,[NUM].[18]
     ,[NUM].[47]
     ,[NUM].[52]
     ,[NUM].[105]
     ,[NUM].[126]
     ,[NUM].[392]
     ,[NUM].[588]
     ,[NUM].[656]
     ,[NUM].[995]
     ,[NUM].[1005]
     ,[NUM].[1010]
     ,[NUM].[1061]
    };

Can you please tell me the different performance optimization techniques for the same.

whytheq whytheq · Accepted Answer · 2016-04-19T19:34:32

TopCount is slow if you use the third ordering parameter - it is better to order the data first and then feed your pre-ordered set into TopCount with just 2 parameters:

WITH 
  SET [S0] AS 
    Filter
    (
      [ID].[Ids].MEMBERS
     ,
      [ID].CurrentMember > 0
    ) 
  SET [S1] AS 
    Order
    (  
      [S0]
     ,[Measures].[TOTAL]
     ,BDESC
    ) 
  SET [S2] AS 
    TopCount
    (
      [S1]
     ,5
    ) 
SELECT 
  {
    [Measures].[TOTAL1]
   ,[Measures].[TOTAL2]
   ,[Measures].[TOAL3]
  } ON COLUMNS
 ,[S2] ON ROWS
FROM [CHARTS]
WHERE 
    {[Date].&[2015-09-01 00:00:00.0]}
  * 
    {
      [NUM].[1]
     ,[NUM].[10]
     ,[NUM].[18]
     ,[NUM].[47]
     ,[NUM].[52]
     ,[NUM].[105]
     ,[NUM].[126]
     ,[NUM].[392]
     ,[NUM].[588]
     ,[NUM].[656]
     ,[NUM].[995]
     ,[NUM].[1005]
     ,[NUM].[1010]
     ,[NUM].[1061]
    };

MDX query optimization while using CrossJoin

1 Answers