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?
Also example images below:
(Power BI Paginated Report Parameters)
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.
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.
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?
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.
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.
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?
Check out the News & Announcements to learn more.
Participate in the Power Virtual Agents Community Challenge
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.
Check out our new Discover Your Career Path blog post series and get all the details.