Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

MDX Scripts and Calculated Members

with one comment

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…

 

Written by Chris Webb

June 10, 2005 at 1:29 pm

Posted in MDX

One Response

Subscribe to comments with RSS.

  1. Chris, although the following statement may sound radical, I often say, that if MDX expression uses IIF function (or CASE for that matter) – I say it is not good, and probably has a bug. Of course, it is not always possible to avoid IIF\’s, and sometimes they are better then anything else, but I would compare them to "goto" in C – only to be used if absolutely needed. This is not just about performance, this is about correctness. Your expression which looks at current month would\’ve been correct in AS2K, but it will return wrong results in AS2005. More on it here in my old blog entry – http://www.sqljunkies.com/WebLog/mosha/archive/2004/11/09/5033.aspx

    Mosha

    June 10, 2005 at 5:48 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,202 other followers

%d bloggers like this: