Connecting SQL Server Profiler To Power BI Premium

Back in December when I wrote a series of posts on testing the performance of Power BI reports in the browser, I mentioned that it was important to test in the browser because some aspects of the performance of a report may be different there compared to in Power BI Desktop. Following on from this, if you’re testing performance of a report in the browser you are also going to want to take a closer look at the DAX queries generated by your report, even if it is just to check that what you see there is the same as what you see in Performance Analyzer in Power BI Desktop. If your report uses a Live Connection to Analysis Services this is easy to do using either SQL Server Profiler, Azure Analysis Services’s diagnostic logging feature or XEvents. If you’re using a dataset stored in Power BI we have a range of options for monitoring what’s going on including Usage Metrics and the Premium Capacity Metrics Apps and of course there’s also DAX Studio, but for an old-school guy like me, connecting to a Power BI Premium workspace using SQL Server Profiler is a great way to go to get detailed information about what’s going on when queries run. In this blog post I’ll show you how to connect Profiler to Power BI Premium.

First you’ll need to install the latest version of SQL Server Management Studio, which includes SQL Server Profiler – you can get it here. If you have an older version installed you’ll probably need to upgrade. And before anyone leaves a comment about Profiler being deprecated, let me point you to the note on this page:


The feature that allows you to connect Profiler to a Power BI Premium workspace is XMLA Endpoints: it’s in preview right now but basically this allows you to connect any tool that works with Azure Analysis Services up to Power BI Premium. SQL Server Profiler wants to connect to an instance of Analysis Services; XMLA Endpoints mean that you can connect it to a dataset in a Power BI Premium workspace but for this to happen you need to know the url for Profiler to connect to. You can find this by going to your workspace, clicking on Settings:

Workspace Settings

and then going to the Premium tab and copying the Workspace Connection string:

Workspace Connection

You can then open up Profiler, go to the File menu and select New Trace and a connection dialog will appear:


In this dialog:

  • Set the Server type to Analysis Services
  • In Server name paste the Workspace Connection that you copied from Power BI earlier
  • In Authentication select Azure Active Directory – Universal with MFA and enter your username

Next click the Options button and go to the Connection Properties tab and on the Connect to database dropdown select <Browse server…>:


Click Yes on the dialog that appears and then choose the name of the dataset in your workspace that you want to connect to in the Connect to database dropdown. If you don’t do this you’ll get errors later on.

Next you’ll see the Properties dialog:


The default template is Blank, which means no events are selected to monitor; to select events go to the Events Selection tab:


…select the events you want, and click Run to start tracing.


Which events should you choose? That’s a big topic and not one that I have time to go into here, but the Query End event is perhaps the one I look at most – it’s fired every time a query finishes executing and gives you a lot of important information such as the start time, end time and duration of the query in milliseconds. Books such as “The Definitive Guide To DAX” have a lot of information on using Profiler with Analysis Services and a lot of that information is relevant to Power BI Premium too. In future blog posts I dare say I’ll show you some interesting things you can do using Profiler and Power BI too…

30 responses

  1. How did you obtain the SSAS 15.1 trace definition file? I have the latest SSMS (15.0.18206.0) installed and only have the 15.0 TDF.

    • Good question – I should have mentioned this. Take your 15.0.0 trace definition file in the SSMS trace definition folder, copy it and rename the new file 15.1.0, and update the minor version XML property inside the new file from 0 to 1.

      • Thanks, got it working. Sure would be nice to profile an entire capacity… maybe some day 🙂

      • Hi Chris, now PowerBi needs the SSAS 16.0 definition file.Can you explain how to get it ? I don’t find the ssms folder.

      • I have been told there will be a new release of Profiler with the latest trace definition file in it soon. In the meantime – and this is completely unsupported, so do this at your own risk – to make Profiler work I take a copy of the latest trace defintion file, edit the contents and and rename it so it has the latest version number, and that seems to work.

  2. I cant find “Settings” for my workspace, what permissions do i need to get there? Is it in the admin portal?

  3. Pingback: Power BI Insights: SQL Server Profiler; JavaScript sites; Power Query; Preventing timeouts ERP for Hong Kong SME

  4. Pingback: Chris Webb's BI Blog: Visualising Power BI Premium And Azure Analysis Services Query Parallelism Chris Webb's BI Blog

  5. Pingback: Power BI Insights: SQL Server Profiler; JavaScript sites; Power Query; Preventing timeouts | Learn With Ashik

  6. Pingback: Chris Webb's BI Blog: Analysing Dataset Refresh In Power BI Premium Using SQL Server Profiler Chris Webb's BI Blog

  7. Hi Chris,
    im getting this error when trying to connect in power bi premium

    Azure Analysis Server returned the following error: Bad Request

    Technical Details:
    RootActivityId: c99d75bc-777e-42f3-86db-7d5b1f3fccec
    Date (UTC): 2/17/2021 2:49:07 AM
    The response returned from the HTTP server is not valid.
    The response returned from the HTTP server is not valid.

  8. Pingback: Chris Webb's BI Blog: Increasing Refresh Parallelism -And Performance – In Power BI Premium Chris Webb's BI Blog

  9. Hi Chris,

    Do you know if there is a way to use something like AsTrace to log and store queries against a PowerBI premium dataset?


    • If it works against AAS then it should work with Premium via the XMLA endpoint, but I’m not aware on anyone who has tried using ASTrace. However you’re better off using the new Log Analytics integration for logging queries.

  10. Pingback: How to connect to a Power BI Premium Per User using SQL Profiler - Reporting/Analytics Made easy with FourMoo and Power BI

  11. Pingback: Chris Webb's BI Blog: Troubleshooting Power BI Timeouts, Part 1: The Two/Five Hour Import Refresh Limit Chris Webb's BI Blog

  12. Hi, we are having issues with version 18.10 of profiler (latest). Following exact steps above we get the error below when connecting. We can login and enumerate the workspaces fine but when connecting we get:


    Cannot connect to powerbi://


    Internal error: An unexpected error occurred (file pfadalwrapper.cpp, line 986, function PFAdalWrapper::GetInstance).

    Program Location:

    at CProfilerOlapDbConnection.Open()
    at CProfilerOlapServerType.GetConnectionObject(UIConnectionInfo ci)
    at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectorThread()

    Do you have any ideas?

  13. I am unable to find out which visual is causing issues as SQL Server Profiler only logs the first 128 characters of the ApplicationContext field. Long GUIDs for both DatasetId and ReportId makes 128 too short too see the visualId. For example: {“DatasetId”:” … [GUID] … “,”Sources”:[{“ReportId”:”… [GUID] …”,”VisualId”:”bc”. It cuts of after “bc” for the visual.

  14. Hi Chris, Great article thanks, can you tell me which trace I need to run when trying to establish more information about why our a refresh of one table through the XMLA end point is timing out?

  15. Hi! Chris. thank you for the blog post. I followed the exact same steps, but after hitting run, I am getting an error “Either the trace with the ID does not exists, or the user does not have permissions to access the object”. any guidance would be very appreciated.

Leave a Reply to Dennis Cancel reply

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

%d bloggers like this: