Chris Webb's BI Blog

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

When are named sets in the MDX Script evaluated?

with 5 comments

Interesting discovery I made last week: I was tuning a cube and noticed that all my queries, when they were run on a cold cache, were much slower than I was expecting – even the most basic query seemed to take at least 40 seconds. After a lot of head-scratching I looked in Profiler and found the answer to what was going on, and it turned out to be the fact that there were two named sets in the MDX Script that used very complex expressions and which together took 40 seconds to evaluate. Commenting them out reduced all my query times by 40 seconds. The problem was that I wasn’t referencing these sets in any of my queries…!

I had thought that named sets in the MDX Script were evaluated the first time they were actually used in a query but this is demonstrably not the case. Consider the following query:

with set mytest as bottomcount(
{[Department].[Departments].&[2],[Department].[Departments].&[3]}
*
{[Scenario].[Scenario].&[1],[Scenario].[Scenario].&[2] }
*
[Account].[Account].[Account].members
*
[Date].[Date].[Date].members
, 10,[Measures].[Amount])
select {} on 0,
mytest on 1
from [Adventure Works]

Executed on a cold cache on the Adventure Works cube it returns in 13 seconds on my laptop. Consider also the following very basic query:

select {[Measures].[Internet Sales Amount]} on 0,
[Date].[Calendar Year].members on 1
from [Adventure Works]

This returns in under a second on a cold cache on the Adventure Works cube. Now go into Visual Studio to edit the Adventure Works cube and add the set from the first query as a named set at the end of the MDX Script so:

create set myexpensiveset as
bottomcount(
{[Department].[Departments].&[2],[Department].[Departments].&[3]}
*
{[Scenario].[Scenario].&[1],[Scenario].[Scenario].&[2] }
*
[Account].[Account].[Account].members
*
[Date].[Date].[Date].members
, 10,[Measures].[Amount]);

If you then clear the cache and rexecute the second query, which used to complete in only 1 second, it will now take 13 seconds despite the fact it doesn’t reference this set in any way. If you take a look in Profiler you can see that the Execute MDX Script event, which is fired the first time you run a query on a cold cache, is now taking 13 seconds and that’s what’s causing the query as a whole to take so long. So named sets in the MDX Script are evaluated when the MDX Script is executed, and that takes place the first time you run a query after the cache has been cleared – either by running a ClearCache command or by processing your cube.

Written by Chris Webb

July 4, 2007 at 10:22 pm

Posted in MDX

5 Responses

Subscribe to comments with RSS.

  1. There is a good reason why named sets cannot be evaluated only when they are referenced. The MDX script must be executed entirely as a whole because later statements may depend on earlier ones. Evaluating named sets out of order could produce different results both because they may depend on something and because something may depend on them. For more details on order of execution here – http://sqljunkies.com/WebLog/mosha/archive/2005/12/31/cube_init.aspx
     

    Mosha

    July 5, 2007 at 2:24 pm

  2. Yes, that all makes sense – I guess I hadn\’t thought through the implications of it, that\’s all.

    Chris

    July 5, 2007 at 2:49 pm

  3. Can you comment on how to clear the cache in SSAS 2005?  I can\’t find any info on it in the BOL.
    Thanks.

    Unknown

    August 14, 2008 at 10:46 pm

  4. Chris

    August 20, 2008 at 11:30 am

  5. [...] when you’re trying to work out why your MDX Script takes a long time to execute – usually expensive named sets are the problem); more detail on SSAS locks; and the new Resource Usage [...]


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 2,866 other followers

%d bloggers like this: