Just wanted to share my experience on the role of cache while browsing the cube.
I have attended one workshop on SSAS conducted by Mr. Amit Bansal in Mumbai. While doing the Hands-on-lab I came across a behavior of cache while browsing the ROLAP partition without proactive caching. We were testing the ROLAP partition by updating the data in the data warehouse to reflect the changes immediately in the cube. After updating the data in the data warehouse database and refreshing the cube, the updated values were not reflecting in the cube. The reason being the cube is reading the data from the cache. Also as proactive caching is not enabled, the cache is not getting updated automatically. At that time a question came to my mind whether the cache can be cleared manually using some command so that the updated data will reflect in the cube…
Browsing the Books Online and some technical stuffs, brought me to the following conclusion,
1. In case of MOLAP partition with proactive caching enabled, the cache is updated immediately after the silence interval.
2. In case of ROLAP partition with proactive caching enabled, the cache is updated immediately after the silence interval.
3. In case of ROLAP partition without proactive caching enabled, the cache is not updated implicitly.
In that case the cache gets rebuild (cleared off) by the following ways,
A. After restarting the SSAS service the cache gets cleared off and the new changes starts reflecting in the cube. (Not practical)
B. After processing the ROLAP partition.
C. Using the ClearCache (XMLA) command. This command flushes the cache for a specified database, dimension, cube, measure group, or partition on an Analysis Services instance. It can be called explicitly from an application, job etc.
Run the following command to clear the cache of the cube,
<!--To clear the cache of an entire cube --> <ClearCache xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <Object> <DatabaseID>RolapProactiveCaching</DatabaseID> <CubeID>Adventure Works DW</CubeID> </Object> </ClearCache>
Add on elements as per the requirement like MeasureGroupID, PartitionID to the above script for clearing specific object,
<!--XMLA Command to clear the cache of a SSAS Database / Cube / MeasureGroup / Partition --> <ClearCache xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <Object> <DatabaseID>RolapProactiveCaching</DatabaseID> <CubeID>Adventure Works DW</CubeID> <MeasureGroupID>Fact Reseller Sales</MeasureGroupID> <PartitionID>Fact Reseller Sales Current</PartitionID> </Object> </ClearCache>
Note: I have tried for database, cube and measure group. But it is not working for a single partition within the measure group and throws the following error
(Errors in the metadata manager. The object with ID of ‘Fact Reseller Sales Current’, Name of ‘Fact Reseller Sales Current’ is of type ‘partition’ which does not support the requested operation.)
You can try the same and let me know if it is working for anybody.
Hope you enjoy this…
Like us on FaceBook | Follow us on Twitter
Join the fastest growing SQL Server group on FaceBook
4 Comments on “SQL Server: Use of ClearCache (XMLA) command in ROLAP partition without Proactive Caching”
Hi, due to the business needs, I created ROLAP cubes with 4 dimensions. However, data is not updated in the real time, unless the cube is processed. Do you know what’s wrong?
Thank you in advance for your help and assistance.
Have you enabled proactive caching for the ROLAP partition. If not; enable it and just refresh / reconnect the cube after the data changes. As I said in this blog, the cache is not updated implicitly for a ROLAP partition without proactive caching.
Can i know how can i generic … like i give the server name and Cube name so that it can clear the cache ?
Saying “generic” means you want to have the sever name and cube name configurable to clear the cache of a cube on a particular server. One possible solution which I can think of right now is to have a .net application using which you can execute the required XMLA command on a particular server. You can make use of the ADOMD.NET client libraries and add a reference to the Microsoft.AnalysisServices.AdomdClient assembly. Using this you can write a small code to form the required XMLA command and execute the same on the desired connection.