Dan English's BI Blog

Welcome to my random thoughts in regards to Business Intelligence, databases, and other technologies

Power BI Data Driven Subscriptions with Power Automate (Report Bursting)

Posted by denglishbi on August 22, 2022

Being able to do a data driven report subscription with Power BI and Paginated reports is a common request we hear from many customers. Let’s say you want to send a PDF version of a report to each of your store or department managers using a set of parameter values specific to each person. In the Power BI service that is not an option, but using Power Automate you can do this.

In this post I will be using a Paginated report that is referencing data in an Azure Analysis Services database and I will be referencing an Excel file that I have in OneDrive for Business which includes the needed information for the data driven subscription with 2000 records. The Paginated report is in a workspace backed by a Power BI Embedded A-SKU (A4 – equivalent of a P1 SKU) for testing purposes and the AAS tier is an S1 (100 QPU).

The report for this example is basic with just a single company name parameter defined that provides a list of the customers (first and last name) and the total amount due.

Paginated report example screenshot
Paginated report example screenshot

With Power Automate you can use the pre-built Power BI templates that are available and then customize them as needed. The end result of the flow I created looks like the following –

Power Automate flow screenshot
Power Automate flow screenshot

The trigger for this flow is just using a schedule setup with a recurrence which is part of the template. For my demo I am just running it manually to show how it works and to highlight a few options you need to be aware of. For the Excel file it looks like the screenshot below and you can see a few records have already been processed based on the Processed and ProcessedDTM columns (these values are being updated in the flow) –

Excel file referenced for data driven subscription screenshot
Excel file referenced for data driven subscription screenshot

In the file there is a RowId column so that I have a unique key value to reference for each record which gets used in the flow to update the processed information, CompanyName column which is used to set the parameter value in the report, user information for who will be getting the report, file name and format type, and then the processed information. The processed information is so I know when the record was last sent as well as if there is a failure or for some reason need to cancel the flow I can then restart the process and it would resume with just the records that are set to ‘No’ because of the filter included in the List rows setup for the Excel file –

List rows Filter Query expression screenshot
List rows Filter Query expression screenshot

In this example instead of emailing files to users I am simply generating the PDF files and placing them in a OneDrive folder.

Pro Tip – It is always a good idea to test your flow out to make sure it will run smoothly without any errors, determine how long the process will take to complete, and verify this will not overload the premium capacity (especially if it is being used by end users for critical reporting).

So before emailing anyone I just want to make sure the process will run from start to finish without any errors. After the process does complete I will review the metrics and determine if any adjustments should be made.

Here are screenshots of the Export to File for Paginated Reports, Create file in OneDrive, and then the two Excel Update a Row steps.

Export to File for Paginated Reports screenshot
Export to File for Paginated Reports screenshot

In the Export call I am specifying the workspace and report, you could actually provide the workspace id and report id in the Excel file to make this dynamic, referencing the File Format in the Excel file for the Export Format type, and then at the bottom setting the name of the parameter in the report (CustomerCompanyName) and referencing the CompanyName provided in the Excel file.

Create file in OneDrive screenshot
Create file in OneDrive screenshot

For creating the file I am dynamically setting the File Name based on columns in the Excel file (make sure to also include the file extension information, seems odd, but that is needed).

Excel Update a row screenshot (set processed info)
Excel Update a row screenshot (set processed info)

In the first Excel Update a Row step I set the Key Column which is RowId and make reference to the RowId value currently be processed in the loop and then set the Processed column to ‘Yes’ and ProcessedDTM column to current UTC time.

Excel Update a row screenshot (reset processed column)
Excel Update a row screenshot (reset processed column)

In the second Excel Update a Row step once all of the records have been processed in the first Apply to Each loop I simply reset the Processed column back to ‘No’ for all of the records to reset it for the next time we need to run the flow.

After all of the steps have been configured and a test run is kicked off there are a couple of things that we will see, first it reads the Excel file records extremely fast, but after further investigation on the Apply To Each step you will see that it is only loading 256 records and in the Excel CompanyList table there are 2000 records;)

Initial flow run screenshot
Initial flow run screenshot

So why is this process only loading 256 records? Hmm, thanks to Sanjay Raut who was working with me on testing this process out last year for report bursting he found this Solved: 256 rows limit – Power Automate and it turns out there is a Pagination setting on the Excel List Rows step that you need to turn on and then set accordingly depending on how many records you might have. In this case I simply set this to 2100 to cover my test sample.

Excel file list rows pagination setting screenshot
Excel file list rows pagination setting screenshot

Now when we reset the Excel file and re-run the process we see that it took a little longer to read all of the records in the Excel file and that the Apply to Each step is going to loop through 2000 records:)

Update flow run screenshot
Update flow run screenshot

Another setting that you will want to be aware of is on the Apply to Each step which controls the concurrency. This is important and you will want to adjust this accordingly depending on how long it takes to run a report and stay within the Power BI connector throttling limits (100 calls per 60 seconds). If you do not enable this then the flow will only process one record at a time, in this test I have it set to 15 (20 is the default when enabled and 50 is the max).

Apply to Each step concurrency setting screenshot
Apply to Each step concurrency setting screenshot

I figure if I can process 30 to 60 reports per minute that is really good and will stay within the throttling limits. I don’t believe that will actually happen, probably more like 20 per minute, but we will see;)

For the second Apply to Each step to reset the file I don’t have the concurrency set to avoid any type of blocking, simply loop through the 2000 records and reset them back to Processed value of ‘No’.

Now that the flow is running the PDF files are being generated in the OneDrive folder as expected –

PDF files being loaded into OneDrive folder screenshot
PDF files being loaded into OneDrive folder screenshot

I took a look at the progress after an hour and based on the Excel file I could tell that it had generated 1,384 files which is around 23 files per minute. Based on this information the process will complete in another 30 minutes, so 1 hour and 30 minutes from start to finish.

I reviewed one of the PDF files out in the OneDrive folder just to make sure everything looked good –

PDF file generated from the flow in OneDrive screenshot
PDF file generated from the flow in OneDrive screenshot

Once the process completed I verified the file count and then reviewed the metrics from the Capacity Metrics App and AAS QPU utilization is Azure portal.

Verified file count in OneDrive folder screenshot
Verified file count in OneDrive folder screenshot
Capacity Metrics App screenshot during the flow process screenshot
Capacity Metrics App screenshot during the flow process screenshot

With just this process running the capacity was not overloaded and used roughly 15% with the background process running which gets evaluated over a 24 hour period.

AAS QPU metrics from Azure portal screenshot
AAS QPU metrics from Azure portal screenshot

And AAS was barely being utilized maxed out at 5 QPU (the S1 has 100 QPU available).

Some things that I will need to compare this process against would be the following –

  1. Moving the AAS database being referenced to a Power BI dataset hosted in the same capacity as the Paginated report
  2. Using a Power BI report instead of a Paginated report

I will do both of these comparisons as follow up posts, stay tuned.

Here are some references that you might find useful in the meantime –

Just don’t forget when running this type of a load to test (and A-SKUs work great for this so you don’t impact production P-SKUs) and make sure to check out the additional settings pointed out earlier like the pagination and concurrency.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: