CODEWITHSUNDEEP
ssasmdxolap
devonuto
devonuto

Reputation: 423

MDX Performance Issues on Count & Sum of Filtered Members/Measures

I Have the following MDX query I need to run on a cube (which I don't have access to change).

This particular query is taking around 1.5 minutes to run, which is just far too long. I've been searching for a way to speed it up, but I'm not having a heap of luck.

Can anyone see a way to improve this query? I've been tearing my hair out for the last couple of days, so any help would be greatly appreciated!

`WITH
    MEMBER [Measures].[1-99_Count] AS 
        COUNT(FILTER ([Scam].[Scam Ref].AllMembers, 
            ([Measures].[Amount Lost]>=1 AND [Measures].[Amount Lost]<=99)))
    MEMBER [Measures].[1-99_Amount] AS 
        SUM(FILTER ([Scam].[Scam Ref].AllMembers, 
        ([Measures].[Amount Lost]>=1 AND [Measures].[Amount Lost]<=99)),
            Iif(IsEmpty([Measures].[Amount Lost]),0,[Measures].[Amount Lost]))
SELECT {[Measures].[1-99_Count],
    [Measures].[1-99_Amount]} ON COLUMNS,
    [First Resolved On Date].[Month].[Month] ON ROWS
FROM [Infocentre]
WHERE ([First Resolved On Date].[Date].[Date].&[20140101]:[First Resolved On Date].[Date].[Date].&[20150623],
    [Scam].[Scam Category Level1].&[{d9d6bc38-e73e-e411-9a82-0a713f2121f7}])`

Upvotes: 1

Views: 534

Answers (2)

whytheq
whytheq

Reputation: 35605

(just for fun!)

Your first measure can definitely be improved via the following. Count(Filter is a pattern you can get rid of most of the time. Mosha blogged about this pattern, which can be improved, here:
http://sqlblog.com/blogs/mosha/archive/2007/11/22/optimizing-count-filter-expressions-in-mdx.aspx

I've also tried to improve the second calculation as well. If this condition is true [Measures].[Amount Lost] >= 1 AND [Measures].[Amount Lost] <= 99 then this must mean that this is false IsEmpty([Measures].[Amount Lost]) so maybe both conditions can be covered by 1 condition. Also replaced your 0 with null - SSAS is a lot happier (& faster) with null:

WITH 
  MEMBER [Measures].[1-99_Count] AS 
    Sum
    (
      [Scam].[Scam Ref].ALLMEMBERS
     ,IIF
      (
        [Measures].[Amount Lost] >= 1 AND [Measures].[Amount Lost] <= 99
       ,1
       ,null
      )
    ) 
  MEMBER [Measures].[1-99_Amount] AS 
    Sum
    (
      [Scam].[Scam Ref].ALLMEMBERS
     ,IIF
      (
        [Measures].[Amount Lost] >= 1 AND [Measures].[Amount Lost] <= 99
       ,[Measures].[Amount Lost]
       ,null
      )
    ) 
SELECT 
  {
    [Measures].[1-99_Count]
   ,[Measures].[1-99_Amount]
  } ON COLUMNS
 ,[First Resolved On Date].[Month].[Month] ON ROWS
FROM [Infocentre]
WHERE 
  (
      [First Resolved On Date].[Date].[Date].&[20140101]
    : 
      [First Resolved On Date].[Date].[Date].&[20150623]
   ,[Scam].[Scam Category Level1].&[{d9d6bc38-e73e-e411-9a82-0a713f2121f7}]
  );

Upvotes: 0

devonuto
devonuto

Reputation: 423

In the end, logic prevailed and I was able to get the cube owners to add a new dimension to the cube, which means I no longer need to try and get this dog's breakfast working.

Upvotes: 1

Related Questions