MDX Scripts and Calculated Members

The other thing that struck me about the Time Intelligence article that I was talking about in my last post (read it here) is the way that Rob and Mosha have used MDX Scripts and calculated members together to solve a problem that, traditionally, I would have solved using calculated members alone, and the fact that this new approach is more effecient. The key paragraphs are on the second page:

To determine which state the current coordinate falls into, you’d typically use the IIF function (in Analysis Services 2000), or you could use the CASE operator (new to Analysis Services 2005). However, this approach results in dynamic checks during runtime evaluation of each cell, and the resulting MDX expressions tend to quickly become cumbersome and difficult to debug as the levels of nesting increase.

The Business Intelligence Wizard provides a much more efficient and elegant approach. The wizard uses the new MDX scripting syntax constructs to define specific scopes to which each calculation applies. Because Analysis Services evaluates SCOPE statements statically (once) when the MDX script executes, this technique inherently avoids unnecessary per-cell runtime checks.

Let’s take a look at one of the calculations they use in their article to understand exactly what this means, the 12 Month Moving Average. In AS2K, you’d typically write this as something like:

WITH MEMBER MEASURES.MOV AS ‘IIF(TIME.CURRENTMEMBER.LEVEL IS TIME.MONTH,
AVG(TIME.CURRENTMEMBER.LAG(11) : TIME.CURRENTMEMBER, MEASURES.[UNIT SALES])
, NULL)’
SELECT {MEASURES.[UNIT SALES], MEASURES.MOV} ON 0,
TIME.MEMBERS ON 1
FROM SALES

The important thing to note here is that because we only want this value to appear at the month level, we have to include the IIF(TIME.CURRENTMEMBER.LEVEL IS TIME.MONTH… check in the calculated member definition. And of course, this gets evaluated every time the calculated member is evaluated.

What Mosha and Rob are saying is that MDX Scripting allows you to avoid this unnecessary overhead. If you look at their version (at the bottom of this page) they still create a calculated member but it doesn’t do the work – this is done later by setting the scope to that calculated member and the month level, and putting the formula there. As they say in the second paragraph I quote above, because the scope is evaluated only once rather than on a per-cell basis, it’s more efficient.

The big question is, how much more efficient is this? I have no idea at this stage, but my guess is that for time intelligence-type calculations you’re probably only going to notice the difference on very, very big queries. However this approach doesn’t just apply to time intelligence-type calculations, it applies to lots of others too; and I bet for complex financial calculations that really hammer your CPU you’ll see a much bigger improvement on even the most basic queries. I’ll have to do some testing to find out.

Just goes to show how much we’re going to have to change the way we think when designing apps for AS2005…