Fast dynamic named set calculation

Question

I have a long complex query with a lot of calculations and conditions but the main structure looks like this:

WITH
MEMBER [Id1] AS [Level].[Level1].CurrentMember.Member_Key
MEMBER [Id2] AS [Level].[Level2].CurrentMember.Member_Key
MEMBER [Level].[Level1].[FirstSet] AS NULL
MEMBER [Level].[Level1].[SecondSet] AS NULL
SET [Set 1] AS {some processed set members}
SET [Set 2] AS {some other processed set members}
SET [Common CrossJoin Set] AS [Level].[Level2].Members

MEMBER [Calculated Measure 1] AS
  IIF([Level].[Level].CurrentMember.Member_Key = 'FirstSet',
    SUM(existing [Set 1]),
    IIF([Level].[Level].CurrentMember.Member_Key = 'SecondSet',
      SUM(existing [Set 2]),
      SUM([Measures].[Measure1]) * 15
    )
  )

MEMBER [Calculated Measure 2] AS
IIF([Level].[Level].CurrentMember.Member_Key = 'FirstSet',
  SUM(existing [Set 1]),
  IIF([Level].[Level].CurrentMember.Member_Key = 'SecondSet',
    SUM(existing [Set 2]),
    SUM([Measures].[Measure2]) * 20
  )
)
SELECT 
  { [Id1], [Id2], [Calculated Measure 1], [Calculated Measure 2]} ON COLUMNS,
  { ([Common CrossJoin Set], [Level].[Level1].[FirstSet]),
    ([Common CrossJoin Set], [Level].[Level1].[SecondSet])
  } ON ROWS
FROM [Cube]

So resulted table looks like this:

║ ---------------║ ---------------------------║ Id1 ║ Id2 ║ Measure1 ║ Measure2 ║

║ L2 Member ║ L1.FirstSet Member ║ L2-1 ║ L1-8 ║ 1 ║ 5 ║

║ L2 Member ║ L1.FirstSet Member ║ L2-2 ║ L1-9 ║ 2 ║ 6 ║

║ L2 Member ║ L1.SecondSet Member ║ L2-3 ║ L1-98 ║ 3 ║ 7 ║

║ L2 Member ║ L1.SecondSet Member ║ L2-4 ║ L1-99 ║ 4 ║ 8 ║

The result is correct but the query is very slow (>4sec). My actual query is bigger and contains a lot of such Sets and measures so it seems like the problem is in existing function and overall structure that prevents engine from inner optimizations to be performed.

This kind of solution is wrong and ugly, but how can I rewrite it and get the same result faster?

Answer 1

I suspect that the bottleneck is because when you use Iif neither of the logical branches is NULL so you're not getting block mode calculations: this is a better way of using Iif : Iif(someBoolean, X, Null) or Iif(someBoolean, Null, x) but unfortunately in your case you cannot have null in either.

Maybe you could try implementing this type of pattern suggested by Mosha for replacing Iif:

WITH 
MEMBER Measures.[Normalized Cost] AS [Measures].[Internet Standard Product Cost]
CELL CALCULATION ScopeEmulator 
  FOR '([Promotion].[Promotion Type].&[No Discount],measures.[Normalized Cost])' 
  AS [Measures].[Internet Freight Cost]+[Measures].[Internet Standard Product Cost]
MEMBER [Ship Date].[Date].RSum AS Sum([Ship Date].[Date].[Date].MEMBERS), SOLVE_ORDER=10
SELECT 
 [Promotion].[Promotion Type].[Promotion Type].MEMBERS on 0
 ,[Product].[Subcategory].[Subcategory].MEMBERS*[Customer].[State-Province].[State-Province].MEMBERS ON 1
FROM [Adventure Works]
WHERE ([Ship Date].[Date].RSum, Measures.[Normalized Cost])

This is from this blog post about optimizing Iif: http://sqlblog.com/blogs/mosha/archive/2007/01/28/performance-of-iif-function-in-mdx.aspx

So looking at one of your calculations - this one:

MEMBER [Calculated Measure 1] AS
  IIF([Level].[Level].CurrentMember.Member_Key = 'FirstSet',
    SUM(existing [Set 1]),
    IIF([Level].[Level].CurrentMember.Member_Key = 'SecondSet',
      SUM(existing [Set 2]),
      SUM([Measures].[Measure1]) * 15
    )
  )

I think we could initially break it down to this:

MEMBER [Measures].[x] AS SUM(existing [Set 1])
MEMBER [Measures].[y] AS SUM(existing [Set 2])
MEMBER [Measures].[z] AS SUM([Measures].[Measure1]) * 15
MEMBER [Calculated Measure 1] AS
  IIF([Level].[Level].CurrentMember IS [Level].[Level].[Level].[FirstSet],
    [Measures].[x],
    IIF([Level].[Level].CurrentMember IS [Level].[Level].[Level].[SecondSet],
      [Measures].[y],
      [Measures].[z]
    )
  )  

Now trying to apply Mosha's pattern (not something I've tried before so you will need to adjust accordingly)

MEMBER [Measures].[z] AS SUM([Measures].[Measure1]) * 15
    MEMBER [Measures].[y] AS SUM(existing [Set 2])
    MEMBER [Measures].[x] AS SUM(existing [Set 1])
MEMBER [Calculated Measure 1 pre1] AS [Measures].[z]
CELL CALCULATION ScopeEmulator 
  FOR '([Level].[Level].[Level].[SecondSet],[Calculated Measure 1 pre1])' 
  AS [Measures].[y] 
MEMBER [Calculated Measure 1] AS [Calculated Measure 1 pre1]
CELL CALCULATION ScopeEmulator 
  FOR '([Level].[Level].[Level].[FirstSet],[Calculated Measure 1])' 
  AS [Measures].[x]