Chris Webb's BI Blog

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

Parameterising Calculated Measure Definitions

with 9 comments

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.

Written by Chris Webb

February 26, 2008 at 9:06 pm

Posted in MDX

9 Responses

Subscribe to comments with RSS.

  1. Chris,
     
    What happens if you do:
     
    with member measures.test as//this would actually be strtomember(@MyParameter) in the querystrtomember("[Measures].[Internet Sales Amount]"), non_empty_behavior= strtomember("[Measures].[Internet Sales Amount]")
    select …
     
    And if it doesn\’t work – at the risk of asking a dumb question – why not?
    Thanks

    Harsh

    February 26, 2008 at 10:09 pm

  2. It looks like you are still on the old version (AS2005). In AS2008 the StrToMember in such scenario should give you the same perfomance as without it.

    Mosha

    February 26, 2008 at 11:57 pm

  3. Harsh – keeping the strtomember in the definition itself, but adding the non_empty_behavior, gives me a query time of five seconds cold cache on my laptop. So still good, but not the best possible performance.
     
    Mosha – sorry to be so behind the times…! I\’m starting on my first AS2008 project next week, and I\’m looking forward to seeing the performance gains on a real world cube.

    Chris

    February 27, 2008 at 6:21 pm

  4. hi,

    Suppose i have 2 tables: Customer and Orders. Orders is my fact table. How can i create measures for the following query:
    – How much customers have orders above 1000 euros in 2008? (in percentages)
    How can i do this in a MDX query?

    Tables:
    Customers (dimension)
    OrderLines (facttable): ordernr, customer, item, count, price

    Thank you in advance,

    Adriaan

    Unknown

    March 11, 2008 at 1:50 pm

  5. It\’s fairly easy to do this. You first create derived column in your OrderLines table (or in a view or in the dsv) which is Count * Price, which gives you the value of each order (Order Value). You can then build a cube with a Customer dimension and one measure group based on the OrderLines table, and that measure group should contain a measure based on the new Order Value column. You can then create a calculated measure which returns the percentage of customers with orders greater than €1000; the definition would look something like this:
    count(filter(Customer.Customer.Customer.Members, Measures.OrderValue>1000))/count(Customer.Customer.Customer.Members)

    Chris

    March 12, 2008 at 11:24 pm

  6. hi,Ok, that\’s great! I have only one problem, the devider (count(Customer.Customer.Customer.Members)) is always the total number of customers, but in my case i have a drilldown table, so you can also drilldown on the customers dimension. So, the number of customers should be dependent on the choosen drilldown level, is that possible?thank you,Adriaanbtw, i have ordered your book \’MDX solutions\’ to learn mdx!

    Unknown

    March 14, 2008 at 11:38 am

  7. Yes, that\’s possible. You need to change the expression to
    Count( Existing Customer.Customer.Customer.Members)

    Chris

    March 14, 2008 at 1:07 pm

  8. Existing function is not available for mondrian cubes ;( see http://mondrian.pentaho.org/documentation/mdx.phpIs there any other method to do the same? Can i do something with IN keyword?thanks,Adriaan

    Unknown

    March 14, 2008 at 1:35 pm

  9. Hmm, try something like
    Count(Filter(Descendants(Customer.Customer.Currentmember, Customer.Customer.Customer), Measures.OrderValue>1000))
    Count(Descendants(Customer.Customer.Currentmember, Customer.Customer.Customer))
     

    Chris

    March 18, 2008 at 2:32 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,113 other followers

%d bloggers like this: