Proactive caching for Analysis Services databases - performance techniques and better usage

By default SQL Server has a pool of memory that is used to store both execution plans and data buffers. This pool of memory is managed by default within Analysis Services for SQL Server and such percentage of the pool allocated to either execution plans or data buffers fluctuates dynamically, depending on the state of the system. The part of the memory pool that is used to store execution plans is referred to as the procedure cache and execution plan will have the components such as Query Plan & Execution Context. A brief notes about these 2 components from BOL:

• Query Plan
  
  The bulk of the execution plan is a re-entrant, read-only data structure used by any number of users. This is referred to as the query plan. No user context is stored in the query plan. There are never more than one or two copies of the query plan in memory: one copy for all serial executions and another for all parallel executions. The parallel copy covers all parallel executions, regardless of their degree of parallelism.
  
  
• Execution Context
  
  Each user that is currently executing the query has a data structure that holds the data specific to their execution, such as parameter values. This data structure is referred to as the execution context. The execution context data structures are reused. If a user executes a query and one of the structures is not being used, it is reinitialized with the context for the new user.
  

To manage the memory efficiently SQL Server Analysis Services provides several proactive caching configuration options that enable you to maximize performance, minimize latency, and schedule processing. This will simplify the process of managing data obsolescence and also the settings will determine how frequently the multidimensional OLAP structure, which is called MOLAP (Multi-dimension OLAP) cache. This is managed by storage (MOLAP storage) provides best query response and sometimes some latency is expected. In case of ROLAP (Relational OLAP) real-time storage will let the users  immediately browse the most recent changes in a data source, but at the penalty of significantly poorer performance than multidimensional OLAP (MOLAP) storage because of the absence of precalculated summaries of data and because relational storage is not optimized for OLAP-style queries.

To minimize this latency you may need to clear the cache to maximize the performance, such as using proactive caching. While there are methods to minimize the latency such as user queries against an OLAP object are made against either ROLAP storage or MOLAP storage, depending whether recent changes have occurred to the data and how proactive caching is configured. The actual execution plan works out the process of directing the queries against source data in MOLAP storage until changes occur in the data source. So to minimize latency, after changes occur in a data source, cached MOLAP objects can be dropped and querying switched to ROLAP storage while the MOLAP objects are rebuilt in cache. After the MOLAP objects are rebuilt and processed, queries are automatically switched to the MOLAP storage. The cache refresh can occur extremely quickly for a small partition, such as the current partition - which can be as small as the current day, so it is better to perform such actions during less-traffic hours on the SQL Server instance. Further to maximize performance while also reducing latency, caching can also be used without dropping the current MOLAP objects. Queries then continue against the MOLAP objects while data is read into and processed in a new cache, this ensures the better performance but may result in queries returning old data while the new cache is being built.

As referred earlier you may need to clear the cache, which is Analysis Services (SSAS) database cache. To perform this action you can take help of XMLA command 'ClearCache' whereby you can clear for a database, cube or measure group. You must specifiy the object ID and not by the names, so to get the Object you can take help of BIDS tool to select the relevant object  such as database, cub or measure group and check value under PropertyID option.

To clear the cache for database:

<ClearCache xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>Adventure Works DW</DatabaseID>
</Object>
</ClearCache>

To clear cache for cube:

<ClearCache xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>Adventure Works DW</DatabaseID>
<CubeID>Adventure Works DW</CubeID>
</Object>
</ClearCache>

To clear cache for measure group:

<ClearCache xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>Adventure Works DW</DatabaseID>
<CubeID>Adventure Works DW</CubeID>
<MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>
</Object>
</ClearCache>

Finally if you have applications in which your users need to see recent data and you also want the performance advantages of MOLAP storage, SQL Server Analysis Services offers the option of proactive caching to address this scenario, particularly in combination with the use of partitions. Proactive caching is set on a per partition and per dimension basis. Proactive caching options can provide a balance between the enhanced performance of MOLAP storage and the immediacy of ROLAP storage, and provide automatic partition processing when underlying data changes or on a set schedule.