SQL Centre of Excellence

One of the most common things to need an IF statement for in MDX is to determine if a measure is NULL to ensure that you output Null instead of the actual calculation.

Mosha pioneered an interesting “trick” for avoiding the use of “IF” to account for Null handling in Budget Variance (See http://sqlblog.com/blogs/mosha/archive/2006/11/05/budget-variance-a-study-of-mdx-optimizations-evaluation-modes-and-non-empty-behavior.aspx)

Basically we can abuse the fact that in MDX Null multiplied by any other number is null.

Having used Mosha’s technique for Budget Variance, I realised that this can pretty much be used anywhere, so below I have a calculated member for YTD

SCOPE ([Reporting].[Period].[YTD]);
    this =  iif([Reporting].[Period].[This Period]=null,null,
Aggregate(  [Reporting].[Period].[This Period]  
             *
             PeriodsToDate( [Date].[Calendar].[Year],
                            [Date].[Calendar].CurrentMember
             )));  
    END SCOPE;

We can re-write this without the IF  as below and enjoy  faster performance:

SCOPE ([Reporting].[Period].[YTD]);
    this =  [Reporting].[Period].[This Period] *
Aggregate(  [Reporting].[Period].[This Period]  
             *
             PeriodsToDate( [Date].[Calendar].[Year],
                            [Date].[Calendar].CurrentMember
             )) / [Reporting].[Period].[This Period];  
    END SCOPE;
END SCOPE;   
blog comments powered by Disqus

Page List

Page List