Introduction
This blog post is going to discuss inspection of Power BI performance when you use Power BI Gateway to push data to PowerBI.com service.
I am going to assume here that you are not new to PowerBI. I am also going to assume that you skipped PowerBI Report Server (which requires a hefty Software Assurance $$$) and settled with a more cost-effective way to run your pbix files using PowerBI.com service ($10 for Power BI Pro or $5K+ for PowerBI Premium). My last assumption would be that you are using Power BI Gateway to push your data to PowerBI.com service.
Problem
Customer: Please allow me to see Execution Logs for Power BI.
Me: Looks like you are using a Power BI Gateway. Let me find it.
Let’s start with how we normally visualize performance: We would look for data collected in a run-time. On-premise solutions like IIS would have log files and reporting platform like SSRS would have some database records that we can review. While I am very familiar with ingesting and analyzing IIS log files and building reports on SSRS by exposing the data collected in ExecutionLog3 view under a ReportServer database, I was very skeptical that Microsoft will expose anything for PowerBI.com. After all, PowerBI.com is Microsoft internal infrastructure.
I guess I was proven wrong. Of course Microsoft is not showing us how bad PowerBI was hammered to process the report or how much time it took to render a report, but Microsoft still collects enough data to weed out the worst performing queries.
Solution
Ironically, not only this is possible, but Microsoft went an extra mile and created Performance BI template for Power BI performance. This is quite awesome.
So what do we need to do to inspect PowerBI performance? We would need to:
- Enable performance logging by setting both QueryExecutionReportOn and SystemCounterReportOn to True inside GatewayCore.dll.config xml file (you are going to need write/read permission to make changes in C:\Program Files\On-premises data gateway\ folder)
2. Set ReportFilePath variable to a shared folder that is more accessible and better managed than a local C drive (Power BI Gateway default)
3. Restart Power BI Gateway service (or upgrade if possible)
4. Download the Gateway Performance PBI template
5. Import previously downloaded template (Select File > Import > Power BI template from within Power BI Desktop)
6. Change folder path to point to log files (if applicable)
7. Enjoy it
8. Enable additional logging inside On-premises data gateway (if more logging is required)
Disclaimer
This blog post is partially based on the following Microsoft article – https://docs.microsoft.com/en-us/data-integration/gateway/service-gateway-performance.
