SQLServerPedia

I'm currently holed up in my hotel room somewhere in Sweden facing up to the fact that I've barely started work on my presentation for SQLBits on Saturday. It's on the subject of using Analysis Services as a data source for Reporting Services, and as it happens one of the tips I'll be talking about came up at work today - handling parameterised calculated measure definitions - so I thought I'd blog about it too.

RS reports commonly use calculated measures in the WITH clause to get around that annoying problem that RS needs to know about its column names in advance. If you're creating a parameterised report which allows your users to choose which columns appear when it renders, you might be tempted to write your query something like this:

with member measures.test as
//this would actually be strtomember(@MyParameter) in the query
strtomember("[Measures].[Internet Sales Amount]")
select measures.test on 0,
topcount(
[Customer].[Customer].[Customer].members
*
[Date].[Calendar].[Calendar Quarter].members
, 100, measures.test) on 1
from [adventure works]

But performance isn't great here: the query executes in 23 seconds cold cache on my laptop. When you compare it with the un-parameterised version:

with member measures.test as
[Measures].[Internet Sales Amount]
select measures.test on 0,
topcount(
[Customer].[Customer].[Customer].members
*
[Date].[Calendar].[Calendar Quarter].members
, 100, measures.test) on 1
from [adventure works]

...which executes in a mete 2 seconds cold cache, then you'll be right in thinking we need to find a better approach. The culprit is of course the strtomember function; using any of the StrToX functions in a calculation is surefire way of killing query performance. Unfortunately because MDX parameters return strings (maybe we could get typed parameters in some future release?) we have to use strtomember to cast the uniquename string our parameter is returning to a member. The best way around this I've found is to create a named set in the With clause and parameterise that instead: it only gets evaluated once, and after that you can reference that set in your calculation so:

with
set mymeasure as {strtomember("[Measures].[Internet Sales Amount]")}
member measures.test as
mymeasure.item(0).item(0)
select measures.test on 0,
topcount(
[Customer].[Customer].[Customer].members
*
[Date].[Calendar].[Calendar Quarter].members
, 100, measures.test) on 1
from [adventure works]

This runs in a much more respectable 7 seconds on my laptop. But there's one more trick you can use though, our old friend non_empty_behavior. If we set it to the measure whose value we're actually displaying like this:

with
set mymeasure as {strtomember("[Measures].[Internet Sales Amount]")}
member measures.test as
mymeasure.item(0).item(0), non_empty_behavior= strtomember("[Measures].[Internet Sales Amount]")
select measures.test on 0,
topcount(
[Customer].[Customer].[Customer].members
*
[Date].[Calendar].[Calendar Quarter].members
, 100, measures.test) on 1
from [adventure works]

We're back down to 3 seconds on a cold cache.