SQL Server Analysis Services 2005/2008 articles,blogs,scripts,FAQs,tools,books

Q: How do I clear Analysis Services (SSAS) database cache?

A: To clear a cache you should use XMLA command ClearCache. You can choose to clear cache for database, cube or measure group. Note: you should specify object IDs and not names. In many cases this might not be the same. To see ID of the object you can open database in the BIDS, then select object (database, cube or measure group) and check value for property ID.

Example of command to clear cache for database:

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

Example of command 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>

Example of command 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>

You cannot clear cache for partition. You will get following error message:

Errors in the metadata manager. The object with ID of 'Internet_Sales_2001', Name of 'Internet_Sales_2001' is of type 'partition' which does not support the requested operation.

If you are testing one MDX script performance, you should first clear cache and then submit query. You can easily submit clear cache XMLA statement and MDX query in the same batch:

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

SELECT [Measures].[Internet Sales] ON 0
FROM [Adventure Works]
go