Forum Discussion

swatig2904's avatar
swatig2904
New Member
5 days ago

PERFORMANCE ISSUES IN PAGINTED REPORT BUILDER

We have a materialized view in Databricks with about 1.5 million rows and 80+ columns.

On top of that, I’ve created a semantic model in Power BI, and a paginated report that shows essentially all 80+ columns (so it’s returning the full 1.5 million rows).

The report also has around 24 filters/parameters. Because of this combination (large dataset + many columns + many filters), the paginated report is taking a long time to load.

The main dataset is generated using the Query Designer, which produces a large:

`EVALUATE SUMMARIZECOLUMNS(...)`

So far, to improve performance I have:

1. **Created lookup tables for parameter datasets**

Instead of retrieving dropdown values from the large flattened dataset, I created lookup tables and updated parameter datasets to use these tables.

In the Databricks view we have 3 date columns: **D1, D2, D3**.

In the Power BI semantic model I’ve joined these to `dim_date`.

In the paginated report I want to expose 6 date parameters: **StartD1, EndD1, StartD2, EndD2, StartD3, EndD3**.

I have three main questions:

**Q1. How should I handle these 6 date parameters in the main dataset?**

Each date column has its own start/end range; I’d like an approach that is reasonable to maintain.

**Q2. Is the current approach (one big flattened table from the view) the right one?**

We actually started with a star schema in the Power BI semantic model and then moved to a single flattened view, as suggested by a senior data engineer. The issue was that many of the 80+ columns required calculated columns and a lot of transformations, so we pushed all of that logic into the Databricks view instead.

**Q3. How else can I improve performance?**

Right now the paginated report is effectively selecting all rows and almost all columns, with 24 filters on top. I’m looking for design patterns or best practices (e.g. alternative modelling, pre‑aggregation, splitting datasets, etc.) that would make this scenario perform better.

Any guidance or examples would be really appreciated.

Paginated Report
paginatedreport
report builder parameter
Report Builders

2 Replies

  • v-csrikanth's avatar
    v-csrikanth
    Community Support
    17 hours ago

    Hi swatig2904​ 

    We would like to inquire whether have you got the chance to check the solutions provided by other users in community to resolve the issue. We hope the information provided helps to clear the query. Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.

     

     

  • v-csrikanth's avatar
    v-csrikanth
    Community Support
    4 days ago

    Hi swatig2904​
    Three changes are likely to have the biggest impact, in this order:

    1) Push the filters into the query:
    Map each report parameter to a dataset/query parameter, rather than applying the filter after the data is retrieved. With 24 filters this can make a significant difference, because the query returns only the rows you need instead of retrieving all 1.5M first.

    2) Apply the D1, D2 and D3 filters directly to the fact table.
    All three date columns relate to the same dim_date, and only one relationship between two tables can be active at a time. Microsoft's guidance is that when a report needs to filter by different roles at the same time, the model needs a separate date table for each role. Filtering the fact table's own columns avoids needing that.

    EVALUATE SUMMARIZECOLUMNS( 'Fact'[ColA], 'Fact'[ColB], KEEPFILTERS( FILTER( ALL('Fact'[D1]), 'Fact'[D1] >= @StartD1 && 'Fact'[D1] <= @EndD1 ) ), KEEPFILTERS( FILTER( ALL('Fact'[D2]), 'Fact'[D2] >= @StartD2 && 'Fact'[D2] <= @EndD2 ) ), KEEPFILTERS( FILTER( ALL('Fact'[D3]), 'Fact'[D3] >= @StartD3 && 'Fact'[D3] <= @EndD3 ) ) )

    3) Create a report-specific view.
    Keeping the transformation logic in Databricks is reasonable. However, if the report doesn't actually display all 80+ columns, create a narrower view containing only the ones it needs. I would also make the date parameters mandatory, or give them a sensible default range, so the report doesn't run against the full 1.5M rows unnecessarily.

    A couple of things are also worth checking.

    First, identify where the time is being spent, query execution, data retrieval, report processing, or rendering. That will tell you which change will have the biggest effect before you alter any modelling.

    Second, if the semantic model is DirectQuery, report queries against it have a fixed 10-minute timeout. For queries that run longer than that, the documented approach is to use the model's XMLA Read/Write endpoint as the report data source instead.

    Regarding the flattened view, I wouldn't rebuild the model into a star schema solely to address this. The bigger concern is the volume being requested: 1.5M rows × 80+ columns.

    Semantic models are designed to summarise large volumes quickly, but the guidance is explicit that they aren't suited to reports that need to retrieve very large volumes of data — in excess of about 10,000 rows. Relational sources are the documented choice for result sets that size, so at 1.5M rows you're well beyond what this path is built for.

    If the actual requirement is to retrieve all 1.5M rows, I would extract the data directly from Databricks rather than using the semantic model as a bulk-data source.

    References
    https://learn.microsoft.com/power-bi/guidance/report-paginated-data-retrieval
    https://learn.microsoft.com/power-bi/paginated-reports/paginated-reports-data-sources
    https://learn.microsoft.com/power-bi/guidance/relationships-active-inactive

    Thanks,
    C Srikanth
    Community Support Team