Recently we had the need to analyse the queries made by the users on Azure Analysis Services and to cross reference that data with Azure AS metrics. For example to see exactly which queries are the cause for high QPU’s or Memory and see who made them on which application.
Currently Azure AS allows you to configure an Extended Events session to collect events from your Analysis Services database:
But there’s no easy way to export or save that data to do some further analysis. You can only watch live data and it’s not very user friendly:
We tried to use the good old ASTrace but it’s not compatible with Azure Analysis Services and it’s not a very good practice because it basicaly create a Profiler Session that will be deprecated soon.
Because we desperately needed to analyse the user queries to identify bottlenecks my amazing BI team at DevScope build an great tool called “Azure-As-Trace” that will allow you to point to a Analysis Services Instance and instantly start collecting the events you want and store them in the file system in JSONL format.
You can download it or contribute to it at github: https://github.com/DevScope/Azure-AS-Tracer
It’s very simple to use you just need to download the binaries and change in the config file ‘AzureASTrace.exe.config’ the following parameters:
|ConnectionStrings/AnalysisServices||The connection string to the Analysis Services instance you want to monitor|
|AppSettings/XEventTemplateFilePath||The path to the XEvents trace template to create the monitoring session on the Analysis Services Instance|
|AppSettings/OutputFolder||The path to the Output Folder that will hold the JSONL files|
After that you have two options:
- Run AzureASTracer as a console application, by simply executing AzureASTrace.exe
- Run AzureASTracer as a windows service by running ‘setup.install.bat’ and start the service
Either way when running the events will be saved on this on the Output folder, AzureASTrace will create a file for every Event Type subscribed and group the files by day:
To analyze those events you can this Power BI Desktop template: