Making Paginated Reports Fast - Data Retrieval Guidance for Power BI

Paginated reports are the unglamorous workhorse of Power BI. They're the ones that look like a printed document: the invoice, the multi-page operational listing, the pixel-perfect statement that has to line up exactly when it lands on a page or in a PDF. Nobody puts them on a slide at a strategy offsite, but half the businesses we work with quietly depend on them to run day to day. And when they're slow, they're properly slow. A report that takes four minutes to render, or times out entirely, brings a real process to a halt.

Most of the time, the problem isn't the report. It's the data retrieval underneath it. Microsoft has specific guidance on this, and it lines up almost exactly with what we see in the field, so I want to walk through it the way I'd explain it to a client staring at a spinning progress bar.

Why paginated reports get slow in the first place

A paginated report runs one or more datasets, and each dataset is a query against a data source. When you open or export the report, those queries run, the data comes back, and then the report engine lays it all out across however many pages it needs. There are two places time gets spent: retrieving the data, and rendering it. The guidance here is mostly about the first, because that's where the biggest wins usually hide.

The trap people fall into is treating the report as the thing to optimise when the query is the actual problem. You can tidy the layout all day and shave nothing off a report whose dataset is dragging two million rows across the network so it can show a summary of forty. The report is slow because it's asking for far more than it needs, or asking for it in an expensive way. Fix the ask and the whole thing speeds up.

Retrieve only what you need

This is the single biggest lever, and it's astonishing how often it's ignored.

Filter at the source, not in the report. The classic mistake is a dataset that runs SELECT * from a big table, pulls the lot back, and then uses report filters to show one region's worth. Every one of those millions of rows crossed the wire and got parsed just to be thrown away. Push the filter into the query so the database does the work and returns only the rows that matter. Databases are extremely good at filtering. Report engines are not. Let each do its job.

Only select the columns you use. If the report shows six fields, don't retrieve forty. Wide rows cost memory and network time, and unused columns are pure waste. It sounds obvious, and it's still the most common thing we find.

Do the aggregation in the query. If the report shows totals by store, don't pull every individual transaction and let the report sum them. Write a query that groups and sums at the source and returns the summary. Returning a few hundred aggregated rows instead of a few million raw ones can turn a timeout into an instant render. This one change alone has rescued more reports than anything else I can think of.

The principle underneath all three is the same. Every row and column you retrieve has a cost, and the cheapest data is the data you never asked for. Design the query around the report's actual need, not around "grab everything and sort it out later."

Use query parameters properly

Paginated reports support parameters, and the way you wire them up matters a lot for performance.

The right pattern is to pass the parameter into the query so the filtering happens at the data source. When a user picks a date range or a branch, that selection should become part of the WHERE clause the database runs. The wrong pattern, which is easy to fall into, is to retrieve a big unfiltered dataset and apply the parameter as a filter inside the report. Functionally they look identical to the user. Underneath, one asks the database for a small, precise result and the other hauls everything back and filters in memory. Same screen, wildly different speed.

Parameters also let you avoid running work nobody needs. If a report can show one branch at a time, there's no reason to ever retrieve all branches. Make the parameter mandatory, feed it into the query, and the report only ever touches the slice in front of the user.

Match the retrieval to the data source

Where the data lives changes how you should go about this, and it's worth being deliberate.

If you're querying a relational database like Azure SQL or SQL Server, you're writing SQL, and normal database performance rules apply. Indexes on the columns you filter and join on make an enormous difference. A query that filters on an unindexed date column will scan the whole table every time; the same query with the right index is near instant. If a paginated report is slow and it's hitting a database, the first question is almost always whether the supporting query is indexed for what it's doing. Getting the SQL and the indexing right is standard work for our Power BI consultants, and it's frequently the fastest path to a report that stops timing out.

If you're querying a Power BI semantic model, the dataset uses DAX or a query against the model, and the performance story is about how the model is built: the relationships, the measures, whether it's import or DirectQuery. A poorly modelled dataset makes every report on top of it slow, and no amount of report tuning fixes a model problem.

The point is that "make the report faster" almost always resolves to "make the query faster," and how you do that depends entirely on what's on the other end.

Watch out for the expensive patterns

A few specific things reliably cause pain, and they're worth checking for by name.

Sub-queries run per row. Some datasets are written so that a lookup runs once for every row in the main result. On a big report that's thousands of round trips where one well-written join would do. If a report's slowness scales with row count in a way that feels wrong, this is a prime suspect.

Sorting and grouping the report can't avoid. If the report groups and sorts data, doing that in the query, where the database can use indexes, is far cheaper than making the report engine sort a huge unsorted result in memory.

Retrieving detail to show a summary. I've said it already but it's the number one offender, so it's worth repeating. If the output is a summary, the query should return a summary. Pulling detail "just in case" is how a two-second report becomes a two-minute one.

One giant report doing everything. Sometimes the real fix is splitting a monster report into a fast summary and an optional detail drill-through, so the heavy query only runs when someone actually asks for the detail.

The honest take

Paginated reports have a reputation for being slow and clunky, and some of that is deserved: the technology is old and the design experience in Report Builder is not what anyone would call pleasant. But most of the reports we're brought in to rescue aren't slow because of the platform. They're slow because the datasets were written to grab everything and sort it out in the report, which is the path of least resistance when you're building and the path of maximum pain when a hundred people are running the thing every morning.

The good news is that data retrieval is very fixable. You rarely need to rebuild the report. You need to rewrite the queries feeding it so they filter, aggregate and select tightly, push the parameters down to the source, and make sure the database is indexed for the work you're asking of it. That's usually a contained piece of work with an immediate, obvious payoff, which makes it one of the more satisfying jobs we do.

If your team is exporting a paginated report and going to make a coffee while it churns, that's a signal, not a fact of life. Faster reporting is often a data plumbing problem, and it's the sort of thing our business intelligence work tackles head-on. Get in touch and we'll take a look at what your reports are actually asking for, and how much of it they don't need.

For the official version, Microsoft's data retrieval guidance for paginated reports sets out the recommendations in detail.