ResMon Cube Documentation

With Analysis Services 2008 and later, data from dynamic management views can be retrieved with SQL query syntax as described here. Therefore, it is possible to build an Analysis Services cube which uses Analysis Services DMV SQL queries as the data source.

The ResMon cube rolls up information about Analysis Services such as memory usage by object, perfmon counters, aggregation hits/misses, and current session stats.

Installation Instructions

Download ResMon from the Downloads tab and unzip.

Open CreateResMon.xmla in Management Studio, connected to the SSAS instance, and execute the script. For Analysis Services 2012, run CreateResMon2012.xmla instead.

If Analysis Services is not the default instance, then exit the XMLA script before running it. For Analysis Services 2008 find "MSAS 2008", for Analysis Services 2012 find "MSAS11", and replace with "MSOLAP$<Instance_Name>".

Connect to the SSAS instance in Object Explorer. Expand Databases... ResMon... Data Sources, then double click on the OlapServer data source.

[image:DataSourceObjectExplorer.PNG]

Click the ... button next to the connection string. Fix the server name to mention the SSAS instance (if it's not the default instance) and change the Initial Catalog to ResMon.

[image:DataSourceConnectionString.PNG]

Click OK twice. Then right click on the ResMon database in Object Explorer, choose Process, then click OK to run a Process Full (this one time only). Now the ResMon cube contains the first snapshot of SSAS memory usage and other stats.

Run some queries against the other databases on that SSAS server, then update the ResMon cube with another snapshot of SSAS memory usage and other stats by running the ProcessResMon.xmla script in Management Studio manually.

If you wish, you can setup a SQL Server Agent job which will, on a schedule, update the ResMon cube. The job step should look like the following and the command should contain the XMLA from ProcessResMon.xmla:

SqlAgentJobStep.PNG

The Sessions cube currently contains only a current snapshot of active session statistics. The script for processing it (which can be used manually or in a SQL Agent job step) is called ProcessSessionsCube.xmla.


Reports Off the ResMon Cube

Here are a few examples of reports that you can build yourself using Excel 2010 PivotTables/PivotCharts connected to the ResMon cube.

Memory Usage Trend Details: A PivotTable that shows the change in memory usage over time. The Time dimension isn't very pretty, but the column headers are showing two snapshots: November 4, 2010 at 11:53 (AM) versus 12:02 (PM). You can see that the Customer dimension is using the most memory at around 9MB:

MemoryUsageTrendPivotTable.PNG


Memory Usage Trend Chart: Showing the trend over time of SSAS memory usage for a certain object, divided by shrinkable and non-shrinkable:

MemoryUsageTrendChart.PNG


Perfmon Counters Snapshot: Showing a snapshot at a point in time for many relevant SSAS perfmon counters. (Make sure to filter by Time since the PERF COUNTER VALUE measure is currently marked as AggregationFunction=Sum.)

PerfmonCounters.PNG


Aggregation Hits/Misses History: Showing whether aggregations are being hit or missed. (You can see Fact Currency Rate is the only measure group where we're getting agg hits.)

AggregationHitsVersusMisses.PNG


Active Session Statistics:

SessionStats.PNG


Known Issues

When the Time dimension is processed, a new row is added keyed off the Now() function rounded to the nearest minute. If the Time dimension is processed in minute 35 of the hour and measure groups are processed in minute 36 of the hour, then an error will occur. In this case, try to start processing as soon as the clock on the server flips to the next minute and hope processing finishes within 59 seconds. This is the only workaround at the moment. If you have other suggestions, please start a discussion in the Discussions tab.

Credits

Many thanks to Edward Melomed, Program Manager with the Analysis Services team, for sharing this code.

Alternatives

An alternative to this ResMon cube is BISMServerMemoryReport which uses PowerPivot.

Last edited Mar 21, 2012 at 2:46 PM by furmangg, version 7

Comments

qma Mar 10 at 6:20 PM 
For some reason, I always have The connection either timed out or was lost

ACALVETT Feb 9, 2011 at 3:00 PM 
If you are going to monitor an instance, before executing the CreateResMon.xmla in Management Studio perform a Find and Replace. Find: MSAS 2008 Replace: MSOLAP$<Instance_Name>