Dynamic Named Sets and the Formula Cache

Reposted from Chris Webb's blog with the author's permission.

Here’s another scenario where the Formula Engine is unable to cache the results of calculations longer than the lifetime of a query (thanks to Akshai Mirchandani yet again for pointing this one out): when you use dynamic named sets inside calculations. To see this happening add the following calculations to the MDX Script of the Adventure Works cube:

CREATE SET NONDYNAMICSET AS [Date].[Day Name].MEMBERS;

CREATE DYNAMIC SET DYNAMICSET AS [Date].[Day Name].MEMBERS;

CREATE MEMBER CURRENTCUBE.MEASURES.NONDYNAMICCALC AS COUNT(NONDYNAMICSET);

CREATE MEMBER CURRENTCUBE.MEASURES.DYNAMICCALC AS COUNT(DYNAMICSET);

Now, clear the cache and run the following query twice:

SELECT MEASURES.NONDYNAMICCALC ON 0
FROM [Adventure Works]

Looking in Profiler, you can see that on the second run the query was answered from the formula engine’s flat cache:

When you run the following query using the calculation that references the dynamic named set:

SELECT MEASURES.DYNAMICCALC ON 0
FROM [Adventure Works]

…you can see that the cache does not get hit:

So be careful if you ever need to use them inside your calculations! Personally I’ve never done much with dynamic named sets at all, but Mosha has of course blogged extensively about several ways they can be useful.

Chris has been working with Microsoft BI tools since he started using beta 3 of OLAP Services back in the late 90s. Since then he has worked with Analysis Services in a number of roles (including three years spent with Microsoft Consulting Services) and he is now an independent consultant specialising in complex MDX, Analysis Services cube design and Analysis Services query performance problems. His company website can be found at http://www.crossjoin.co.uk and his blog can be found at http://cwebbbi.wordpress.com .