Optimizing Power BI Report Builder performance

[manhil29]

Hi everyone,

I am a newbee to Power BI Report Builder tool. I am working on building a paginted report using Power BI Report Builder. My plan is to embed the final report into Power BI Dashboard in the end. I am using Power BI Semantic Model as my data source. I have 3 subreports inside the main report. Subreports include query level parameter to fetch data. My main report source has 69,000+ rows and then each subreport would have rows (not exceeding 50 rows per subreport). The main report and subreports are published into "My Workspace" on Power BI Report Server and keeps running forwever. Last time I checked, it ran for 15+ hours but the refresh wasn't complete.

Power BI Semantic Model from the original dashbaord actually uses custom queries to extract data from Oracle database and the referesh happens in few minutes (less than 2 minutes). However, the paginated report never completes the refresh. All the subreports are using "Project ID" as a paramter to fetch/extrct the data. Your help to optimize the Report would be appreciated.

Best,

Maouee

[Anonymous]

Hi, @manhil29 

If you want to optimize the performance of paginated reports in the Power BI service, you can first refer to the overall process of the report performance optimization guide below and optimize each part according to the following process:

Troubleshoot report performance in Power BI - Power BI | Microsoft Learn

Secondly, you can refer to the following paged report practices:
The first document will tell you how to design a paged report

Design tips for reports in Power BI Report Builder - Power BI | Microsoft Learn

The second one will explain how to get the best performance for our paginated reports from the data retrieval aspect.

Data retrieval guidance for paginated reports - Power BI | Microsoft Learn

Generally speaking, we need to reduce the number of unnecessary columns introduced into the visual objects of paginated reports. Your main data set has more than 69,000 rows. You can refer to the following optimization suggestions for large paginated reports:

Process large Power BI paginated reports - Power BI | Microsoft Learn

In the following document, we recommend that you use capacity to improve the performance of paginated reports. Make sure that each of the previous steps is optimized to the extreme, and then consider whether to use premium capacity. Since my workspace is in shared capacity without a license, it may cause performance issues.

Paginated reports capacity planning for Power BI Premium - Power BI | Microsoft Learn

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[manhil29]

Thanks Jianpeng Li for sharing the resources and links to optimize. I now understand that I am using dataset paramter to fetch data for subreports based on Project ID. I will try to change the approach and use dataset filter or Tablix filter to fetch the data for subreports only once and display the data in subreports based on Project ID. While I work on changing the approach, my understanding is the subreports dataset are queried for each row in the main report with 69,000+ rows. Please confirm and also if my assumption is correct, is there a way to stop refreshing the subreport datasets for every row in main report. This would definitely improve the process as well as performance. 

[Anonymous]

Hi, @manhil29 

The paging report refreshes at the page level, and I didn't find the option or documentation related to limiting row refreshes. You need to optimize your query by following the tips above. Then enable more load for your paged reports in power BI service.

Power BI Paginated Reports June 2019 Feature Update Summary | Microsoft Power BI Blog | Microsoft Po...

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[manhil29]

Thanks Jianpeng Li! I was busy making changes into my report builder files using the links you shared. Here is what I observed based 2 approaches I took.

Just to repeat, I am using Power BI Semantic Model as data source for all of my reports, and my main report has 70K+ records (now) and it has 3 subreports (one with 79K+ records now, and the other two have 285K+ records in total).

Approach 1: I used Query Paramter to get the records from Dataset for subreports

Observation: The process to fetch the data is slower. The approximate number of records for subreports for each row in main report varies between 0 and 50. I allowed the report to run for almost 8 hours and the rows fetched were nothing more than 80K+ and the report was never displayed.

Approach 2: I fetched all the records for subreport datasets in single attemp and applied table level filter to display the records for subreports

Observation: The process went on for 8+ hours but never completed. It fetched almost 125 million+ rows in total but the report was never displayed.

I presume in both of the appraoches, the report server fetches the subreport datasets for each row in the main report. Is there something I am missing or doing wrong?

Best,

Manhil29

[manhil29]

Thanks Jianpeng Li! I would work on this and share my response/ffedback with you. It seems my paginated report feches the data for the individual subreports based on Project ID for every row in main report. That also means that the the subreport datasets are queried 69,000+ times for each subreport as I am using dataset paramter instead of dataset filter. I will work on changing on the approach and see if it can help. In the meantime, would it be possible to share some resources on "if we can stop refreshing sub-report datasources for every row in main report".