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

• Never use late binding functions like LookupCube , or StrToMember ,

StrToSet without the Constrained flag, inside calculations since they
have a serious negative impact on performance. It's almost always possible
to rewrite calculations so they don't need to be used; in fact, the only valid
use for StrToMember or StrToSet in production code is when using MDX
parameters. The LinkMember function suffers from a similar problem but is
less easy to avoid using.

• Use the NonEmpty function wherever possible; it can be much more

efficient than using the Filter function or other methods. Never use
NonEmptyCrossjoin either. It's deprecated, and everything you can
do with it you can do more easily and reliably with NonEmpty .

• Lastly, don't assume that whatever worked best in earlier versions of

Analysis Services is still best practice for Analysis Services 2012. In general
you should always try to write the simplest MDX code possible initially, and
then only change it when you find performance is unacceptable. Many of
the tricks that existed to optimize common calculations for earlier versions
now perform worse on Analysis Services 2012 than the straightforward
approaches they were designed to replace.

Caching
We've already seen how Analysis Services can cache the values returned in the cells
of a query, and how this can have a significant impact on the performance of a query.
Both the Formula Engine and the Storage Engine can cache data, but may not be
able to do so in all circumstances; similarly, although Analysis Services can share the
contents of the cache between users there are several situations where it is unable to
do so. Given that in most cubes there will be a lot of overlap in the data that users are
querying, caching is a very important factor in the overall performance of the cube and
as a result ensuring that as much caching as possible is taking place is a good idea.

Formula cache scopes
There are three different cache 'contexts' within the Formula Engine, which relate
to how long data can be stored within the cache and how that data can be shared
between users:

• Query Context : This means that the results of calculations can only be cached

for the lifetime of a single query and so cannot be reused by subsequent
queries or by other users.

• Session Context : This means the results of calculations are cached for the

lifetime of a session and can be reused by subsequent queries in the same
session by the same user.