THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jorg Klein

Microsoft Business Intelligence consultant from the Netherlands

SSAS - Clear SSAS cache with an SSIS package

Often I see developers on different forums asking how they can clear the SSAS cache. You can achieve this by restarting SSAS, which is done quite often.
Restarting is not necessary though, it's possible to clear the cache of an SSAS database with an XMLA script. Use the script below and replace "YourSsasDatabaseId" with the ID of your SSAS database and "YourSsasCubeId" with the ID of your SSAS cube.

<ClearCache xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <
Object
>
    <
DatabaseID>YourSsasDatabaseId</DatabaseID
>
    <
CubeID>YourSsasCubeId</CubeID
>
  </
Object
>
</
ClearCache>

Please note that you must use the ID's of the database/cube and not the name! You can find the ID's of these objects at the properties in BIDS or in SSMS. As you can see on the screenshot below, the cube/database name is not always the same as the ID. This particular cube has ‘Finance' as its name and ‘DW' as its ID!

Cube Properties

You can run this script manually from SSMS or automatically using SSIS. To run it from SSMS, right click your SSAS database and choose New Query > MDX. Although this is an XMLA script and not MDX it can be executed as MDX script.

Running this script from SSMS is useful but I think most developers would want to clear the cache automatically. Fortunately SSIS has a ‘Analysis Services Execute DDL Task' that can execute this script!

task1

 

 

 

Just configure the task for your SSAS instance and copy/paste the script in the SourceDirect box as shown below.

task2

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

With the possibilities SSIS offers you can now clear the cache anytime you want. For example you could run it right after you have processed the cube. A lot of developers restart the server after processing but with this script that is no longer necessary!

Thanks to Jamie Thomson for sharing this script on his blog!

Published Friday, March 13, 2009 11:54 PM by jorg
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About jorg

Jorg Klein, Microsoft Business Intelligence consultant/architect from the Netherlands.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement