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 – Follow up using Dataset

Posted by denglishbi on August 30, 2022

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
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
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
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
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
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;)

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: