Slow Power Automate Workflow when Emailing Paginated Report Exports

[Anonymous]

I am trying to improve the speed of a solution I've built to export Paginated reports into excel and distribute them to a predeterimened email recipient list. For vaious business reasons, I cannot send users straight to Power BI Service, so I created a Power Automate flow that retrieves a list of emails, uses them to filter a paginated report, exports the report based on that email's list of stores, and then sends those exports to the respective emails (pictured below). 

Obviously 2 hours is too long and I'd like to find a better solution. Is there a way to make these run concurrently, or am I missing an absolutely obvious fix I could be using?

Thanks!

Also example images below:

(Flow)

(Power BI Paginated Report Parameters)

[nilimarodrigues]

Here is what I have observed. When I am fetching paginated reports for export in power automate from "Dedicated Capacity" workspace, power Automate completes the job in 5 minutes.

But when I am fetching the exact same paginated reports that are published on a non "dedicated capacity" workspace (workspace created by Premium Per User) then it takes 20 minutes.

I as a user executing the power automate flow has "Premium Per User" license. 

Wondering why power automate is taking more time for one type of workspace as compared to the "Dedicated Capacity" one. Any ideas?

[mlathouwers]

My colleague noticed that running dataflows is also significantly faster in a premium environment. Must be part of the shared resources vs dedicated resources

[Raveendra]

We are using premium per user plan only . how to  to spin up  like steps to Embeded capacity you mentioned steps in earlier post

[kstiegler]

I ran into a similar situation recently, maybe this will help you - after much testing, I determined that the cause of my bottleneck was the MS-enforced rate limit for exporting from paginated reports: https://docs.microsoft.com/en-us/power-bi/developer/embedded/export-paginated-report#ppu-concurrent-... After further investigation with one of our Power Platform consultants, enabling a Power BI Embedded capacity removes the rate limit. For my scenario, it sped up the paginated report exporting from the 5 minute limit to 40 seconds per report, obviously much better. 

It's possible you're running into the same situation, hard to say for certain.

[Raveendra]

Please help me enabling a Power BI Embedded capacity removes the rate limit. currently we are using Premimum per user plan.

[Syndicate_Admin]

Hi , Hope you doing good.

can you please help me to get it done mentioned steps.

Regards,

Raveendra

[taft3je]

We are also working to automate the delivery of our PBI Paginated reports to our sales force using Power Automate.  I read your response and the linked information, but I am still not sure how this reduces the time between exports.  For the record, I am just technical enough to be dangerous.  

For our reports, I created the data model in PBI Desktop and published the model to a Premium Workspace.  Using the data model, I created a Paginated report.  Then, I imported a list of reps, and I used that list to create the parameter.  I am using the parameter as a filter on each tablix.  
The Power Automate runs a stored procedure which is parced into the rep and the reps email address.  It then loops through the parameters to run the report, saves the file as Excel, and emails it to the specified address.
In this setup, how do I use the Embedded Capacity to improve the delivery speed of the reports?

[kstiegler]

Are you seeing a 5 minute minimum time per report export? You may not be since you're exporting to Excel and not PDF, I haven't tried our flow with Excel. In our case, there was a 5 minute limit for exporting to PDF that was caused by the Power BI capacity we were using. Increasing the capacity to Embedded (previously Premium Per User) removed that artificial 5 minute rate limit.

[taft3je]

Our MIS Manager figured out how to link the embedded capacity to the Premium Workspace.  We ran into the same time contraints regardless of if the export was Excel or pdf.  I tried both.  Now, he is trying to automatically turn on the capacity 30 min prior to running the reports, and turning it off 30 min after anticipated completion.  Do you know if you still need the embedded capacity if you have the Premium Per Capacity license?

[kstiegler]

I'm not sure about a Premium Per Capacity license, but I do know that a Premium Per User license is insufficient to remove the rate limit. I don't have a Premium Per Capacity license to test it.

[Syndicate_Admin]

Please share the settings of the same

[taft3je]

We were still seeing the reports run every 5 min.  I assumed it was because of the metering because when I reun the report manually inside Power BI, it runs in about 30 sec.  

There is a new challenge.  Since I moved the report to a Power BI data source, the report will not run through Power Automate.  My reports that are using SQL queries are running fine (slowly but fine), and the new reports do not seem to be running.  I know that is off topic, but it is frustrating.  We are brand new to PBI Paginated reports, and it seems to be one frustration after another.

[mehrdad]

@Anonymous Hi. Have you found a solution for this? I'm experiencing the same.

[mlathouwers]

Hi @Syndicate_Admin , sorry but I can't read your screenshots. But if you are sending the same report to multiple people, it is obvious to create the report 1 time and send is to several recipients at once.  

[taft3je]

We worked our way through this.  I have a Premium Per User license.  With this, I created a Premium workspace in our Power BI service.  

We then signed up for Power BI Embedded capacity, and linked the Premium Workspace to the embeded capacity.  The downside to this is that you can only use the reports in that workspace while capacity is running.  The cost of the Embeded Capacity is about $1 per hour ($700 per month), but it is pay as you go, so we use Power Automate to control the reports and the capacity.  

When Power Automate kicks off the flow, it checks the status of the embedded capacity.  If capacity is off, it turns it on, then it runs the reports (about 80), and when the reports have completed, capacity is turned off.  It generally takes about two hours to run the reports, so each one takes about a minute and a half to run publish and email.