SQL Server Analysis Services (SSAS) provides both OLAP & Data Mining functionality. However, after you have developed and deployed solutions you will find the need to begin managingthe server'sactivity. In this post I will provide you with an overview of the various techniques & tips you can use to monitor and in some cases manage SSAS.

SSAS Connection Architecture

There are three layers to connections in SSAS: Connections, Sessions, and Commands. Connections are just that, they represent a connection from a client to the server. Sessions provide statefulness for the connections such as transactions and command context.Youare then left withCommands. Commands are just that, they represent the individual queries sent to the SSAS server. As you can imagine these three layers are tied together: Connections have Sessions and Sessions have Commands.

Viewing SSAS Activity

Now that you understand the basics of SSAS connection architecture how do you start working with this data? Well, as with most things in SSAS, its not AS EASY when compared to the server's relational sibling. I begin with the easiest approach: tools.There are a few available tools that one can use to view and manage SSAS activity. First and foremost is that of SQLSentry's Performance Advisor for Analysis Services. This tool is hands down the best SSAS monitoring tool available. SQLSentry clearly filled a large void in the SSAS world. Moving on, there is a sample application called Activity Viewer found on CodePlex. This tool provides a basic, stripped-down activity monitor application that you can use. The tools I mentioned above provide this ability but perhaps you arein one of those shops that don't spend much money on 3rd party tools, nowwhat? Analysis Services ships with what is called Schema Rowsets. There are similar to the concept of Dynamic Management Views (DMVs) (in fact some people refer to SSAS Schema Rowsets as 'DMVs') found in the relational engine as they provide us with the dynamic activity of the SSAS server. To find out currently running Connections, Sessions, and Commands you simply need to query the following three Schema Rowsets:


Note: If you would like to see every SSAS Schema Rowset that is available simply run the following MDX:


Run the following MDX queries to return all current connection activity:






Killing SSAS Activity

Most importantly, not only does one need the ability to view SSAS activity but to controlvarious activities. Usually the reason behindcontrolling activity is simple, there is a long-running MDX command that is affecting concurrent users on the server. Again, what about those ofyou who don't have access to 3rd party tools? XML for Analysis (XMLA), which was introduced with SSAS 2005 provides a Cancel command. Using the Cancel command you can kill any of the three objects we have discussed today. As an example below is a XMLA script that I used to terminate a sample connection:

<Cancel xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"><ConnectionID>21ConnectionID>Cancel>

Killing SSAS Activity Automatically

Last but not least, what ifyou want to kill SSAS activity on a regular basis? Well,you canuse SQL Server Agent and another BI tool called SQL Server Integration Services (SSIS)! You can build a SSAS Activity Monitoring package from scratch or you can leverage Chris Webb's solution. Either way, these solutions provide a decent work around for the problem of having long running MDX queries bogging down your server.

Happy Monitoring!