ASQA

Analysis Services Query Analyzer (ASQA) is a tool, integrated in SQL Server Management Studio, for troubleshooting MDX queries. It automatically executes all the required tasks to collect performance data and metrics of an MDX query, and presents results in a graphical and easy understandable way.

Yes! Analysis Services Query Analyzer (ASQA) is a complete FREE tool distributed under the MIT license and its source code is published on GitHub in this repository.

Analysis Services Query Analyzer (ASQA) is the combination of an Analysis Service custom assembly and an SQL Server Management Studio Addin (technically speaking, the correct term is VS Package). The former represents the engine of the tool and has the responsibility to execute all the required tasks to collect performance data and metrics of an MDX query following and respecting all the best practices. It must be installed on the same SSAS Multidimensional instance where the MDX query performance analysis will be executed. The latter has the responsibility of calling the execution of the analysis and presenting the results inside SQL Server Management Studio.

Troubleshooting MDX queries is a complex and time-consuming activity that:

• requires a strong knowledge of the MDX language and of the SSAS Multidimensional engine (fundamental to try to understand what happens behind the scenes during the resolution of the MDX query and to consequently identify where the bottlenecks, if any, can reside)
• implies the execution of many repetitive and manual tasks (needed to collect and analyze data and metrics about MDX query performance).

If Analysis Services Query Analyzer (ASQA), evidently, nothing can do about the first requirement, it can tremendously help to simplify and speed up the latter.

The current version of Analysis Services Query Analyzer (ASQA) fully supports all the versions of Analysis Services Multidimensional starting from the 2012 version (Build number: 11.0.x.x), and the ASQA Addin can be installed in all the versions of SQL Server Management Studio starting from the 2012 version (Build number: 11.0.x.x). It can also save all the analyses results in a dedicated database hosted in any version of SQL Server on premise starting from the 2012 version (Build number: 11.0.x.x) as well as on Azure SQL Database.

Good point! This is what we call Live Mode execution and is the default execution mode. But ASQA Assembly can also save all the results of an analysis in a dedicated SQL Server database on premise or on Azure instead of sending them back to the client. In this way, the client still needs to be up and running until the end of the analysis execution, but the client can be any software able to send a request of analysis to the ASQA Assembly! We call it Batch Mode execution and it only requires installing the ASQA SQL Server database (by simply using a specific menu item of the ASQA SSMS Addin).

A Batch Mode analysis, obviously, can be executed from the ASQA SSMS Addin, but can also be launched by a PowerShell script (the ASQA SSMS Addin has a specific menu item to automatically generate a PowerShell script for this) whose execution can be scheduled, for instance, during the night. In this way, it is also possible to execute analysis on an entire set of MDX queries (by using a PowerShell script, provided in the installation folder, that loops through all the MDX query scripts contained in a folder) and stores all the results of the analyses in the ASQA SQL Server database.

By simply using the ASQA SSMS Addin that has a specific window to filter the records of the ASQA SQL Server database and easily identifies the results of analysis we are interested in. Then they could be loaded and visualized by ASQA SSMS Addin as they were generated by a Live Mode execution.

The engine of Analysis Services Query Analyzer (ASQA) is an Analysis Service custom assembly and since SSAS tabular instance does not support Analysis Service custom assemblies, it cannot work with SSAS tabular instance . This is the main reason why Analysis Services Query Analyzer does not support DAX query.

The ASQA SSAS Assembly is forced to manage only one analysis at a time. This makes perfect sense since two analyses running simultaneously on the same server would negatively influence each other and, consequently, the results of both would not be accurate. Obviously, since it is possible to install the ASQA SSAS Assembly into multiple SSAS Multidimensional instances, it is possible to execute multiple analyses at the same time but only if they are executed on different SSAS Multidimensional instances. This is true for both execution modes: Live Mode and Batch Mode.

We are planning to develop many new features like an ASQA Excel addin (coming very soon!), an ASQA Pdf report or eXtended Events support, so … stay tuned!