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!