SSAS Calculated Measure based on another calculated measure

Question

I have a MDX query below. This query works, the problem is it is slow, takes > 15 secs to return results even though the data set is not huge. I believe the query should execute under 2 secs (its also used on a landing page and the wait time is bothersome). The [Measures].[Star Rating] is causing the slow down because of all the IF THEN ELSE logic. All its doing is based on the [Mean Score], it finds the [Star Rating] from a lookup table based on the range in the lookup table. For e.g. if [Mean Score] < 86, [Star Rating] = 1 if [Mean Score] >= 86 and <= 90, [Star Rating] = 2

The [Mean Score] is a simple sum/count calculation. It can change based on the date range being used as parameter. Can you recommend either an optimization in the existing query below or recommend an alternate way to calculate [Star Rating] ?

The MDX query is below:

    WITH 
       MEMBER [Measures].[MeanScore] AS ([Measures].[Standard Point Assignment - Sum]/[Measures].[Episode Of Care HCAHPS Count])
       MEMBER [Measures].[StarRating] AS 
       CASE 
       WHEN [HCAHPS Star Rating].[HCAHPS Star Rating ID].CurrentMember IS [HCAHPS Star Rating].[HCAHPS Star Rating ID].[All] 
          THEN 
         CASE 
           WHEN [Measures].[HSR-HCHCAHPS Domain ID] = TAIL([HCAHPS Star Rating].[HCAHPS Star Rating ID].[HCAHPS Star Rating ID]).Item(0).Item(0).Properties('HCHCAHPS Domain ID') 
                  THEN 
               (
                [Measures].[Rating], 
                Tail([HCAHPS Star Rating].[HCAHPS Star Rating ID].[HCAHPS Star Rating ID]).Item(0).Item(0)
               )
           ELSE 
               (
                [Measures].[StarRating],
                Tail([HCAHPS Star Rating].[HCAHPS Star Rating ID].[HCAHPS Star Rating ID]).Item(0).Item(0).PrevMember
               )
         END 
       ELSE 
         CASE 
           WHEN [Measures].[MeanScore] > [HCAHPS Star Rating].[HCAHPS Star Rating ID].CurrentMember.Properties('Start', typed)
           AND [Measures].[HC-HCAHPS Domain ID] = [HCAHPS Star Rating].[HCAHPS Star Rating ID].CurrentMember.Properties('HCHCAHPS Domain ID', typed) 
                  THEN 
               (
               [Measures].[Rating], 
                [HCAHPS Star Rating].[HCAHPS Star Rating ID].CurrentMember
                )
           ELSE 
               (
                [Measures].[StarRating],
                [HCAHPS Star Rating].[HCAHPS Star Rating ID].CurrentMember.PrevMember
               ) 
         END 
     END 
SELECT 
    {
        [Measures].[Episode Of Care HCAHPS Count]
        ,[Measures].[Is Top Box]
        ,[Measures].[CompositeScore]   
        ,[Measures].[PromoterCount]
        ,[Measures].[PromoterPercent]
        ,[Measures].[PassiveCount]
        ,[Measures].[PassivePercent]
        ,[Measures].[DetractorCount]     
        ,[Measures].[DetractorPercent]
        ,[Measures].[StarRating]
        ,[Measures].[MeanScore]
    } ON COLUMNS, 
    NONEMPTYCROSSJOIN
    (
        {NONEMPTY([HCAHPS Domain].[HCAHPS Survey Methodology ID].[HCAHPS Survey Methodology ID])}
        ,DESCENDANTS(StrToSet('[Org Hierarchy].[Parent Key].&[118418]'))
        ,{[HCHCAHPS Domain].[HC Domain Group].[HC Domain Group]}
        ,{[HCHCAHPS Domain].[HCAHPS Domain Name].[HCAHPS Domain Name]}
        ,{[HCAHPS Question Answer].[Question Number].AllMembers}
     ) ON ROWS 
     FROM [CAHPS] 
     WHERE 
     (
        StrToMember("[Date].[Date].&[" + FORMAT(NOW()-365,"yyyy-MM-ddT00:00:00")  + "]",CONSTRAINED):StrToMember("[Date].[Date].&[" + FORMAT(NOW(),"yyyy-MM-ddT00:00:00")  + "]",CONSTRAINED)
     )

Denny Lee Denny Lee · Accepted Answer · 2016-11-16T04:14:24

In general, IIF and CASE statements within Analysis Services MDX could potentially see some performance degradations. While most IIF statements are relatively inexpensive, complex nested conditions (with lots of IIF statements), this would result in the Analysis Services formula engine will end up running the query cell-by-cell.

For performant queries, the goal is to have the query run in subspace mode (or block computation) instead of cell-by-cell mode. To do this within the context if IIF statements, please try to utilize SCOPE statements. A great reference to convert IIF statements to SCOPE is Mosha Pasumansky's blog post is Performance of IIF function in MDX.

By the way, for more information on subspace computation (vs. cell-by-cell), please refer to Section 3.2.1 of the Analysis Services Performance Guide

SSAS Calculated Measure based on another calculated measure

1 Answers