I recently found out the SSAS team added new trace information to Azure Log analytics. The new information allows you to find out the report and dataset in Power BI that generate each query.
To show you how this works. I captured the queries in AAS using Azure Log analytics as I described here.
I then make sure I add the column ” ApplicationContext_s” using the query below. This will give me the context the query was running under.
AzureDiagnostics
| where OperationName == "QueryBegin"
| project TimeGenerated, TextData_s, DatabaseName_s, ApplicationContext_s
| where TimeGenerated > ago(2h)
In this case it is the generated by the Power BI service and the column contains the DatasetId and ReportId as we know it in Power BI.
This is very useful if we want to figure out who is generating a particular load on the service.
But there is also another use of this, we can use the DatasetId or ReportId as filter to the query. This allows us to only see the data for the dataset or report we want.
AzureDiagnostics
| where OperationName == "QueryBegin"
| project TimeGenerated, TextData_s, DatabaseName_s, ApplicationContext_s
| where TimeGenerated > ago(2h)
| where ApplicationContext_s contains "b9d53eb4-01e7-4eb5-989b-a416769b9001"
A very useful addition to the logs that might come in handy when doing some debugging.
🙂 I have a nice AAS Log Analytics Stats Power BI dashboard that I have to use when we run into any type of memory, errors, or query pool related issues:) Allows us to track down the customer, database, and queries being used.
This is great. I’ve always wished we had this for Excel Pivot tables also.
sounds like a good blog post Dan 🙂
Blogging… it has been a while. Priorities have shifted a bit over the last few years. Working, family, coaching, running user groups, etc. takes up a lot of time. Plus my dogs and pets keep me very busy as well, not much free time for blogging to do at least the level that I would want to do or have done. I have lots of cool things that I would like to share;)