I often use the following XMLA code to clear database/cube/measure group cache in SSAS Multidimensional when I do performance testing between different MDX queries or versions of the same database. I am posting it here so that I can find it easily in future. You can use it to clear the cache at the database, cube or measure group levels.
<ClearCache xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine”>
YourDBName
YourCubeName
YourMeasureGroupName
</ClearCache>
If you need to clear the cache at Database level you don’t need the lines for Cube and Measure Group. Similarly if you are clearing the cache for the whole cube you don’t need the line for the measure group.
Thank You!
Can this be done with tabular models as well? because the database / cube is a little bit different there
Yes, the XMLA commands work against both Tabular and Multidimensional databases. Keep in mind that the cache itself contains different things for Tabular vs. Multidimensional. Multidimensional is disk based so it pulls all measure group files,… into memory (it’s cache) as needed. Tabular is memory based so the model files are always in memory themselves. The cache in Tabular stores uncompressed data and some query results.