Query Performance Tuning - Expert Cube Development with SSAS Multidimensional Models - page 237

• Global Context : This means the results of calculations are cached until the

cache is dropped (usually when some form of processing takes place on the
server) and reused by subsequent queries run by other users as well as the
user who ran the original query.

Clearly the Global Context is the best from a performance point of view, followed by
the Session Context and then the Query Context; Analysis Services will always try to
use the Global Context wherever possible, but it is all too easy to accidentally write
queries or calculations that force the use of the Session Context or the Query Context.
Here's a list of the most important situations when that can happen:

• If you define any calculations (not including named sets) in the WITH clause of
a query, even if you do not use them, then Analysis Services can only use the
Query Context (see http://tinyurl.com/chrisfewith for more details).
• If you define session-scoped calculations but do not define calculations in the
WITH clause, the Session Context must be used.

• Using a subselect in a query will force the use of the Query Context

(see http://tinyurl.com/chrissubcache ), although this has been
addressed as of Analysis Services 2012 SP1 CU4 (see http://tinyurl.com/
subselects2012 ).

• Use of the CREATE SUBCUBE statement will force the use of the Session Context.

• When a user connects to a cube using a role that uses cell security, then the

Query Context will be used.

• When calculations are used that contain non-deterministic functions

(functions which could return different results each time they are called),
for example, the Now() function that returns the system date and time,
Username or any Analysis Services stored procedure, then this forces
the use of the Query Context.

Other scenarios that restrict caching
Apart from the restrictions imposed by cache context, there are other scenarios
where caching is either turned off or restricted.

When arbitrary-shaped sets are used in the WHERE clause of a query, no
caching at all can take place in either the Storage Engine or the Formula Engine.
An arbitrary-shaped set is a set of tuples that cannot be created by a cross-join,
for example:

({([Customer].[Country].&[Australia], [Product].[Category].&[1]),
([Customer].[Country].&[Canada], [Product].[Category].&[3])})