mssqltips logo

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

By:   |   Updated: 2010-08-20   |   Comments (2)   |   Related: 1 | 2 | 3 | 4 | > Analysis Services Measure Groups

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.

run XMLA query from SSMS

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


Last Updated: 2010-08-20


get scripts

next tip button



About the author




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Tuesday, June 02, 2015 - 10:50:03 PM - Dinesh DBA Back To Top

How to delete multiple SSAS DB uisng XMLA ?


Sunday, August 22, 2010 - 3:01:44 AM - gill Back To Top
I m confused. Which sql server 2008 is compatible for RMS 2.0 (STORE OPRATION)



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools