Failed to save modifications to the server … not enough memory to complete this operation

[Bestball]

Hi All,

Happy New Year.

I have a report which had run well until I attempted to add a new data source in Power BI Desktop.

I'm using the latest version of Power BI Desktop [Version: 2.88.802.0 64-bit (December 2020)]. And my laptop has 8G ram.

It went well when I added this new data source in Power Query. After the "Apply & Close" button was clicked, the "Load" pop-up window occurred while the memory usage was increasing very quickly. A mass of memory was taken over by the "Power BI Desktop" process and its sub-process "SQL Server Analysis Services". After a couple of minutes, the error "Failed to save modifications to the server … not enough memory to complete this operation" occurred. I've unticked the "Auto Date/Time" option in the current file. But it doesn't work. 

The size of the new data source is very small, with only 400 rows and 10 columns. There is a column of Date type. After an investigation, it was the Date type column that caused this error! If I removed the Date column from the data source, it can be added to the data model successfully.

I guess that when "Apply & Close", Power BI automatically (and stupidly) associated this new data source to my Date Table (created by DAX). In the Date column of the new data source, there's few "very old" date, like "15/5/2001" (the date columns in my fact table start from 2014). I am not sure if it caused my Date Table (created by DAX) was expanded to 2001. Probably, the fact table (or the visual cube?) expanded as well?

So is there any method to turn off Power BI automatically (and stupidly) associating the new data source to my Date Table? I don't need this function at all. 

Thank you very much.

Alex Lu

[aviral]

I was able to solve this error by increasing the Maximum allowed cache to 64000 MB. 

"File"-->"Options and Settings"--> "Options"--> "Global-Data Load"--> "Data Cache Management Options"

Also, ensure that you either have enough RAM or increase the Virtual Memory to the same size. 

[Anonymous]

I had the same issue but at the end the is issue is totally different.

We found Blank /null rows in one of the table which was causing the issue , Removing those blank rows fixed he issue.

[v-shex-msft]

HI @Bestball,

I think the date table should not the root casing of your scenario. Have you turned off the 'relationship detect' options to disable auto detect the table relationships? 

Create and manage relationships in Power BI Desktop 

How many records are your data table hosted? Any complex expressions calculation(multiple iterators, nested cross table looping calculation ....) and advanced operation(merge, combine, reference, invoke custom functions, recursion...) existed in Dax or query editor side?

Optimizing nested iterators in DAX 

Recursive Functions in Power BI / Power Query 

AFAIK, they may affect the performance and spend a huge amount of system resources.You can also take a look at the following document about performance tips:

Optimization guide for Power BI 
Regards,

Xiaoxin Sheng

[Bestball]

Hi Xiaoxin,

Thank you.

The 'auto detect relationship' has been disabled since I created the report. There're over 500k rows in my fact table and I do have a few complex DAX. But there's only 15 rows and 4 columns (2 TEXT columns and 2 DATE columns) in the new data source which caused this problem.

I did further troubleshooting. In Power Query, I changed the type of the 2 DATE columns to TEXT (for instance, it's still '31/12/2099', but it's a string, not date), the problem would not happen after I clicked the "Apply & Close" button.

I guess Power BI automatically does some extra pre-calculation to optimise DATE type. But this might bring side-effect in some situation. For examples, in my case, I will not want the DATE columns in the small table to join my date dimension table.

Kind regards,

Alex Lu

[akfir]

is removing any unnecessary date columns on PowerQuery may be a solution as well? 

[v-shex-msft]

Hi @Bestball,

Yes, power bi will do some processing with date fields. (it will generate the hidden calendar to mapping date fields for further chart record summarize and time intelligence functions usage)

Apply auto date/time in Power BI Desktop 

Currently, you can't do customized with this hidden calendar.

Regards,

Xiaoxin Sheng

[ChemEnger]

Just to keep this thread fresh.  I too have had a report that's been running smoothly for a couple of years, latest count 56000 rows x 16 columns.  Then it fell over - would not refresh with a SQL Server query either native in Power BI Desktop or importing from Dataflow (preview looked OK in both instances).

Turns out there was a typo in a date field in the SQL database - someone had entered 31 May 3392 instead of 2023 (!)  Excluding this row solved the issue straight away.

[amitchandak]

@Bestball , In the Options and setting disable Auto Date time option , Increase the Cache size to max .

Refer to these setting: https://www.thebiccountant.com/speedperformance-aspects/

[cbritton]

Amazing! This small configuration change alone resolve my own out of memory error (we have hundreds of Date columns in our data model) while also reducing data refresh time and overall file size. THANK YOU!

[Bestball]

Hi Amitchandak,

Thanks for your advice.

The "Auto Date/Time" option has been off.

I increased the Cache size from 4G to 8G, but the error still happened.

Alex Lu