How to optimize MDX that should conditionally decide between two Measures based on the half Year choosen (involving SCOPE, Descendants Statements)

Question

The [Conditional Forecast Cost] is generated by following SCOPE-Statements based on measures called "Cost" and "Forecast Cost".

These Statements work fine but it is too slow. Especially if the whole YEAR is choosen in the report (or even more Years). Waiting time can be more than 5 Minutes. Any Idea how to optimize this MDX ?

SCOPE ([Measures].[Conditional Forecast Cost]);           
THIS =    (IIF([DIM Date].[Half Year].&[1],[Measures].[Cost], [Measures].[Forecast Cost]));
END SCOPE;

SCOPE ([DIM Date].[Iso Month].[All].children, [Measures].[Forecast Costs]);           
    THIS =     SUM(
                  Descendants(
                               [DIM Date].[Iso Month].CurrentMember, 
                               [DIM Date].[Iso Month]
                             ), 
                    [Measures].[Conditional Forecast Cost]
                   );   
END SCOPE;

Background information:

First Statement represents the basic Logic.

If the first half year is choosen the Measure "Cost" should be applied.
For the second half year the Measure "Forecast Cost" should be applied.

That works fine as long as we are reporting on a monthly Level.

If the whole year should be aggregated the first Statement is not enough because the engine will not aggrate the way we have defined it with "IIF([DIM Date].[Half Year].&[1],[Measures].[Cost] ..." .

So the second SCOPE Statement with the Descendants Function forces the engine to do the aggregation from the lower monthly level. That works so far but it is really slow.

Additional information:

For now we would prefer to implement this Logic in the semnatic Layer with MDX and not in the ETL.
The "Cost" Measure is also a calculated Measure in the MDX.
The DIM Date Dimension has a hierachy just with "YEAR-Month" (of course some additional Attributes).

Yuri Abele Yuri Abele · Accepted Answer · 2017-07-28T06:47:07

Show please MDX-Script of MEASURES.[Cost].

Your IIF describes two sub-cubes => the SCOPE job. Transform it to such form (simplified):

MEASURES.[Conditional Forecast Cost] = [Measures].[Forecast Cost]
SCOPE([DIM Date].[Half Year].&[1])
    MEASURES.[Conditional Forecast Cost] = [Measures].[Cost];
END SCOPE;

You have recursion (very bad!): [Conditional Forecast Cost] calls [Forecast Cost], then [Forecast Cost] calls [Conditional Forecast Cost]! You have to rewrite it without recursion!

yabele.blogspot.com, www.xing.com/profile/Yuri_Abele

How to optimize MDX that should conditionally decide between two Measures based on the half Year choosen (involving SCOPE, Descendants Statements)

1 Answers