Search code examples
c#cachingssasolapxmla

Configure proactive caching in Analysis Services with XMLA or C#

I am configuring proactive caching with MSSQL management studio using a wizard (please look at the attached screenshoots below). It works fine, but I have to configure the same from code (C#, XMLA or any other way).

I cannot find enough information how to do this. For example this page from documentation tells about cache clearing, but nothing about cache setup: http://technet.microsoft.com/en-us/library/ms186654.aspx

Can anybody help with this please? Any useful links or code samles would be welcome.

Thanks in advance

aaa

enter image description here

Solution

  • As a starting point, you could let Management Studio generate the XMLA alter script for you: In the first dialog of your question, configure the things as you like them to be, and then select "Script" in the top toolbar, and then the target where you want to see the XMLA.

    If you want to go the .net way, AMO would be the API to use (see http://msdn.microsoft.com/en-us/library/microsoft.analysisservices.aspx, but there are nearly no code examples in this documentation). You would probably want to set the ProactiveCaching property of the partition you want to change (see http://msdn.microsoft.com/en-us/library/microsoft.analysisservices.partition.proactivecaching.aspx) to a ProactiveCaching object having the correct settings (see http://msdn.microsoft.com/en-us/library/microsoft.analysisservices.proactivecaching.aspx).

    AMO coding normally works like this:

    • You connect to the server (Create a new Server object, call its Connect method).
    • You navigate through the object hierarchy from the server to the object you need (the specific partition object in your case).
    • You change properties. This just changes the structures locally in your copy of the cube data structures.
    • You call the Update property on the changed object or a parent of it to write the changes back to the server.
    • If there are no errors, you Disconnect from the server. Better still, you are using the C# using construct for the Server object in the first step above, as Server implements 'IDisposable'.