Subset with nonempty function too slow

Question

Hi. I have a MDX query:

WITH 
MEMBER [Measures].[DS_TY] AS 
  '([Transaction Type].[All Transaction Type].[Daily Sales], [Measures].[TY])' 
MEMBER [Measures].[ST_TY] AS 
'([Transaction Type].[All Transaction Type].[Stock Transfer], [Measures].[TY])' 
MEMBER [Measures].[IS_TY] AS 
  '([Transaction Type].[All Transaction Type].[IServ], [Measures].[TY])' 
MEMBER [Measures].[TOT_TY] AS 
  '([Transaction Type].[All Transaction Type], [Measures].[TY])' 
MEMBER [Measures].[DS_LY] AS 
  '([Transaction Type].[All Transaction Type].[Daily Sales], [Measures].[LY])' 
MEMBER [Measures].[ST_LY] AS 
'([Transaction Type].[All Transaction Type].[Stock Transfer], [Measures].[LY])' 
MEMBER [Measures].[IS_LY] AS 
  '([Transaction Type].[All Transaction Type].[IServ], [Measures].[LY])' 
MEMBER [Measures].[TOT_LY] AS 
  '([Transaction Type].[All Transaction Type], [Measures].[LY])' 
MEMBER [Measures].[2] AS 
  'iif([LY]=0 Or [LY]=Null, Null, ([TY] - [LY])/[LY])' 
MEMBER [Measures].[4] AS 
  '[1] - [TY]' 
MEMBER [Measures].[6] AS 
  '[5] - [TY]' 
SET smeasures AS 
'{[DS_TY],[ST_TY],[IS_TY],[TOT_TY],[DS_LY],[ST_LY],[IS_LY],[TOT_LY],[1],[2],[3],[4],[5],[6]}' 
MEMBER [Measures].[TY] AS 
  '[Measures].[Gross Up Sales YTDTY Amt (Allocation)]' 
MEMBER [Measures].[LY] AS 
  '[Measures].[Gross Up Sales YTDLY Amt (Allocation)]' 
MEMBER [Measures].[1] AS 
  '[Measures].[Quota Amt YTDTY (Allocation)]' 
MEMBER [Measures].[3] AS 
  '[Measures].[%Perf YTDTY GrossUp (Allocation)]' 
MEMBER [Measures].[5] AS 
  '[Measures].[Quota Annual Amt TY (Allocation)]' 
SELECT smeasures ON COLUMNS, 
SUBSET (ORDER ( NONEMPTY(

    [Brand Customer].[Brand Customer].[Customer].members 
  , smeasures), 
  [Brand Customer].currentmember.name, 
  basc 
), 0, 5) ON ROWS 
FROM [CBrand] 
WHERE ( 
      { 
        [Time].[Time].[Year].[2020].[November] 
      }, 
      [Transaction Type].[All Transaction Type], 
{[Brand Org].[Level 02]}- 
DESCENDANTS({[Brand Org].&[D2_BTR2099_01], [Brand Org].&[D2_DTR2099_99], [Brand Org].&[D2_GTR2099_02]}) 
    ) 

I am trying to implement pagination with SUBSET and to return only non empty rows using NONEMPTY() function. But the query takes too long even if I am just getting 5 customers based on the third parameter in subset. Is it because I have too many measures? I am also new to MDX and I would like to ask if there's a better and faster way to do this.

Thank you very much

Answer 1

Did you resolve your issue ? Or could we help further ?
We are glad to hear your feedback!

Answer 2

See whether you can reduce the list of measures that you ask the engine to check the customers for being not empty against. As it is, the engine might end up, internally, calculating every measure for every customer in order to obtain the non-empty list from which the subset is to be extracted.