Missing Members and the Formula Engine Cache

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

Continuing my occasional series on ways to prevent the Formula Engine cache from being able to cache values for longer than the lifetime of a query (ie forcing it to use ‘query’ scope instead of ‘global’ scope), here’s something new I found the other day: the presence of any ‘missing’ members in a query forces ‘query’ scope for the FE cache.

Take, for example, the following query in Adventure Works:

SELECT {[Measures].[Internet Sales Amount]} ON 0,
{[Customer].[Country].&[Australia]
,[Customer].[Country].&[DoesNotExist]}
ON 1
FROM [Adventure Works]

When you run this query, assuming you’ve not altered any of the default property settings on Adventure Works, you’ll see results that include just one row for Australia. The country ‘DoesNotExist’ does not exist on the Country hierarchy of the Customer dimension; but you don’t get an error because of how the MDXMissingMemberMode property has been set (this blog post gives a good overview of what this property does).

However, if you regularly delete members from your dimensions and you rely on MDXMissingMemberMode to avoid errors in queries you’ve written that reference these members, there’s a hidden performance penalty. Here’s an illustration: if you add the following calculated measure onto the MDX Script of the Adventure Works cube -

CREATE MEMBER CURRENTCUBE.MEASURES.CACHEABLE AS 1;

And then clear the cache and run the following query twice:

SELECT MEASURES.CACHEABLE ON 0,
{[Customer].[Country].&[Australia]}
ON 1
FROM [Adventure Works]

If you run a Profiler trace that includes the Get Data From Cache event, the second time the query runs you’ll see the Get Data From Cache event appear because SSAS is retrieving the value of the single cell returned in the cellset from the Formula Engine cache:

However, if you write a query that includes a member that does not exist and do exactly the same thing, you’ll see that the FE cache is no longer used in the second query:

SELECT MEASURES.CACHEABLE ON 0,
{[Customer].[Country].&[Australia]
,[Customer].[Country].&[DoesNotExist]}
ON 1
FROM [Adventure Works]

So, clearly, the presence of a ‘missing’ member is preventing the use of global scope in the FE cache. And as I said, if you’re relying on this functionality in production, you’re going to be seriously limiting the effectiveness of FE caching and overall query performance will be worse.

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 .