Memory Allocation Failure on Service (Power BI Pro)

[Anonymous]

Hello everyone, 

I'm seeking your help and advice because I'm not able to automatically refresh my report on service anymore (Power BI Pro).

I've set up a daily automatic refresh of my report since may 2019 and it worked perfectly so far. My data source are Azure SQL server and sharepoint. My report has around 10+ tables, the two largest ones are 5,6 and 2.1 millions rows, the rest is significantly lower. My .pbix file weight 73mo. 

My first issue started when I was unable to refresh it on my desktop version because of memory failure. So I added 8go of RAM which gave me 16go total and solved my issue. 

However a couple of days after that change and the update of my report on service (few small additions), the scheduled refresh started to crash, giving me the following message :

I've looked online and try doing some query optimisation. I've removed any large merging and optimize the columns of my large tables (14 columns for the largest and 8 for the second one). However it didn't change a thing. Unfortunately this report will grow every month (historical KPI on a monthly basis) and new features are required and added every now and then. 

Is there anything I could do to improve the performances of my report and allow online refresh ? Obviously Premium would be an answer but that's not in my hands. Maybe data flows ? Can the new query diagnostics be of any help ? 

Thanks a lot for your help !

[kallevaataja]

@Anonymous @ShNBl84  have you found any solutions? I have similar issues and feeling that something has changed inside PBI Service. My refresh works fine in Desktop, but I get the same error message in PBI Service.

[ShNBl84]

A few of my reports have been failing to apply changes starting about a week ago. I reverted to an old version from a month ago and it is getting the same error even though it worked a month ago. I think there was some kind of software update that is causing the error. I even uninstalled/reinstalled PBI yesterday to try to fix it. It didn't help.

[Anonymous]

Hi @Anonymous ,

Have you added any advanced operation in your query tables? (e.g. merge, combine, reference...) If this is a case, it may spend more memory resources on these operations because of the looping calculation.  

You can take a look at the following link to about use table.buffer/list.buffer to reduce the additional spend on these operations.

How and When to use List & Table Buffer?

Regards,

Xiaoxin Sheng