SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


Analyze Power BI Query Performance using SQL Server Profiler

Especially for self-service BI or Power BI is one of the most common reporting tools used by data professionals. This Power BI tutorial shows how report developers can use Power BI tools for performance analysis of their reports especially for SQL query performance on data source.

Launch Power BI Desktop software on your computer

launch Power BI Desktop reporting tool

After Power BI started, we will connect and trace Power BI tasks using SQL Server SQL Profiler tool. To do that we need the port number the Power BI is using for connections. Since this port changes we have to first launch the Power BI tool then figure out its ports in use.

We can detect the port numbers that the Power BI self-service reporting tool is using by means of Windows Powershell. So now we are ready to launch Windows Powershell as administrator.

Windows PowerShell

When Windows Powershell started, execute the following command on Powershell command prompt

netstat -b -n
Code

Then among output data on screen, search for "msmdsrv.exe"

detect Power BI port using Windows Powershell

Take note of the port number "49900" in this case.
Please pay attention to the fact that each time a new Power BI Desktop application is launched, this port changes

To trace the SQL tasks being processed on Power BI we can now launch SQL Server Profiler
You can open SQL Server Profiler either using its application icon or within SQL Server Management Studio.

SQL Server Profiler

Now start a new trace via the icon on the menu bar, or follow menu options "File > New Trace"

As seen as follows select "Analysis Services" in Server Type combobox.
In Server Name please enter localhost and the port number we have fetched using PowerShell in previous steps in the format of "localhost:49900"
And connect to the server via SQL Profiler tool.

connect Power BI using SQL Server Profiler tool

And simply press "Run" on "Trace Properties" screen to start tracing Power BI using SQL Server SQL Profiler

Now let's switch to Power BI Desktop screen.
I will show an other alternative tool to trace performance metrics which is new for Power BI report developers shipped with Power BI tool.
Developers can also activate Performance Analyzer feature which is introduced recently for Power BI developers to analyze performance issues with their reports.
Just mark the checkbox next to "Performance Analyzer" within View menu.

Power BI Performance Analyzer tool

Click on Start Recording on Performance Analyzer tool as seen in below screenshot.
You can switch to a new empty page.
You can now see that every action you take on Power BI will be listed in Performance Analyzer so you can compare for example two different reports or data visualizations easily.

Start Recording for Performance Analyzer tool in Power BI

On Home menu click Get Data
This menu will help us to define a new data source for your SQL report or use one of the previously created ones.
Choose the option according to where you will read your data for your Power BI report

Power BI report from SQL Server as data source

Connect to the report data source
As a second step, you will choose the database table or view that you want to display on your Power BI report.
Select the table/view and press Load button

choose and load the data source table or view for your Power BI report

After your data source selection for your Power BI report, you will see that the active trace in SQL Profiler will start showing actions taking place to access data from source and internal tasks processed within Power BI Desktop

Pay attention to "17 - ReadData" events.
This maps to data reading for Power BI report task where the SQL query to fetch report data is executed.

load Power BI report data

When data load is completed, in SQL Profiler trace logs, search for "Finished reading data for" or the last line for the ReadData event.

SQL Server Profiler trace logs for Power BI report data read operation

Go to the Duration column. You will see the duration required to read data from its source for the Power BI report.
Above and below screenshots are from two different data sources showing one data source is performing much better than the other one.

analyze Power BI report performance using SQL Server Profiler

I hope this tutorial is useful for Power BI developers understand how SQL Server Profiler and Performance Analyzer tools can be used for measuring and analyzing their report's performance to fetch the data from its source.



SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.