Performance Tuning · Power BI · Power BI Desktop

Monitoring Power BI Desktop Activity Using Extended Events In SQL Server Management Studio

Something I do all the time when performance tuning Power BI is use SQL Server Profiler to monitor query and refresh activity. There’s a handy external tool that lets you open up Profiler with a connection to Power BI Desktop; if you’re using Power BI Premium you can also connect Profiler up to a published dataset using the XMLA Endpoint. Profiler is a bit old-school though and likely to lead to snarky comments from SQL Server DBAs (it’s not deprecated for Analysis Services though!) who’ll say you should be using Extended Events (aka xEvents) instead. And guess what – did you know you can use Analysis Services xEvents to monitor activity in Power BI Desktop?

Doing this is quite simple. The only challenge to overcome is how to connect SQL Server Management Studio to an instance of Power BI Desktop, and the easiest way to do this is to install DAX Studio as an external tool, open it connected to Power BI Desktop, and then look in the bottom right-hand corner of the screen for the address of the instance of the Analysis Services engine inside Power BI Desktop that DAX Studio is connected to:

You can then use this address in the connection dialog of SQL Server Management Studio:

Once you’ve connected, under the Management folder in the Object Explorer pane you’ll find there’s already an xEvent session running you can look at:

You can also create a new session by following the instructions here.

All very interesting, but what’s the point of doing this? I admit that I find Profiler much quicker and easier to use but xEvents have two slight advantages over Profiler:

  1. Frustratingly, all the timestamps in Profiler for Analysis Services events are rounded to the nearest second, which makes answering questions like “which event started or finished first?” much harder to answer. In contrast, all the timestamps for xEvents are very granular:
  • 2. There are some events that are only available in xEvents and not in Profiler traces. The ExecutionStatistics event shown in the screenshot above is a great example: the text column for it a lot of detailed information that I don’t know how to interpret (yet) and which isn’t documented but apparently can be used to determine the amount of time spent in the Formula Engine among other things. This is useful because the widely used method of doing this – add up the amount of time spent in the Storage Engine and subtract from the overall query duration – is unreliable and will become even more unreliable in the future.

I love this kind of detailed information and I’m sure there are a lot of practical problems it can be used to solve. The bad news is that you can’t use this technique against Power BI Premium, at least at the time of writing. Look out for more blog posts on this subject in the future!

7 thoughts on “Monitoring Power BI Desktop Activity Using Extended Events In SQL Server Management Studio

  1. Chris, can you talk to the kind folks who support SSMS and ask them to add a command line parameter to SSMS that would let us provide the server name to connect to Analysis Services? There’s one for the database engine, so SSAS should be a simple add. If so, they (or us) could add an External Tool to Power BI to open SSMS. See https://ideas.powerbi.com/ideas/idea/?ideaid=ad45b4a5-f3db-ea11-bf22-501ac524580f. I’d like to think this is so simple an intern could do it!

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      Haha, I can try but I suspect it will be a long way down their list of priorities

      1. Thanks. Even the Profiler accepts the Analysis Services instance name! How hard could it be to move that over to SSMS? I would love to have a direct link to SSMS from Power BI Desktop’s External Tools. Thanks again!

  2. Microsoft is being very confusing here:

    1. Server Profiler is supposed to be deprecated:
    https://learn.microsoft.com/en-us/sql/tools/sql-server-profiler/sql-server-profiler?view=sql-server-ver16

    2. But the DirectQuery documentation tells you to use… Server Profiler:
    https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-troubleshoot#review-trace-files

    3. And xEvents are not supported in Power BI Premium:
    https://learn.microsoft.com/en-us/analysis-services/instances/monitor-analysis-services-with-sql-server-extended-events?view=asallproducts-allversions

    4. Yet you can use xEvents with Power BI Desktop, as per your entry!

    Make it make sense!

    This comment is not directed at you Chris as you do a fantastic job with your blog, but rather at Microsoft BI being a mess of conflicted and half-deprecated tools (not just the above, but also a bunch of SSDT and SSMS features that don’t work with Power BI Premium).

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      The first link you shared does mention that Analysis Services workloads (which includes Power BI) are supported

Leave a ReplyCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.