In 2019-May release of Power BI on-premises gateway, it has introduced a new feature called Gateway Performance Monitoring. Power BI team has given this feature so that now you can monitor query activities, errors and resource usage of a gateway sever in a power report very easily. Here is the link to the announcement of that feature.
https://powerbi.microsoft.com/en-us/blog/on-premises-data-gateway-may-2019-update-is-now-available/
Please note that this feature is still in preview (as at now) and by default it is disabled. If you are to enable it, you need to do some changes in a configuration file. Firstly, you need to find the “Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config” file inside your installation folder. Unless you have specified else, default location is “C:\Program Files\On-premises data gateway”. Check Image:01
Image:01: Configuration file and file location
Open this config file and you will notice that by default following settings are set to False. Set it as True and check for the “ReportFilePath’ setting to make sure where will the report data be saved once we enable this option. Check Image:02/Image:03
Image:02: Settings to set as True
Image:03: Report Path
After you have done the changes, save the configuration file and open the gateway application. In-order to changes to affect you need to restart to gateway and you can do that by clicking restart now button in “Service Settings”. While doing so identify the user which has used to run Gateway Service inside your server. In most cases it will be “NT SERVICE\PBIEgwService” unless you have specified someone else. Check image:04
Image:04: Restarting the gateway
Once the gateway is restarted you can go and check the folder you set as report folder previously and you will notice that there are set of files inside it. One thing to you must know that these files are inside service users “AppData” folder; Not your “AppData” folder. Check Image:05 for file path and files.
Image:05: Log file path and log files
If you open these files you can notice that these are additional log files created by Gateway application. To analyse these log data, download “Gateway Performance PBI template file” from following link.
Template Download Link:
Double click on the template file and it will open Power BI desktop application and ask for your report data folder path. By default, it will automatically pick correct path and if not, you can change path and click load to view the report. Check Image: 06
Image:06: Log file pathIt will open a report which contains 5 report pages. I have listed what kind of information available in each page in below table.
|
Page Name |
Available Information |
|
Query Execution By Data Source |
Query Execution information by data source. Including Query Execution time, Data Processing time and more. |
|
Query Execution By Query Type |
Query Execution information by query type. Query type can either be refresh query or direct query. |
|
System Counters Over Time |
System counter such as CPU and Memory usage of gateway. |
|
Average Execution And Errors |
Average execution time, number of successful/failed queries, No of failures by query type. |
|
Data Error |
All the Data errors occurred when running queries. |
Table:01: Report pages and information
You can modify this report the way you want since you have access to the data model of this report.
Below are few screenshots of my report when I check the report.
I believe this report contains lots of information which can be used to troubleshoot reports and the gateway. Specially when you face any gateway related issues or you want to monitor which queries take more time to execute, you can start troubleshooting from here.
hopefully this post helps you and thanks for reading. Cheers!!
Asanka @ ShareWhatIKnow 