Wednesday, March 24, 2010

Clear Analysis Services cache

As you probably know, Analysis Services caches results of queries on a cube. Thus, if you're making a new query soon after the previous one, most probably you get the same results even you have new data in the underlying relational database. In most cases, it makes Analysis Services performance better. However, sometimes you need to get up=to-date results from a cube instantly. The simplest way I know is:
Open SQL Server Management studio and connect to an Analysis Services instance.
1) Select a database that contains a cube you want to be updated.
2) Run a new XMLA query query.

3) Insert content below.
<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<ClearCache>
<Object>
<<DatabaseID&gtAdventure Works DW 2008R2 SE</DatabaseID>
<CubeID>Adventure Works DW</CubeID>
</Object>
</ClearCache>
</Batch>

4) replace a database ID with database name that you selected

5) copy a cube's ID from the cube's properties and update CubeID parameter.

6) Run a query and ensure that no errors have been returned

The query works for SQL Server 2008 (might be working for other version but I didn't check it yet)

4 comments:

Anonymous said...

The other way is to restart SSAS service.

Nick M.

Dmitriy Chernyavsky said...

Yep, restarting SSAS will help. However, in some cases we don't have ability to do anything on a service level, for instance, when we use external hosting for SSAS with restricted permissions, but still need to force updating the data.

Anonymous said...

I can't belive you have't contacted me so far about SSAS. Is it an easy product to learn? I don't think so :)


Nick M

Dmitriy Chernyavsky said...

Hi Nick,
I hope it's not too late.
At the moment I switch between SSAS, SharePoint,and TeamCity/MSBuild activities a few times a week. So can't say that I really put my shoe too deeply into SSAS. When I do, I'll get back to you, especially if I get involved into new topics, like PowerPivot. Thanks, Dmitri