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

If your users frequently run queries that use arbitrary-shaped sets then this can
represent a very serious problem, and you should consider redesigning your cube
to avoid it. The following blog entries discuss this problem in more detail:
• http://tinyurl.com/tkarbset
• http://tinyurl.com/chrisarbset
Even within the Global Context, the presence of security can affect the extent to which
cache can be shared between users. When dimension security is used the contents of
the Formula Engine cache can only be shared between users who are members of roles,
which have the same permissions. Worse, the contents of the Formula Engine cache
cannot be shared between users who are members of roles which use dynamic security
at all, even if those users do in fact share the same permissions.
Cache warming
Since we can expect many of our queries to run instantaneously on a warm cache,
and the majority at least to run faster on a warm cache than on a cold cache, it makes
sense to preload the cache with data so that when users come to run their queries
they will get warm-cache performance. There are two basic ways of doing this:
running CREATE CACHE statements and automatically running batches of queries.
The CREATE CACHE statement
The CREATE CACHE statement allows you to load a specified subcube of data into the
Storage Engine cache. Here's an example of what it looks like:
CREATE CACHE FOR [Adventure Works] AS
({[Measures].[Internet Sales Amount]}, [Customer].[Country].[Country].
MEMBERS,
[Date].[Calendar Year].[Calendar Year].MEMBERS)
More detail on this statement can be found at http://tinyurl.com/catcache .
The CREATE CACHE statements can be added to the MDX Script of the cube so they
execute every time the MDX Script is executed, although if the statements take a long
time to execute (as they often do), this might not be a good idea. They can also be run
after processing has finished from an Integration Services package using an Execute
SQL task or through ASCMD, and this is a much better option because it means you
have much more control over when the statements actually execute - you wouldn't
want them running every time you cleared the cache, for instance.