Using XMLA Command to Clear Cache of a SQL Server Analysis Service Database

Problem
One of the Business Intelligence developers in my company approached me yesterday with a dilemma. He wanted to know if there was a way to clear the cache of an Analysis Services database other than by recycling the Analysis Services service in SQL Server. At first I started to tell him, but figured it would be smarter to document the same and share the information. Below is the a process that can be used for SQL Server 2005 and alter versions.

Solution
You can clear the cache of an Analysis Services Database, Cube, Measure Group or a Dimension using the ClearCache XMLA command. While specifying the values for Analysis Services Database, Cube, Measure Group or a Dimension, you need to specify the Object ID rather than its name. You can easily find the Object ID by opening the Analysis Services Databases within Business Intelligence Development Studio (BIDS). Select any object in Object Explorer and look for the Object ID value within the Properties window.

Here are the steps.

1. Connect to an Analysis Service Instance using SQL Server Management Studio.
2. In the Object Explorer, expand the Analysis Server Node, Expand Databases -> Right click on the Analysis Services Database and select the New Query … XMLA option from the drop down list as shown in the snippet below.

Here are the commands you would use to clear the cached data.

XMLA script to Clear Cache for an Analysis Services Database

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

If you are going to test the performance of a specific MDX query then first clear the cache and then execute the MDX query.

---

XMLA script to Clear Cache for a Cube

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

---

XMLA script to Clear Cache of a Measure Group

<ClearCache xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>Adventure Works DW 2008</DatabaseID>
<CubeID>Mined Customers</CubeID>
<MeasureGroupID>Exchange Rates</MeasureGroupID>
</Object>
</ClearCache>

---

XMLA script to Clear Cache of a Dimension

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

Next Steps

• Review these other Analysis Services Tips