Export Paginated Report to Excel - Performance

[Rakesh112233]

Hi - I'm using Power Automate flow to export a paginated report to excel. I'm on office 365 E5 license so Automate is included in it.

When i run the report in Power BI and export it manually it takes less a minute whereas when running from power automate its taking around 6 to 7 mins every time. 

Any ideas why automate is taking such long time, is there any throttling going on? How do i speed up the export?

[UrsineWelles]

Hi Rakesh,

I am having a very similar performance issue with exporting a PBI Paginated Report to PDF and saving it to SharePoint. The PBI export step is taking about 1 minute for each report in an Apply To Each loop and to save ~400 reports is therefore going to take more than 6 hours. An SSRS subscription to a network drive doing the same job takes ~30 minutes. As you found, when accessing the report directly in PBI and refreshing it then exporting it to my Download folder takes a few seconds. We have PBI Service Premium and an enterprise licence for Power Automate.

Is there anything that I can do to improve the performance of the PBI Export Paginate Report step?

John

[Rakesh112233]

Unfortunately this forum doesn't seem to be active unlike the power bi community forum - but obviously there are people like yourself and me coming up with similar, practical issues.

You seem to be in better situaton with licensing? can you raise a support ticket with microsoft on power automate?

perhaps you can increase the concurrency on for each loop and see if that helps speed up the report generation. I tried that and was throttled...

[UrsineWelles]

I can raise a support ticket but I wanted to do a quick check with the community first in case I was missing something simple.

I will look into raising the concurrency for each loop too.

[UrsineWelles]

Using concurrency was a really good idea - on the default setting of 20 my Flow finishes in 30 mins. Thanks!

The individual PBI Paginated Report exports are still slow (2 mins each now) but the parallelisation has made this much less of an issue.

[Rakesh112233]

thats great. I'm premium per user license for power bi and office 365 e5 standard license for automate. I can't get my report exported to excel as apparently there's a bug that prevents exporting more than 102,250 rows to excel from paginated report. 

could you still raise a ticket with microsoft on automate performance pls?

[Anonymous]

I am also facing the same issue, Export to excel from paginated report not exporting more than 102,250 rows. can any one help.

[ObiShyamKenobi]

As a reference for others, I found this article - https://docs.microsoft.com/en-us/power-bi/enterprise/service-premium-per-user-faq

Where only one call is allowed to be made every 5mins on PPU. I am not sure how it is on PPC (Premium capacity)

[Anonymous]

Hello Sir,

from your post dated  ‎02-28-2022 06:50 AM to Rakesh:

I am having a very similar performance issue with exporting a PBI Paginated Report to PDF and saving it to SharePoint. The PBI export step is taking about 1 minute for each report in an Apply To Each loop and to save ~400 reports is therefore going to take more than 6 hours.

I'm trying to tackle similar PBI -> SharePoint PDF looping export setup and have trouble finding a correct mix of PowerAutomate templates, and from your post I read it's quite lengthy process too. Could you please share your advise on such setup, and which template(s) you would recommend in that regard?

Thanks!

[UrsineWelles]

Hi PowerArsenal,

My structure is fairly simple. My PBI Paginated report takes one input parameter. I have an Excel file folding a list of the parameter values. The PowerAutomate flow uses an Excel GetItems step to get the list and an Apply to Each runs over the list, running the ExportToFileForPaginatedReports step, with the ParameterValues name set to the name expected by the report and the ParameterValues taking the parameter value from the GetItems step. A save file to SharePoint step then saves the output pdf.

[Anonymous]

Hi UrsineWelles and many thanks for sharing your setup. Will further explore these choices.