MDX Query using Aggregate Filter Calculated Member too slow

Question

I am new to MDX. I am trying to run the following MDX Query in an SSRS report and it is taking ages to finish running it. Sometimes I have to cancel the Query because it is too slow.

WITH 
  --All forms ordered ASC
  SET [OrderedSet] AS 
    Order
    (
      NonEmpty
      (
        [Form].[Number].[Number]
       ,[Measures].[Sales]
      )
     ,[Measures].[Sales]
     ,BASC
    ) 
  --Rank the forms in [orderedSet]
  MEMBER [Measures].[Rank] AS 
    Rank
    (
      [Form].[Number].CurrentMember
     ,[OrderedSet]
    ) 
  --Running Total
  MEMBER [Measures].[Running Total] AS 
    Sum
    (
      Head
      (
        [OrderedSet]
       ,(
          [Measures].[Rank]
         ,[Form].[Number].CurrentMember
        )
      )
     ,[Measures].[Sales]
    ) 
  --Total Sales
  MEMBER [Measures].[Total Sales] AS 
    Sum
    (
      [OrderedSet]
     ,[Measures].[Sales]
    ) 
  --Find the spending band Sales limits
  MEMBER [Measures].[3%Sales] AS 
    0.03 * [Measures].[Total Sales] 
  MEMBER [Measures].[8%Sales] AS 
    0.08 * [Measures].[Total Sales] 
  MEMBER [Measures].[18%Sales] AS 
    0.18 * [Measures].[Total Sales] 
  MEMBER [Measures].[38%Sales] AS 
    0.38 * [Measures].[Total Sales] 
  MEMBER [Measures].[62%Sales] AS 
    0.62 * [Measures].[Total Sales] 
  MEMBER [Measures].[82%Sales] AS 
    0.82 * [Measures].[Total Sales] 
  MEMBER [Measures].[92%Sales] AS 
    0.92 * [Measures].[Total Sales] 
  MEMBER [Measures].[97%Sales] AS 
    0.97 * [Measures].[Total Sales] 
  --Assign Sales limits for each spending bands
  MEMBER [Measures].[MinSales] AS 
    CASE 
      WHEN 
        [Form].[Number].CurrentMember = [Form].[Number].[0-3] 
      THEN 1
      WHEN 
        [Form].[Number].CurrentMember = [Form].[Number].[3-8] 
      THEN 
        [Measures].[3%Sales]
      WHEN 
        [Form].[Number].CurrentMember = [Form].[Number].[8-18] 
      THEN 
        [Measures].[8%Sales]
      WHEN 
        [Form].[Number].CurrentMember = [Form].[Number].[18-38] 
      THEN 
        [Measures].[18%Sales]
      WHEN 
        [Form].[Number].CurrentMember = [Form].[Number].[38-62] 
      THEN 
        [Measures].[38%Sales]
      WHEN 
        [Form].[Number].CurrentMember = [Form].[Number].[62-82] 
      THEN 
        [Measures].[62%Sales]
      WHEN 
        [Form].[Number].CurrentMember = [Form].[Number].[82-92] 
      THEN 
        [Measures].[82%Sales]
      WHEN 
        [Form].[Number].CurrentMember = [Form].[Number].[92-97] 
      THEN 
        [Measures].[92%Sales]
      WHEN 
        [Form].[Number].CurrentMember = [Form].[Number].[97-100] 
      THEN 
        [Measures].[97%Sales]
    END 
  MEMBER [Measures].[MaxSales] AS 
    CASE 
      WHEN 
        [Form].[Number].CurrentMember = [Form].[Number].[0-3] 
      THEN 
        [Measures].[3%Sales]
      WHEN 
        [Form].[Number].CurrentMember = [Form].[Number].[3-8] 
      THEN 
        [Measures].[8%Sales]
      WHEN 
        [Form].[Number].CurrentMember = [Form].[Number].[8-18] 
      THEN 
        [Measures].[18%Sales]
      WHEN 
        [Form].[Number].CurrentMember = [Form].[Number].[18-38] 
      THEN 
        [Measures].[38%Sales]
      WHEN 
        [Form].[Number].CurrentMember = [Form].[Number].[38-62] 
      THEN 
        [Measures].[62%Sales]
      WHEN 
        [Form].[Number].CurrentMember = [Form].[Number].[62-82] 
      THEN 
        [Measures].[82%Sales]
      WHEN 
        [Form].[Number].CurrentMember = [Form].[Number].[82-92] 
      THEN 
        [Measures].[92%Sales]
      WHEN 
        [Form].[Number].CurrentMember = [Form].[Number].[92-97] 
      THEN 
        [Measures].[97%Sales]
      WHEN 
        [Form].[Number].CurrentMember = [Form].[Number].[97-100] 
      THEN 
        [Measures].[Total Sales]
    END 
  --Create Spending bands based on the Running Total ranges
  MEMBER [Form].[Number].[0-3] AS 
    Aggregate
    (
      Filter
      (
        [OrderedSet]
       ,
          [Measures].[Running Total] > 0
        AND 
          [Measures].[Running Total] <= [Measures].[3%Sales]
      )
    ) 
  MEMBER [Form].[Number].[3-8] AS 
    Aggregate
    (
      Filter
      (
        [OrderedSet]
       ,
          [Measures].[Running Total] > [Measures].[3%Sales]
        AND 
          [Measures].[Running Total] <= [Measures].[8%Sales]
      )
    ) 
  MEMBER [Form].[Number].[8-18] AS 
    Aggregate
    (
      Filter
      (
        [OrderedSet]
       ,
          [Measures].[Running Total] > [Measures].[8%Sales]
        AND 
          [Measures].[Running Total] <= [Measures].[18%Sales]
      )
    ) 
  MEMBER [Form].[Number].[18-38] AS 
    Aggregate
    (
      Filter
      (
        [OrderedSet]
       ,
          [Measures].[Running Total] > [Measures].[18%Sales]
        AND 
          [Measures].[Running Total] <= [Measures].[38%Sales]
      )
    ) 
  MEMBER [Form].[Number].[38-62] AS 
    Aggregate
    (
      Filter
      (
        [OrderedSet]
       ,
          [Measures].[Running Total] > [Measures].[38%Sales]
        AND 
          [Measures].[Running Total] <= [Measures].[62%Sales]
      )
    ) 
  MEMBER [Form].[Number].[62-82] AS 
    Aggregate
    (
      Filter
      (
        [OrderedSet]
       ,
          [Measures].[Running Total] > [Measures].[62%Sales]
        AND 
          [Measures].[Running Total] <= [Measures].[82%Sales]
      )
    ) 
  MEMBER [Form].[Number].[82-92] AS 
    Aggregate
    (
      Filter
      (
        [OrderedSet]
       ,
          [Measures].[Running Total] > [Measures].[82%Sales]
        AND 
          [Measures].[Running Total] <= [Measures].[92%Sales]
      )
    ) 
  MEMBER [Form].[Number].[92-97] AS 
    Aggregate
    (
      Filter
      (
        [OrderedSet]
       ,
          [Measures].[Running Total] > [Measures].[92%Sales]
        AND 
          [Measures].[Running Total] <= [Measures].[97%Sales]
      )
    ) 
  MEMBER [Form].[Number].[97-100] AS 
    Aggregate
    (
      Filter
      (
        [OrderedSet]
       ,
          [Measures].[Running Total] > [Measures].[97%Sales]
        AND 
          [Measures].[Running Total] <= [Measures].[Total Sales]
      )
    ) 
SELECT 
  NON EMPTY 
    {
      [Measures].[MinSales]
     ,[Measures].[MaxSales]
     ,[Measures].[Form Count]
     ,[Measures].[Sales]
    } ON 0
 ,{
    [Form].[Number].[0-3]
   ,[Form].[Number].[3-8]
   ,[Form].[Number].[8-18]
   ,[Form].[Number].[18-38]
   ,[Form].[Number].[38-62]
   ,[Form].[Number].[62-82]
   ,[Form].[Number].[82-92]
   ,[Form].[Number].[92-97]
   ,[Form].[Number].[97-100]
  } ON 1
FROM 
(
  SELECT 
    [blah] ON COLUMNS
  FROM [Cube]
);

After few tests and google search, I know that the culprit is the calculated member that aggregates the filtered Form Numbers. Also the [Form].[Number].[Number] dimension is a huge dimension with hundreds of thousands of members. So was wondering if we create a calculated member in such a dimension, would that slow down the query and if so what can be a workaround?

Many Thanks

whytheq whytheq · Accepted Answer · 2016-10-28T12:18:13

CASE and FILTER are both slow mdx functions. For a trivial CASE it is easy enough to switch to IIF which generally performs better - but you have too many conditions to nest IIFs.

Three points that might help:

1.I noticed that you have a mistake in your case statements - you should be using the IS operator rather than the =

CASE 
  WHEN 
    [Form].[Number].CurrentMember IS [Form].[Number].[0-3] 
  THEN 1
  WHEN 
    [Form].[Number].CurrentMember IS [Form].[Number].[3-8] 
  THEN 
    [Measures].[3%Sales]
  WHEN 
    [Form].[Number].CurrentMember IS [Form].[Number].[8-18] 
  THEN 
    [Measures].[8%Sales]
  WHEN 
    [Form].[Number].CurrentMember IS [Form].[Number].[18-38] 
  THEN 
    [Measures].[18%Sales]
  WHEN 
    [Form].[Number].CurrentMember IS [Form].[Number].[38-62] 
  THEN 
    [Measures].[38%Sales]
  WHEN 
    [Form].[Number].CurrentMember IS [Form].[Number].[62-82] 
  THEN 
    [Measures].[62%Sales]
  WHEN 
    [Form].[Number].CurrentMember IS [Form].[Number].[82-92] 
  THEN 
    [Measures].[82%Sales]
  WHEN 
    [Form].[Number].CurrentMember IS [Form].[Number].[92-97] 
  THEN 
    [Measures].[92%Sales]
  WHEN 
    [Form].[Number].CurrentMember IS [Form].[Number].[97-100] 
  THEN 
    [Measures].[97%Sales]
END

2.The running order of your WITH clause is wrong - these various members [Form].[Number].[97-100] are calculated after they are used in the CASE statements.

3.The calculation you have for [Total Sales] I cannot imagine will require any sort of ordering so why use the ordered set? I fact you could just use a tuple with the [All] member of the hierarchy to get this value:

--Total Sales
MEMBER [Measures].[Total Sales] AS 
(
  [Form].[Number].[All]
 ,[Measures].[Sales]
)

So implementing the above we now have:

WITH 
  --All forms ordered ASC
  SET [OrderedSet] AS 
    Order
    (
      NonEmpty
      (
        [Form].[Number].[Number]
       ,[Measures].[Sales]
      )
     ,[Measures].[Sales]
     ,BASC
    ) 
  --Rank the forms in [orderedSet]
  MEMBER [Measures].[Rank] AS 
    Rank
    (
      [Form].[Number].CurrentMember
     ,[OrderedSet]
    ) 
  --Running Total
  MEMBER [Measures].[Running Total] AS 
    Sum
    (
      Head
      (
        [OrderedSet]
       ,(
          [Measures].[Rank]
         ,[Form].[Number].CurrentMember
        )
      )
     ,[Measures].[Sales]
    ) 
  --Total Sales
  MEMBER [Measures].[Total Sales] AS 
  (
    [Form].[Number].[All]
   ,[Measures].[Sales]
  )
  --Find the spending band Sales limits
  MEMBER [Measures].[3%Sales] AS 
    0.03 * [Measures].[Total Sales] 
  MEMBER [Measures].[8%Sales] AS 
    0.08 * [Measures].[Total Sales] 
  MEMBER [Measures].[18%Sales] AS 
    0.18 * [Measures].[Total Sales] 
  MEMBER [Measures].[38%Sales] AS 
    0.38 * [Measures].[Total Sales] 
  MEMBER [Measures].[62%Sales] AS 
    0.62 * [Measures].[Total Sales] 
  MEMBER [Measures].[82%Sales] AS 
    0.82 * [Measures].[Total Sales] 
  MEMBER [Measures].[92%Sales] AS 
    0.92 * [Measures].[Total Sales] 
  MEMBER [Measures].[97%Sales] AS 
    0.97 * [Measures].[Total Sales] 
  --Create Spending bands based on the Running Total ranges
  MEMBER [Form].[Number].[0-3] AS 
    Aggregate
    (
      Filter
      (
        [OrderedSet]
       ,
          [Measures].[Running Total] > 0
        AND 
          [Measures].[Running Total] <= [Measures].[3%Sales]
      )
    ) 
  MEMBER [Form].[Number].[3-8] AS 
    Aggregate
    (
      Filter
      (
        [OrderedSet]
       ,
          [Measures].[Running Total] > [Measures].[3%Sales]
        AND 
          [Measures].[Running Total] <= [Measures].[8%Sales]
      )
    ) 
  MEMBER [Form].[Number].[8-18] AS 
    Aggregate
    (
      Filter
      (
        [OrderedSet]
       ,
          [Measures].[Running Total] > [Measures].[8%Sales]
        AND 
          [Measures].[Running Total] <= [Measures].[18%Sales]
      )
    ) 
  MEMBER [Form].[Number].[18-38] AS 
    Aggregate
    (
      Filter
      (
        [OrderedSet]
       ,
          [Measures].[Running Total] > [Measures].[18%Sales]
        AND 
          [Measures].[Running Total] <= [Measures].[38%Sales]
      )
    ) 
  MEMBER [Form].[Number].[38-62] AS 
    Aggregate
    (
      Filter
      (
        [OrderedSet]
       ,
          [Measures].[Running Total] > [Measures].[38%Sales]
        AND 
          [Measures].[Running Total] <= [Measures].[62%Sales]
      )
    ) 
  MEMBER [Form].[Number].[62-82] AS 
    Aggregate
    (
      Filter
      (
        [OrderedSet]
       ,
          [Measures].[Running Total] > [Measures].[62%Sales]
        AND 
          [Measures].[Running Total] <= [Measures].[82%Sales]
      )
    ) 
  MEMBER [Form].[Number].[82-92] AS 
    Aggregate
    (
      Filter
      (
        [OrderedSet]
       ,
          [Measures].[Running Total] > [Measures].[82%Sales]
        AND 
          [Measures].[Running Total] <= [Measures].[92%Sales]
      )
    ) 
  MEMBER [Form].[Number].[92-97] AS 
    Aggregate
    (
      Filter
      (
        [OrderedSet]
       ,
          [Measures].[Running Total] > [Measures].[92%Sales]
        AND 
          [Measures].[Running Total] <= [Measures].[97%Sales]
      )
    ) 
  MEMBER [Form].[Number].[97-100] AS 
    Aggregate
    (
      Filter
      (
        [OrderedSet]
       ,
          [Measures].[Running Total] > [Measures].[97%Sales]
        AND 
          [Measures].[Running Total] <= [Measures].[Total Sales]
      )
    ) 
  --Assign Sales limits for each spending bands
  MEMBER [Measures].[MinSales] AS 
    CASE 
      WHEN 
        [Form].[Number].CurrentMember IS [Form].[Number].[0-3] 
      THEN 1
      WHEN 
        [Form].[Number].CurrentMember IS [Form].[Number].[3-8] 
      THEN 
        [Measures].[3%Sales]
      WHEN 
        [Form].[Number].CurrentMember IS [Form].[Number].[8-18] 
      THEN 
        [Measures].[8%Sales]
      WHEN 
        [Form].[Number].CurrentMember IS [Form].[Number].[18-38] 
      THEN 
        [Measures].[18%Sales]
      WHEN 
        [Form].[Number].CurrentMember IS [Form].[Number].[38-62] 
      THEN 
        [Measures].[38%Sales]
      WHEN 
        [Form].[Number].CurrentMember IS [Form].[Number].[62-82] 
      THEN 
        [Measures].[62%Sales]
      WHEN 
        [Form].[Number].CurrentMember IS [Form].[Number].[82-92] 
      THEN 
        [Measures].[82%Sales]
      WHEN 
        [Form].[Number].CurrentMember IS [Form].[Number].[92-97] 
      THEN 
        [Measures].[92%Sales]
      WHEN 
        [Form].[Number].CurrentMember IS [Form].[Number].[97-100] 
      THEN 
        [Measures].[97%Sales]
    END 
  MEMBER [Measures].[MaxSales] AS 
    CASE 
      WHEN 
        [Form].[Number].CurrentMember IS [Form].[Number].[0-3] 
      THEN 
        [Measures].[3%Sales]
      WHEN 
        [Form].[Number].CurrentMember IS [Form].[Number].[3-8] 
      THEN 
        [Measures].[8%Sales]
      WHEN 
        [Form].[Number].CurrentMember IS [Form].[Number].[8-18] 
      THEN 
        [Measures].[18%Sales]
      WHEN 
        [Form].[Number].CurrentMember IS [Form].[Number].[18-38] 
      THEN 
        [Measures].[38%Sales]
      WHEN 
        [Form].[Number].CurrentMember IS [Form].[Number].[38-62] 
      THEN 
        [Measures].[62%Sales]
      WHEN 
        [Form].[Number].CurrentMember IS [Form].[Number].[62-82] 
      THEN 
        [Measures].[82%Sales]
      WHEN 
        [Form].[Number].CurrentMember IS [Form].[Number].[82-92] 
      THEN 
        [Measures].[92%Sales]
      WHEN 
        [Form].[Number].CurrentMember IS [Form].[Number].[92-97] 
      THEN 
        [Measures].[97%Sales]
      WHEN 
        [Form].[Number].CurrentMember IS [Form].[Number].[97-100] 
      THEN 
        [Measures].[Total Sales]
    END 
SELECT 
  NON EMPTY 
    {
      [Measures].[MinSales]
     ,[Measures].[MaxSales]
     ,[Measures].[Form Count]
     ,[Measures].[Sales]
    } ON 0
 ,{
    [Form].[Number].[0-3]
   ,[Form].[Number].[3-8]
   ,[Form].[Number].[8-18]
   ,[Form].[Number].[18-38]
   ,[Form].[Number].[38-62]
   ,[Form].[Number].[62-82]
   ,[Form].[Number].[82-92]
   ,[Form].[Number].[92-97]
   ,[Form].[Number].[97-100]
  } ON 1
FROM 
(
  SELECT 
    [blah] ON COLUMNS
  FROM [Cube]
);

MDX Query using Aggregate Filter Calculated Member too slow

1 Answers