Power BI Data Driven Subscriptions with Power Automate – Follow up using Power BI report

In the last post I went over using Power Automate to perform a data driven report subscription using a Paginated report referencing a Power BI dataset. The flow referenced an Excel file with the information to make the process data driven and generate 2000 PDF files that could then be emailed to users. In the flow the PDF files were simply placed in a OneDrive folder for testing purposes to validate the flow would run as expected and to review the metrics after the fact to evaluate the impact of running the process.

That post was the first follow up to this post where an AAS database was used to run the report bursting process. This post is the second follow up I wanted to do by replacing the Paginated report with a Power BI report to compare performance. Granted not all Paginated reports could be swapped out with a Power BI report, but in this particular case it will work fine since I am not return 100s of records and don’t require multiple pages, plus I am just producing PDF files.

The first thing I needed to do was to recreate the Paginated report to a Power BI report and I did that with Power BI Desktop referencing the Power BI dataset that was made in the previous post follow up. I won’t go into this in detail, it was simply a matter of getting a similar look to the report output and testing it out to make sure it should work for the report bursting process.

Power BI report for data driven subscription screenshot

Once this was tested and ready I cleared the filter and deployed the report to the Power BI service. The next step was to add a new column into the Excel file for the subscription process that could be used to pass the filter to the Power BI report. Steven Wise has a nice blog post that outlines this setup with using URL filters with the report subscription here Filter & Email Power BI Report pages using Power Automate & Excel. I go ahead and add a new column “URLFilter” in the Excel file and add an expression to generate the filter that will be used in the Power Automate process to filter the report based on the Company Name value which is an attribute in the Customer table.

Excel file with the URLFilter column screenshot

Steven has a reference in his blog post to how the filtering works and is used in the export process. Now that we have the report available and the Excel file updated, we just need to modify the Power Automate flow to use the “Export To File for Power BI Reports” steps instead of the Paginated Reports step.

Power Automate Export To File for Power BI Reports step screenshot

In the above screenshot you can see that I am referencing the “Export To File for Power BI Reports” step now, using the new Power BI report that was deployed to the service, and have made a reference to the URLFilter column in the “ReportLevelFilters Filter” section of the step.

Now it is time to test the updated flow and process to validate everything works and then compare the results. After the flow has run and we take a look at the PDF files in the OneDrive folder we can notice one considerable difference…

PDF files in OneDrive folder screenshot

And the difference is in the size of the file that was produced. In the first post I included a screenshot and the size of the files was around 260 KB and now they are around 30 KB which is an 88% reduction in file size. Granted the report / file being generated here is really basic, but that is a pretty significant difference and the output if we look is basically the same (I could have spent a bit more time on getting the formatting identical).

PDF file output from the Power Automate flow screenshot

And the process took around 90 minutes again to complete, so right in line with the Paginated report examples. One thing to note is that I didn’t adjust the concurrency from the 15 setting. If we take a look at the capacity metrics we see similar impact with approximately 15% with the background processes and looks a lot cleaner with just the 2000 background operations.

Capacity Metrics App screenshot during the flow process screenshot

With Power BI reports using the Export API call we should be able to do 55 concurrent report pages using the A4 SKU that I am testing with based on the documentation limits mentioned here. I did bump up the concurrency setting from 15 to 50 in the flow and it did complete in 80 minutes the 2nd time versus the 90 minutes initially, which means like 25 reports per minute instead of 22.

Overall to sum up the three different tests from this series of posts they were basically the same when it came to the following:

• Duration – approximately 90 minutes for 2000 reports (PDF exports)
• Capacity impact – approximately 15% on A4 (P1 equivalent) for background operations which are evaluated over a 24 hour basis

The differences comes down to the following:

• AAS Database versus Power BI Dataset
  • Advantage to Power BI having everything hosted in the service and has more features like endorsing the dataset in the tenant, using sensitivity labels, central monitoring, etc.
  • More details on the feature comparison can be found here
• Paginated versus Power BI reports
  • Format support – paginated reports support more options like Excel, Word, CSV compared to Power BI report
  • We did notice a major difference in file size where the Power BI report PDF files were 88% smaller than the Paginated report ones

So that wraps up the data driven subscription overview and comparison, let me know what you thought in the comments and provide any feedback you have on the series of posts. Thanks!