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

In the last post I went over using Power Automate to perform a data driven report subscription using a Paginated report referencing an AAS database. 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.

For the follow up there were two items that I wanted to compare against the original flow

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

In this post I will cover the first comparison. I went ahead and deployed the existing AAS model to the premium workspace being used for the report bursting test. I did this using the Visual Studio project along with the XMLA endpoint. For this to work you will need to make sure that the XMLA endpoint read/write is enabled for the capacity as well as having the XMLA endpoints enabled in the tenant-level settings which are enabled by default.

Once the settings are all enabled then you just need to get the workspace connection information to use the XMLA endpoint and then make sure your model is using compatibility level 1500 which is supported in Power BI Premium.

Visual Studio Tabular Model Compatibility Level 1500 screenshot

Then it is simply a matter of setting the server information for the deployment using the workspace connection information and deploy the model.

Visual Studio Deployment Server Information screenshot

To test the Paginated report with the new model I went ahead and updated the data source connection information to reference the workspace connection instead of AAS. After you do this you will then need to switch over to the ‘Credentials’ section in the properties to enter your user name and password to authenticate.

Paginated Report Data Source connection string screenshot

Once you have authenticated you can then publish the version of the Paginated report referencing the Power BI dataset to the workspace. Now we are about ready to test the Power Automate flow with the Paginated report using the Power BI dataset, just need to update the flow to reference this version of the report which is easy to do. I would also do a quick test with the new report just to make sure it runs fine in the workspace without any issues prior to running the updated flow.

Power Automate flow with updated Paginated report reference screenshot

Once again we let the process run and it completed in approximately 90 minutes and then after reviewing the metrics app we see very similar metrics with the background operations using roughly 15% of the capacity and these operations get evaluated on a 24 hour period.

Capacity Metrics App screenshot during the flow process screenshot

So really not much different than running the process against AAS, expect now we have everything running entirely in our Power BI Premium capacity, so we can leverage all of the features like endorsing the dataset in the tenant, using sensitivity labels, central monitoring, etc.

In the next follow up post we will test out the process using a Power BI report instead of the Paginated report, so stay tuned;)

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

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

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

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

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

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

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

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)

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)

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

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

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

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

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

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

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

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

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
   • Post – Power BI Data Driven Subscriptions with Power Automate – Follow up using Dataset (2022-08-30)
2. Using a Power BI report instead of a Paginated report
   • Post – Power BI Data Driven Subscriptions with Power Automate – Follow up using Power BI report (2022-09-06)

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 –

• Export Power BI paginated reports with Power Automate
• Report BURSTING with the Power Platform (Guy in a Cube video by Patrick LeBlanc)
• Export paginated report to file

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.