Paginated Reporting at Scale: Choosing the Right Microsoft Platform

But before jumping into the technology, it is worth asking a more basic question. In a world filled with interactive dashboards and near real-time access to data, traditional page-by-page reports can sometimes feel like a format from another era. Do we still need paginated reports?

I remember working for an investment bank, where month-end operations, including statement generation and printing, were an important part of regular banking business. More importantly, regulations and SLAs governed the entire process with our customers. So, even today, there is still value in flipping through page-by-page datasets, especially when information needs to be formally generated, distributed, and retained.

What Are the Options?

Options 4 and 5 started to become less attractive when compared against the three main requirements. Power Platform Templates can provide a relatively simple development experience and fit well within the Microsoft ecosystem, but large-scale document generation and printing may become a limitation. Programmatic Generation, on the other hand, can certainly provide the required scale and flexibility, but that flexibility comes at the cost of additional development and ongoing maintenance. 

Talking about Option 3, Microsoft stopped releasing new standalone versions of SSRS starting with SQL Server 2025, consolidating its on-premises reporting services under PBIRS. This effectively brings Options 2 and 3 together. With that, our search becomes much simpler: moving forward, we are really looking at two main Microsoft reporting platforms: Microsoft Fabric and Power BI Report Server.

Fabric Journey 

Choosing Microsoft Fabric initially felt like a no-brainer. It is a complete, unified analytics platform that brings data integration, engineering, warehousing, analytics, and Power BI together under one roof. But, as the saying goes, with great power comes great responsibility. In this case, that responsibility is financial: as reporting workloads grow, so does the Fabric capacity required to support them, making cost and capacity planning an important part of the equation.

Bringing Sourcing Data In

With Fabric selected as the initial direction, the proposed workflow was relatively straightforward. Data from on-premises data sources would flow through the On-premises Data Gateway into Microsoft Fabric, where it would be made available in OneLake. From there, Power BI Report Builder would be used to develop the paginated reports, with the final output generated as PDF or printed documents at scale. 

Data Mirroring in Fabric was a relatively new concept when we started exploring it and had recently become generally available. It supports different source data platforms and, at a high level, provides a fairly simple way to continuously replicate data changes into Fabric. Depending on the size, complexity, and required latency of the datasets, mirroring can be either a blessing or a curse for your data platform. But importantly, it worked, and we were able to establish a near real-time flow of data changes from our on-premises source into Fabric.

At the same time, Fabric Mirroring was, and still is, a fairly new capability, and our journey was not without challenges. I had to spend quite a few calls with the Microsoft support team troubleshooting issues where data was either missing or duplicated during replication. Some of these issues required updates to the On-premises Data Gateway, while in other cases tables had to be reconfigured or re-enabled for mirroring to give the replication a fresh start as Microsoft continued improving the product.

Reading directly from mirrored datasets became a performance bottleneck for large-scale reporting workloads. The initial idea was to take an existing paginated report and lift and shift its business logic, embedded in complex SQL queries. With the replicated source tables available in OneLake, the queries were recreated and successfully validated. However, they were very slow. No OneLake in the world can compete with well-established, indexed data assets in a well-managed relational database.

So, the hope of relying on OneLake replicated data slowly and painfully faded away. We turned to the idea of direct access to the source database, which was also supported by Fabric paginated reports.

Challenge #2: Testing a Direct Database Approach

For the test, I developed a fairly complex paginated report containing more than 10 different datasets and deployed it to Fabric. We also built a notebook-based solution to simulate a large-scale printing operation, rendering reports as PDF documents and saving them to OneLake. Different testing parameters allowed us to control the pace of execution, the number of reports submitted within a given time frame, and retry steps for failed executions. Everything was ready.

But for some strange reason, we could only successfully generate about 40 reports per minute, while our planned target was several hundred reports per minute. Increasing the number of reports per minute would result in failures. We could still pass those failed executions into another round of report submissions, and they would work, but the overall problem didn’t go away.

The error message from Fabric was very generic: "There was an error contacting an underlying data source. Manage your credentials or gateway settings on the management page. Please verify that the data source is available, your credentials are correct and that your gateway settings are valid."

Challenge #3: Scaling Fabric Capacity

First things first, let’s throw more wood on the fire and see if we can increase the heat. Let’s scale up the Fabric Capacity running our workload and see if things improve. Microsoft Fabric is essentially compute running on Microsoft’s infrastructure in Azure data centers, which we consume as a managed cloud service. Fabric Capacity determines how much of that compute our workloads can consume, expressed in Capacity Units (CUs), while Microsoft manages the underlying servers, virtualization, networking, operating systems, scaling, and Fabric services.

Well, unfortunately, scaling up the Fabric Capacity didn’t change the overall report execution experience. Report executions would still eventually start to fail somewhere beyond the 40 reports-per-minute mark. We scaled the capacity all the way from F8 to F128, but it made no noticeable difference.

Challenge #4: Scaling the Data Gateway

The error message was still the same, and its very generic nature didn’t provide us with much information to understand the root cause of the problem. Knowing that the source data was flowing through several points, from the On-premises Data Gateway hosted on an Azure VM, through Azure Service Bus Relay, and then to the Fabric cloud data connector, we shifted our efforts toward the On-premises Data Gateway.

So, what did we do? We scaled up the Azure VM hosting the gateway and configured the Data Gateway in Fabric with multiple nodes.

And it wasn’t helping us. Yes, along the way we learned a lot of low-level details about how the Data Gateway operates by reviewing thousands and thousands of lines of very detailed logs it produced. 

At this point, we also started engaging Microsoft technical support, raising all our concerns with one main goal: to understand why this was happening.

Challenge #5: The VNet Data Gateway Experiment

PBIRS: A Fresh Start

After exhausting every optimization path in Fabric, we had nothing left to lose by testing a dedicated reporting platform. So, I created a reporting environment using a pure on-premises solution with Power BI Report Server (PBIRS). There are several similarities and differences between PBIRS and Fabric when it comes to supporting paginated reports.

One of the biggest similarities is the .RDL report format. You can take a paginated report originally developed in Power BI Report Builder (please don’t confuse it with Power BI Desktop) for deployment to Fabric and open it in Microsoft Report Builder for PBIRS. The visual layout remains intact, and everything is there. However, the report datasets need to be reconfigured from Power Query-driven artifacts to direct data source connections. However, no complaints here, good compatibility.

The biggest, and perhaps smartest, difference between executing and rendering paginated reports in Fabric versus PBIRS is the execution model. Fabric uses an asynchronous model where, after submitting a report request through the REST API, you have to continuously poll its status to determine when the execution is complete, and then make another call to retrieve the resulting PDF.

With PBIRS, the execution can work synchronously, where the request is completed with the resulting file returned directly, without the need to continuously poll for the result. This gave us a significant performance boost and allowed us to achieve and even exceed our expected benchmark of generating several hundred reports per minute.

As for the guy in this photo, I’m not angry about anything, it’s just a sign of dedication and love for singing. Happy Data Adventures! ??