Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Missing Members and the Formula Engine Cache

with one comment

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.

Written by Chris Webb

November 22, 2010 at 9:26 pm

Posted in MDX

One Response

Subscribe to comments with RSS.

  1. [...] Reporting Services-generated MDX, Subselects and Formula Caching       c) Missing Members and the Formula Engine Cache       d) Now() and the Formula Cache       e) [...]


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 )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 2,868 other followers

%d bloggers like this: