Memory error: Memory Allocation failure . Try simplifying or reducing the number of queries.

[cmsouth]

This PowerBI worked for the past year, but has recently started failing during scheduled refresh with the error "Memory error: Memory Allocation failure . Try simplifying or reducing the number of queries.".  I can refresh in desktop and when i manually click the refresh the data set in the online site, but fails during the schedule (on-prem data gateway).  I have moved the refresh time to different windows in case there was a conflict with other refreshes.  We've changed the server memory (its a virtual Hyper-V).  Its strange that it will refresh manually, but not on schedule.  Suggestions?

[v-cazheng-msft]

Hi @cmsouth 

My I know whether this issue happens when you set scheduled refresh at a relative fixed time? If you change the scheduled time, will you get same issue? What’s the data source of the dataset? What kind of connection mode does this dataset use to connect to the data source? Is this dataset created on a large amount of data from data source?

Best Regards,

Community Support Team _ Caiyun

[cmsouth]

To answer your questions:

1) It is scheduled to refresh daily at 4am EST.  I have tried different times after the refresh started failing, but receive the same error.  I can usually manually refresh the dataset in the app.powerbi.com screen the day I upload a new version, but even the manual will fail after a day or so.  Then i have to refresh on desktop and upload a new version.

2) The datasource is a MySQL query against an AWS RDS MySQL instance. It uses the MySQL connector to access the database and calls a procedure that runs the query.   
  I have 20+ other PBi's that run against the same MySQL instance with no other errors. 

  In this PBi, there are 5 queries, 3 that are larger and return 6k, 15k, and 40k records.  The other 2 are used for last update date and date dimension (7k),  The queries takes 5 minutes to refresh from the desktop.

[v-cazheng-msft]

Hi @cmsouth 

Thanks for your reply! I understand and this issue only happens in Power BI service but not local Power BI Desktop. May I know whether this report is published in a shared but not a Premium capacity(there is a diamond icon beside the premium workspace name)? According to the error information, it seems like there isn’t enough memory to handle these queries. I do suggest you simplifying your calculation and reducing the data amount used or you may consider opening a support ticket to ask for further help if you are a Pro user. For how to create a support ticket, please refer to this blog.

Best Regards,

Community Support Team _ Caiyun

[AGo]

Same problem for me, my dataset has always refreshed correctly for the past couple of years, but not now.

[AGo]

My collegue is telling me that this problem is getting more frequent since 6 months even with the same amount of rows and dax code, due to dax measures or columns, it's like the service is reducing memory or computational capacity available to Pro licences.

[aggregator3000]

Hi @cmsouth @AGo 

I had this issue for the past month, and found an easy fix

Of course, before using that fix.. I rebuilt most of the report to no avail. Had to refresh locally daily and republish...

If I deployed fresh to another workspace it would refresh fine in Service (pro only).. was not RLS related either.

As it seemed to be Service-Workspace specific I thought maybe there is a cache going on and that would have an issue or be too big.

Searched around that and the first result solved it for us

It was fixed simply by clearing the caches in the PBIX file and then republishing. That solution is here

Hopefully this works for you. Good luck

[cmsouth]

@aggregator3000 - You ROCK! That solved my problem - refreshed first time on its own in months.  I had a ticket with MS and they told me to redo a bunch of the queries, but that didn't make a difference - your solution fixed for me!

[aggregator3000]

@cmsouth great outcome, you're very welcome.. Hopefully this helps other people who have sunk many hours into it like us.

[ramdasanudas]

get more space in C: drive
or
Try to clean the data load cache: File -> Options and Settings -> Options -> Data Load -> Clear cache and Save the file and Load again