Monitoring queries from Power BI Service using Extended Events in SSMS

[mmcanelly]

I'm working on setting up monitoring of my queries from the Power BI service to SQL Server each time a semantic model refreshes. I'm using SSMS v18.12, and it looks like Extended Events in SSMS can do what I need. The only piece I can't figure out is how to identify which dataset/semantic model each query is coming from.

Currently my setup is like this:

• Monitoring sql_batch_starting and sql_batch_completed events
• Filtering on client_hostname field to only get events for queries coming from the Power BI service
• Tracking sql_text, database_name, and username fields

Does anybody know if there's any event or field within Extended Events that would identify which semantic model in the Power BI service each request is coming from?

[mmcanelly]

Thank you @Anonymous. I tried replicating what you have here in Profiler, but I'm not seeing the DatasetID (or the other parameters that you show in red in your screenshot). My SQL:BatchStarting event just shows the query itself without any IDs included. Are you doing something on the Power BI end of things to pass that DatasetID in when the query runs?

[Anonymous]

Hi,@mmcanelly 

You need to open SQL Server Profiler in SSMS first.

Then go to Power BI Service and click on refresh.

Then go back to SQL Server Profiler to find the place I have boxed in the picture, and after that drop down the SQL below to find the DatasetId:

Finally, open the semantic model in Power BI Service and find the corresponding semantic model that performs the refresh by using the datasetId in the address bar.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,
Fen Ling,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mmcanelly]

Hi @Anonymous, I followed your steps, but I'm not seeing the DatasetId or any of the other fields that you show in your screenshot. Mine is just showing the query that ran from the Power BI refresh, without any additional information to identify the dataset.

Did you have to set something additional up in Power BI to include the dataset ID when it sends the query?

[Anonymous · Accepted solution]

Hi,@mmcanelly 

After connecting to SQL Server Profiler, you need to open the corresponding report in Power BI Service and click refresh in the upper right corner.

Then go back to SQL Server Profiler to find the statement in the red box.

And then pull the SQL statement to the bottom to find the DatasetId.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,
Fen Ling,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mmcanelly]

I should also mention that we don't have a Premium capacity and are doing everything with Pro licenses, and it looks like you might be using Premium based on the ApplicationName in your screenshot. I'm not sure if that difference is relevant or not.

[Anonymous]

Hi,@mmcanelly 

I am glad to help you.  

According to your description, you want to know how to identify which semantic model a Power BI Service request is coming from? 

If I understand you correctly, then you can refer to my solution. 

You can use SQL Server Profiler for tracing, through which you will get the DatasetId, ReportId, and VisualId corresponding to the executed operation: 

Then in the address bar of Power BI, you can find the corresponding DatasetId. Corresponding the DatasetId, you can see which semantic model your request in Power BI Service corresponds to. 

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,
Fen Ling,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.