SSAS: Adding XMLA templates to SSMS

I use the Templates feature in SSMS a fair bit when I am working with XML/A, but I sometimes wonder how many people are aware that it exists. On my copy of SSMS, it is docked on the right hand side.

image

If you cannot see the Template explorer at all, you can access it through the View menu.

image

Have a look at the following list of templates that are available for MDX and XMLA for Analysis Services.

image

This feature is really great for XMLA queries. I don't really know of anyone that sits down and writes XMLA from scratch, especially with all the XML namespace declarations. Come to think of it, I don't know many people that write XMLA :)

But there are some things that you just need to use XMLA for and in addition to the built-in templates there are a couple of XMLA templates that I have added to my SSMS that I find useful.

The XMLA Templates for SSMS are found at the rediculously long path of 

%program files%\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\sqlworkbenchprojectitems\AnalysisServices\XMLA

I have added the following two templates to the management folder.

Cancel Template

This first one is useful when you get a run away query or some other process that is consuming excessive resources. You need to run one of the existing templates that ship with SSMS in order to find out which Connection, Session or SPID is consuming the resources. From there it is a simple matter of inserting the appropriate identifier in the template below. There is an existing Cancel template that is shipped with SSMS, but it does not include any of the possible parameters, so you have to guess what they are. I find it easier to use this one and delete the parameters I do not need.

<Cancel xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
   <!-- Choose one of the following 3 identifiers -->
   <ConnectionID></ConnectionID>
   <SPID></SPID>
   <SessionID></SessionID>
   <!-- True or False, Defaults to False-->
   <CancelAssociated></CancelAssociated>
</Cancel>

ClearCache Template

Sometimes it is necessary to start up a second instance of SSMS, if the resource consuming query/process is one that was issued from your machine. 

The next one comes in useful when doing performance testing, it allows you to clear the Analysis Services cache for the specified object. Whenever I needed to run this command I often found myself having to jump over to Jamie Thomson's blog here SSIS Junkie - SSAS- Clearing the Analysis Services cache to double check the syntax (that I told him about!). Now that I have it saved as a template I can just double click the template or drag it into an open XMLA window.

<ClearCache xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
   <Object>
      <!-- Remove Unused identifiers-->
      <DatabaseID></DatabaseID>
      <CubeID></CubeID>
      <MeasureGroupID></MeasureGroupID>
      <PartitionID></PartitionID>
   </Object>
</ClearCache>

Enjoy!

---------------------------------------

Of course those of you with the Analysis Services Stored Procedure Project installed have an even easier option than the XML/A templates, you can execute commands like the following straight from an MDX windows.

CALL ASSP.ClearCache()

CALL ASSP.CancelSpid(...)

Print | posted on Monday, September 10, 2007 12:25 AM

Comments on this post

# re: SSAS: Adding XMLA templates to SSMS

Requesting Gravatar...
I'll be damned - how have I never noticed that feature before. Very cool. Love that you can easily just add new templates right in SSMS.
Left by Kevin Dente on Sep 11, 2007 3:20 AM

Your comment:

 (will show your gravatar)