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,
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 –


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

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:

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.

One response

  1. Pingback: Where to store calculated measures – SSAS cube or SSRS reports? - Some Random Thoughts

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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: