Power BI troubleshooting SQL queries that are triggered by Power BI

[Guust_van_Dijck]

Hi Power BI people!

Currently we have many semantic models in the Power BI Service that are regularly refreshed by one and the same "service user". We set it up like this so that we don´t risk refresh fails because of de-activated accounts. The problem that we are experiencing with this setup is that our Ops colleagues find it very difficult to pinpoint who to talk to when Power BI is causing a heavy load on our SQL Server Databases. So I´m looking for a way to make this proces better.

In the ideal world we would be able to immediately see which query on SQL Server was triggered by which semantic model. I guess my questions are as follows;

- Is there an easy way to match the SQL Server query to the Power BI Semantic model?

- If the above proves difficult, are there any best practices regarding a "service user" setup that would help troubleshoot these issues?

- Any other tips regarding this issue?

Kind regards,

Guust

[lbendlin · Accepted solution]

Is there an easy way to match the SQL Server query to the Power BI Semantic model?

Not at all.  Take the Request ID and go hunting in your logs. Good luck.

If the above proves difficult, are there any best practices regarding a "service user" setup that would help troubleshoot these issues?

In each query add a fake (static) column that identifies the requestor.

Any other tips regarding this issue?

Don't use service principals if you care about auditing.

[Guust_van_Dijck]

Thanks a lot for your answer! Especially the fake static column suggestion could prove helpful!