SSAS Events Analyzer

If you want to monitor a production Analysis Services instance, you do not have many options available out of the box. The SQL Server Profiler is a tool that has been deprecated in SQL Server 2016, but it is still supported for Analysis Services workloads (see more info on MSDN).

You might use AS Trace as a tool to automate the capture of Profiler events, but the profiler API have an additional overhead that can be lowered using the new Extended Events platform. You can find a good introduction to Extended Events written by Mark Vaillancourt that references other article on the same topic. In SQL Server 2016 there is a user interface to create, stop, and displays Extended Events sessions in Analysis Services 2016, described by Adam Saxton in a blog post.

This project started from the articles described above to create a simple monitoring system that can be easily applied to any server with a minimal effort. The goals were the following:

1. Monitor a production server with a minimal impact, collecting information about query and process
2. Analyze data to locate most expensive queries
3. Analyze data to detect increasing processing time

The initial focus was on Tabular models, but in reality the same technique can be applied to Multidimensional. You might want to add events in Multidimensional to analyze access to aggregations, but you should minimize the number of events logged in order to avoid slowing performance, so you might consider extending the events captured based on your specific requirements and workload.