Forum Discussion

manhil29's avatar
manhil29
New Member
1 year ago

Optimizing Power BI Report Builder performance

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

Need Help

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      manhil29
      New Member
      1 year ago

      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".

    • manhil29's avatar
      manhil29
      New Member
      1 year ago

      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.