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?

Can you replace these statements `[Level].[Level].CurrentMember.Member_Key = 'FirstSet'` for definite members using the `IS` operator e.g. `[Level].[Level].CurrentMember IS [Level].[Level].[Level].[FirstSet]` — whytheq, Nov 03 '16 at 22:30
What is the purpose of these NULLstatements? `MEMBER [Level].[Level1].[FirstSet] AS NULL` — whytheq, Nov 03 '16 at 22:33
I tried replacing with IS operator, but it does not work. The purpose of NULL-members is just to create an empty row with specific set in it for each member in [Common CrossJoin Set] (or [Level].[Level2].Members). — K V, Nov 05 '16 at 14:46
Why does it not work - `IS` is the standard way of checking equality between members and will be faster: https://msdn.microsoft.com/en-us/library/ms145997.aspx — whytheq, Nov 06 '16 at 09:05
Yes, I know. Maybe because member in this case is NULL, that's why I compare it by member_key which is set by name. Anyway, the comparing part of this algorithm is not a bottleneck. It takes approximately 60-100 ms from total execution time, but its like a few percents only. What do you think, maybe there is other way to fundamentally rewrite the query? — K V, Nov 06 '16 at 14:57

score 0 · Answer 1 · answered Nov 06 '16 at 17:39

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]

Hi whytheq, Thank you for your answer. I understood the idea of this solution and implemented it in my query so the results are the same but there are no improvements in performance so far. Even the Cells Calculated number is almost the same in MdxStudio. — K V, Dec 23 '16 at 10:13

Fast dynamic named set calculation

1 Answers1

Linked