Dataset Refresh Fails with error : The evaluation reached the allowed cache entry size limit

[AndreasDest]

The refresh of a dataset on PowerBI service fails and the error given is "We're sorry, an error occurred during evaluation.;The evaluation reached the allowed cache entry size limit. Try increasing the allowed cache size.. The exception was raised by the IDataReader interface.". Locally the refresh is completed without any issue. We are using pro licenses. The size of the dataset is ~400Mb and its sources are Azure SQL Database and Excel Files located on Sharepoint Online. No Power BI Gateway is being used to refresh the data on the service. I have increased the maximum allowed Mbs of Data Cache Management locally on PowerBi desktop.

I can't find any reference about the cache usage and how I can reduce it on the dataset or even which are its limits on Power BI service. It seems like a black box. The problem started after I added some data (minor in terms of volume) from excel files on Sharepoint online, I don't know if it is a coincidence.

Any help and guidance would be highly appreciated!

Best

[StefanoGrimaldi · Accepted solution]

hey, try reducing the number of simulstanious evaluation it does in addition to increasing the maximum MB permit per evaluation(that you aleady did). 

also try to norrow down the query giving you the error you can run each query one by one and see the amount of time taking each one and the amount of data its pulling. them send over the query script to see if theres a optimization issue with the code. 

if all fails also open a support ticket for microsoft since they can provide you additional information on the server error its giving you if you cant access any more information about the incident to determine (also try running each query as a dataflow to see how its behavite in the web service). 

hope this give some orientation to start with 

[AndreasDest]

The answer was in "try running each query as a dataflow to see how its behavite in the web service". When I moved the queries on dataflows I was able to spot the query giving the error.

[AndreasDest]

Thank you very much for your response.

I reduced simultaneous evaluations but with no luck. I reduced the complexity (removed some merge steps) of some of the queries but still get the error.

Currently I am moving the queries to dataflows but it is considerable work.

I will come back with feedback later.

Best