Query execution failed for dataset - Paginated report

[sqlguru448]

Hello Folks,

I have a paginated report published to Power BI workspace, the report runs fine for small set of data but when it outputs ~55K rows for 60 columns it gives below error, can you please help me out if any capacity settings needs to be changed without impacting other reports which are created in Power BI?   

There is no issue with gateway/dataset or the query behind the report as it runs fine on SSMS.

Any help is really appreciated.

Thank you.

[GilbertQ]

Hi there

To me this would appear to be a capacity issue due to the combination of rows and columns.

You could look at the Premium Metrics App to see if you are hitting your paginated reports memory allocation.

To resolve this you would need to assign more memory to paginated reports.

[sqlguru448]

Thank you for the quick response, currently the Max Memory of Paginated reports is set to 20%.

By changing the Max Memory of Paginated reports will it impact any other settings? or will decrease the memory allocation for reports/dashboards which are created in Power BI? 

[GilbertQ]

Hi there

Yes changing the setting will certainly affect other workloads in Power BI Premium.

I would suggest looking into the Premium metric App to see how the memory allocation is being used?

https://docs.microsoft.com/en-us/power-bi/admin/service-admin-premium-monitor-capacity

[sqlguru448]

Thank you.

I have checked the app, it's using the full allocated memory for paginated reports, I cannot increase it since it will impact other work loads. please suggest.

[GilbertQ]

Hi there

It would appear to run your query you will need more memory.

The only alternative other than more memory is to reduce the number of columns, rows or both!