Missing Members and the Formula Engine Cache

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.