Now(), The Formula Engine Cache And The Where Clause

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

Back in 2009 I blogged about how the use of the Now() function inside calculated members prevents the results of those calculations being cached for longer than the lifetime of a query. It might be worth rereading that post before you carry on to get some background on the problem here:
http://cwebbbi.wordpress.com/2009/09/10/now-and-the-formula-cache/

Today I had an interesting email from a customer (thank you, Per!) showing me something I didn't know: that using the Now() function in the Where clause of a query has the same effect.

Here's the repro. On the MDX Script of the Adventure Works cube, I created the following calculated measure:

CREATE MEMBER CURRENTCUBE.MEASURES.TEST AS [Measures].[Internet Sales Amount] + 1;

Now consider the following query:

SELECT {MEASURES.TEST} ON 0,

{[Date].[Day Name].&[1]}

ON 1

FROM [Adventure Works]

WHERE(

STRTOMEMBER("[Date].[Calendar Year].&[" + "2003"  +  "]")

)

The first time the query is run you can see in Profiler the SSAS Storage Engine retrieving values; the second time it's run you can see the values for MEASURES.TEST being returned from the Formula Engine cache.

Now consider this second query:

SELECT {MEASURES.TEST} ON 0,

{[Date].[Day Name].&[1]}

ON 1

FROM [Adventure Works]

WHERE(

STRTOMEMBER("[Date].[Calendar Year].&[" + cstr(Year(Now())-10)  +  "]")

)

It returns the same result as the previous query (at least so long as the current year is 2013), but you can see in Profiler that the second time the query is run, once again the Storage Engine cache is queried and the Formula Engine cache is not used:

So clearly the use of the Now() function in the Where clause is enough to prevent the use of global scope by the Formula Engine cache, and some limited testing suggests the same applies for other non-deterministic functions like Username() - which is hardly surprising. Something to watch out for, then.

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/ .