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:
and then going to the Premium tab and copying the Workspace Connection string:
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…
Discover more from Chris Webb's BI Blog
Subscribe to get the latest posts to your email.
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.
I cant find “Settings” for my workspace, what permissions do i need to get there? Is it in the admin portal?
Can this work with Power BI Embedded?
Yes
Thanks. That was fast!
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.
(pfutil)
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?
Thanks!
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.
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://api.powerbi.com/v1.0/myorg/.
===================================
Internal error: An unexpected error occurred (file pfadalwrapper.cpp, line 986, function PFAdalWrapper::GetInstance).
(pfutil)
——————————
Program Location:
at CProfilerOlapDbConnection.Open()
at CProfilerOlapServerType.GetConnectionObject(UIConnectionInfo ci)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectorThread()
Do you have any ideas?
18.10 is not the latest – 18.11 was released recently. Can you test that?
Many thanks for your quick reply. Sorry, 18.11 didn’t show up when checking for new version in SSMS. Now installed it and got exactly the same error with 18.11.
Seems to be a fairly common issue that’s been going on for quite some time:
https://community.powerbi.com/t5/Issues/Analize-in-Excel-Internal-Error/idi-p/2295416
I have version 3.3.0 of adal.dll mentioned in there.
Looking at your original message, the error doesn’t seem to contain the complete XMLA endpoint connection. Have you entered the full XMLA endpoint server name?
Ah, that got filtered out by WordPress, I replaced our workspace name with when posting and that got removed. I have tried with
powerbi://api.powerbi.com/v1.0/myorg/Long%20-%20Workspace%20name
powerbi://api.powerbi.com/v1.0/myorg/Long – Workspace name
powerbi://api.powerbi.com/v1.0/myorg/Test1 <= Created for test
Also, I've tried with dataset names without spaces. I read something in the release notes about spaces but it didn't make any difference.
You should just be copying and pasting the server name from the browser into Profiler though – are you doing that?
I’ve created a feedback item for this here: https://feedback.azure.com/d365community/idea/137bbe19-d79e-ec11-a81c-000d3adfb8f5
It’s really a blocker as this (to my knowledge) is the only way we can do detailed, ad-hoc tracing without spinning up Log Analytics.
Thanks for your help!
Yes I copy straight from workspace settings
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.
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?
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.
@Vickar,
Would you try select the database name when connecting to server using SQL profiler?
See the “Connect to database dropdown” in this article
Ref:
https://learn.microsoft.com/en-us/analysis-services/instances/use-sql-server-profiler-to-monitor-analysis-services?view=asallproducts-allversions#using-sql-server-profiler
When connecting to a Power BI Premium workspace, a valid database must be specified in the Connection Properties tab of the Connect to Server dialog, otherwise a user does not have permissions to access the object error message is returned.