We can Clear Cache by using ClearCache command inXML forAnalysis (XMLA)
for a specified Database,Cube,MeasureGroup ,dimension or partition.
Once we clear the cache analysis servicewill rebuild the cache
We can use this command to check performance testing ofMDX Queries
Syntax for clear cache
<Command>
<ClearCache>
<Object>…Object>
ClearCache>
Command>
Clear Cache for Database
<ClearCache xmlns=“http://schemas.microsoft.com/analysisservices/2003/engine”>
<Object>
<DatabaseID>Adventure Works DW 2008DatabaseID>
Object>
ClearCache>
Clear Cache for Cube
<ClearCache xmlns=“http://schemas.microsoft.com/analysisservices/2003/engine”>
<Object>
<DatabaseID>Adventure Works DW 2008DatabaseID>
<CubeID>Adventure WorksCubeID>
Object>
ClearCache>
Clear Cache for Measure Group
<ClearCache xmlns=“http://schemas.microsoft.com/analysisservices/2003/engine”>
<Object>
<DatabaseID>Adventure Works DW 2008DatabaseID>
<CubeID>Adventure WorksCubeID>
<MeasureGroupID>Fact Internet Sales 1MeasureGroupID>
Object>
ClearCache>
Clear Cache for Dimension
<ClearCache xmlns=“http://schemas.microsoft.com/analysisservices/2003/engine”>
<Object>
<DatabaseID>Adventure Works DW 2008DatabaseID>
<DimensionID>Dim CustomerDimensionID>
Object>
ClearCache>
Clear Cache for partition
<ClearCache xmlns=“http://schemas.microsoft.com/analysisservices/2003/engine”>
<Object>
<DatabaseID>Adventure Works DW 2008DatabaseID>
<CubeID>Adventure WorksCubeID>
<MeasureGroupID>Fact Internet Sales 1MeasureGroupID>
<PartitionID>Internet_Sales_2001PartitionID>
Object>
ClearCache>
This gives error
Errors in the metadata manager.The object withID of ‘Internet_Sales_2001’, Name of ‘Internet_Sales_2001’ isof type ‘partition’ which does notsupport the requested operation.
But inBOOKS Online ofSQL Server 2008
ms–help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10md_2devguide/html/afad5c39–d4c3–4307–b3b9–a06617da0028.htm
It says that we can clear cache of partition also , but while we try to clear partition cache it gives error.
It sounds me like a bug.
Cheers
Amish Shah
January 15, 2010 at 6:55 pm |
Hello, i'm sorry for my poor english but i thank you for you help.I copy one ClearCache example on MDX editor and there is an error :"Executing the query …Query (7, 2) Analyseur : La syntaxe pour '<' est incorrecte.It is on the first '<' (<ClearCache xmlns= … )Execution complete"I suppose that the explanation is very simple, but i'm new 😉
January 20, 2010 at 10:00 am |
Christian it works fine for me. Are you running it in MDX query window?Have you passed proper ID?
January 20, 2010 at 10:01 am |
Is your MDX Editor in SQL Server Management Studio or some other tool?It works in MDX Query window in SQL Server Management Studio.
January 20, 2010 at 10:55 am |
Thanks for your reply, Indian people are considered gifted people in data processing 😉 My code come from 'the best practices" MS-PRESS : the red on '<' was because i didn't put the 'GO' before, but now it's 'batch' which is red. Nevertheless the log seems good to me (and i got the result : Executing the query … 01.Obtained object of type: Microsoft.AnalysisServices.AdomdClient.CellSetFormatting.Cell set consists of 351 rows and 3 columns.Done formatting.Execution complete 01.Executing the query … 11.Execution complete 11.Executing the query … 21.Obtained object of type: Microsoft.AnalysisServices.AdomdClient.CellSetFormatting.Cell set consists of 1 rows and 0 columns.Done formatting.Execution complete 21. Can we have more details on the red string in SSMS ? More important for me now, i got a pdf file and i need note or copy important items, and copy is disabled what is the solution ? i would ask MS but i don't know skilled people out of sellers 😉 P.S. i bought my book.