When are named sets in the MDX Script evaluated?

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.