Hi Friends,

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,

Add on elements as per the requirement like MeasureGroupID, PartitionID to the above script for clearing specific object,

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…

 

Regards

Amit Karkhanis

Like us on FaceBook Follow us on Twitter

Join the fastest growing SQL Server group on FaceBook