One feature I’ve missed from my days working with SAP BusinessObjects is the ability to generate bulk, personalized reports seamlessly using its ‘Publications’ feature. This capability allowed us to process thousands of tailored reports efficiently by combining multiple documents: one for personalization and another containing the list of instances and parameters. The setup was straightforward, taking just a few minutes if the reports were ready.
As some of my customers migrate from SAP BI to Microsoft’s tools, I explored the options available for achieving similar functionality. Two primary methods stood out:
- Using Power Automate
- Report Bursting with Python Notebooks in Fabric
This blog post compares these approaches, outlining their setup, performance, scalability, and cost considerations.
Preparation
For this experiment, I used property purchase records from Iceland (“Kaupskrá”) as my dataset, loading it into OneLake in a Microsoft Fabric workspace.
Since the goal was to generate PDF reports, I created a paginated report using Power BI Report Builder with the OneLake table as the data source. The report included a parameter for passing property numbers to personalize each document.
Next, I registered an application in Microsoft Entra for authentication and granted it access to the Fabric workspace containing the report and Lakehouse.
With the dataset, report, and authentication in place, I was ready to set up report bursting using the two methods.
Method 1: Power Automate Workflow
Power Automate offers a graphical user interface to define workflows. Here’s how I set it up:
- Data Retrieval:
- Used the “Get Rows” action for SQL Server to connect to the SQL endpoint in OneLake and read data from the “Kaupskrá” table.
- Limited the retrieved rows to 100 properties to match the second method for comparison.
- PDF Generation:
- Added a “For Each” loop to iterate through the property list.
- Used the “Export to File for Paginated Reports” action to generate PDFs.
- File Storage:
- Saved the PDFs to OneDrive, specifying filenames dynamically based on property numbers.
Execution: Setting up this workflow was intuitive and required minimal technical expertise. However, processing 100 reports took 58 minutes, highlighting performance limitations for larger datasets. Additionally, the SQL Server connector used is a Premium feature, which incurs additional costs.
Method 2: Fabric Python Notebooks
Using Fabric Python Notebooks involved coding but provided significant flexibility and efficiency. Here’s the process:
- Access Token Generation:
- Generated a token using the registered application for authentication.
- Data Retrieval:
- Queried the “Kaupskrá” table to retrieve the list of properties, limiting it to 100 rows for a fair comparison.
- PDF Generation:
- Used the Power BI REST API ‘ExportTo’ endpoint within a while loop to generate the reports.
- Saved the PDFs to the Lakehouse folder in OneLake.
Execution: While this approach required some coding, I used ChatGPT to generate the base script and made minor tweaks to fit the requirements. Setting up the workflow took slightly longer than with Power Automate but was still manageable. Once executed, the notebook generated 100 reports in just 6 minutes. The files were stored in OneLake, accessible via tools like Microsoft Azure Storage Explorer for download or further processing.
Conclusions
Both methods successfully achieved the goal of generating bulk personalized PDF reports. However, the performance disparity was significant: Power Automate required 58 minutes to process 100 documents, while Python in Fabric accomplished the same task in just 6 minutes. This difference is critical for clients needing to generate tens of thousands of reports, where every second counts.
Although Power Automate’s no-code interface is appealing, tools like ChatGPT bridge the gap by simplifying Python development for Fabric. This approach allows even those with limited coding experience to leverage Fabric’s superior scalability and efficiency, making it a clear winner for high-volume scenarios.
It is also essential to consider the cost implications: Power Automate requires premium connectors, while Fabric Python Notebooks necessitate a Power BI workspace with Fabric capacity. For organizations migrating from SAP BI and seeking a robust report bursting solution, Fabric Python Notebooks provide an efficient and future-proof strategy.
What are your thoughts? Have you tried either of these approaches? Let me know!