SSMS Templates for SSAS Developers

Templates and Snippets are two nice little features in SSMS that allow you to quickly generate standard code statements.  They come with a fairly substantial list of generic statements to get you started, but if you’re OCD and/or lazy (like most good DBAs and developers) you can customize the existing ones or add a few of your own.

Snippets vs. Templates: What’s the differnce?

There’s not too much of a difference between snippets and templates other than the fact that snippets are accessible via the right-click menu while working on TSQL and XML files in SSMS and can have key fields defined which allows the user to tab through them like fields on a web form.

Unfortunately, snippets aren’t available for MDX windows so the avid SSAS developer looking to save a bit of time is stuck using templates.  This isn’t that big of an issue because 1) templates are easier to create than snippets and 2) SSAS developers shouldn’t spend nearly as much time in SSMS as DBAs and Database Developers do.

Using SSAS Templates

To access the templates, you can press Ctrl+Alt+T to open up the Template Browser.  At the top of the window are two icons that allow you to toggle between regular SQL Server templates and SSAS templates. And, as you can see from the screenshot below, there is a decent list of templates available for various SSAS tasks right out of the box:

To use a template, you can either double click it (which will open it up in a new window) or you can just click and drag the item into the current window.

Below is a quick screenshot where I’ve dragged the Basic Query template out into a blank MDX editor window:

If you want to change one of the standard templates (perhaps, like me, you’re not a fan of the formatting) just right-click the item in the Template Browser and select “edit” from the drop down menu.  This will open the template file in a new window and your changes can be made and saved directly in SSMS.

Below is what it looks like after making a few changes to the formatting of the original basic query template:

Demo: Creating a New Template to Clear the Cache

So far you’ve seen how to access and edit the canned templates.  So now I’m going to show you how to really take advantage of this feature by creating a brand new custom template from scratch.

Clearing the cache is an important part of SSAS performance related activities such as benchmarking and troubleshooting slow MDX queries. I’ve used the clear cache statement thousands of times over the years, but I couldn’t write it out from memory if my life depended on it 😦  It is embarrassing how many times I’ve googled this statement…thus making it an ideal candidate for a template 🙂

1. Open the Template Browser and choose the Analysis Services Templates by clicking on the cube icon at the top of the window.
2. Expand the XMLA –> Management path like below:
   
3. Right-click the Management folder, select New –> Folder and title it Clear Cache:
   
4. Now right-click the Clear Cache folder and select New –> Template and title it Clear Cache – Database
5. Right click the Clear Cache – Database template file and choose edit
6. Now just add the code to clear a database cache:

   

 

You can repeat the process for creating additional templates that clear the cache at the various levels below database, such as cube, measure group, partition, etc.  See this post for a list of all the XMLA clear cache statements.

Looking Ahead

Over time, you’ll start to build up a nice little collection of very useful templates.  And to make things easy, all the templates (default and custom) are stored as individual files in the following directory:

C:\Users\anton\AppData\Roaming\Microsoft\SQL Server Management Studio\11.0\Templates

So when it comes time to roll on to the next project, you can back up this directory and take it with you to the next client.