Paginated Reports impact Fabric capacity

[D4zk]

Hi all,

I'm facing an issue related to a paginated report created from the Semantic model of the Fabric warehouse. This report displays all records from one fact table (10 million records) and 6-7 dimension tables (ranging from 10,000 to 200,000 rows each). Without any filters, the report shows around 1 million rows.

Due to DirectLake limitations, I encountered the issue: “The result set of a query to the external data source has exceeded the maximum allowed size of '1,000,000' rows.” To address this, I revised my approach.

I created a stored procedure in the Gold layer of the Fabric warehouse to pre-calculate the dataset for the report. Afterward, I was able to generate and view the report both locally and in the service. However, it still takes at least 4-5 minutes to display, even though the execution time of the stored procedure is only 25 seconds. Additionally, while testing the report, the CU% of Fabric spiked to 500%, causing some other pipelines to fail.

and It takes too much resource + time for rendering report.

How could I resolve this issue? Currently, I cannot use the paginated report with this requirement, as it impacts other teams by causing the Fabric capacity to reach its limits.

[D4zk]

Thank you for all your responses. Actually, when I checked resource usage, I found that RENDER tasks consume the most CUs, not data queries. Since the primary requirement of SAP BO in my company is data export and not go through SQL management, so I need to maintain a similar approach in Power BI paginated reports that have "subcription to report" to export and send data to email. I will try to increase Fabric capacity.

[Poojara_D12]

Hi @D4zk 

Given the issue with the paginated report pulling large datasets from a Fabric warehouse, resulting in performance bottlenecks and Fabric capacity spikes, here are some strategies that could help optimize performance:

1. Implement Row Limiting and Filter Parameters

• Since the report does not use filters and retrieves around a million rows by default, try adding default filter parameters to restrict the data at runtime.
• Allow users to input specific date ranges, categories, or other filters that significantly reduce the data volume being fetched. This way, users can narrow down the scope of the data they’re viewing.

2. Optimize the Stored Procedure Further

• Although the stored procedure is running within 25 seconds, check if it can be optimized further by:
  • Indexing frequently accessed columns.
  • Avoiding complex joins if they aren’t necessary.
  • Pre-aggregating data where possible to reduce row count.
  • Reducing data granularity (e.g., summarizing data by month instead of day) if detailed granularity is not necessary.

3. Use Data Aggregations and Summary Tables

• Create a pre-aggregated summary table that is refreshed periodically (e.g., every hour or day) based on common reporting needs. Instead of querying the fact and dimension tables directly, the report can pull data from this summary table.
• This approach can significantly reduce the query load and enhance performance, especially for reports with higher-level summary information.

4. Partitioned Views or Materialized Views

• Use partitioned views or materialized views in the Gold layer to divide the data logically by date or other relevant criteria. This will help limit the data scanned for each query and reduce processing time and memory usage.

5. Adjust Fabric Resource Settings

• If possible, increase the capacity or memory allocation for the Fabric environment temporarily during peak reporting hours. You can also schedule intensive report refreshes during off-peak hours to avoid capacity spikes and conflicts with other workloads.

6. Consider Dataflow for Pre-Processed Data

• You can use Dataflows to preprocess the data and apply any needed transformations. This way, you can offload some data processing from the paginated report itself and reduce on-the-fly data processing.

7. Switch to Incremental Refresh

• If your report can work with incremental data refreshes, set it up so that only new or modified records are fetched. This reduces the amount of data queried at any time and keeps resource usage lower.

8. Leverage Power BI Dataset Caching (in Power BI Service)

• If possible, configure your Power BI dataset to cache data from the warehouse periodically. Caching will help improve report loading times by reducing dependency on real-time data pulls.

9. Consider Changing the Report Design

• Since paginated reports are typically designed for detailed data dumps, they may not be ideal for large datasets. If possible, consider breaking the report into multiple sections or reports that each address a different aspect of the data.

10. Monitor and Analyze CU% Usage

• Use the Capacity metrics (CU%) from your screenshots to analyze when and where the resource spikes are highest. This can help you pinpoint the most resource-intensive operations and decide on further optimizations.

Implementing a combination of these strategies should help you mitigate the capacity spikes and performance issues in your paginated report. Let me know if any of these options seem viable or if you'd like additional details on a specific approach.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant

[Anonymous · Accepted solution]

Hi, @D4zk 

Thanks for the reply from lbendlin , please allow me to provide addition.

You can try to optimize the performance of your stored procedures. This includes indexing, avoiding unnecessary calculations, and using an efficient query structure. Or take advantage of the report cache to store the report results for a period of time. This reduces the need to rerun reports frequently, which reduces the load on the system.
You need to plan your capacity, considering the complexity of the report design, the amount of data retrieved by the report, and how the report retrieves the data. You can check the following links:

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

Paginated reports in Power BI: FAQ - Power BI | Microsoft Learn

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

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

[lbendlin]

Paginated reports are pre-rendered. That means when you refresh that report it will generate tens of thousands of pages.  Nobody will ever read these.

Switching to Direct Query mode may help but in some corner cases you may still hit the 1 M row limit (theoretically you can modify that limit in the capacity settings, but that is not advisable).