Power BI Desktop Memory Issue Resolution - Part 1

Colin Maitland, 05 February 2019

In this article I will describe a “Failed to save modifications to the server …  not enough memory to complete this operation” error I encountered in Power BI desktop and how this issue was resolved.

The Power BI Data Model included a Referred Contact dimension table, Referrals fact table and a Date dimension table. The Referred Contact table included a Date of Birth column which was used for calculating the Age at Date Referred. This was required so that Referrals could be subsequently aggregated by Age Range.

All went well with the development and testing of the Data Model against an initial small and incomplete set of sample data until after importing a larger and more complete set of sample data comprising of 10,000 Contacts and 5,000 related Referrals into Dynamics 365 and then attempting to refresh the dataset in Power BI desktop.

After a couple of minutes of computer activity with various degrees of high CPU usage and an increasing degree of Memory usage as monitored using Windows Performance Manager, the following error was displayed and the refresh was cancelled.

Initially, this was confusing because there was not a lot of data being loaded into the Power BI Data Model from Dynamics 365. Only those fields and records that were required by the Data Model were being loaded. It was also confusing because the issue had not been encountered prior to my loading the new sample data.

In addition, the ‘Auto Date/Time’ option in the report file was turned off so that no hidden Date dimension tables would be automatically added to the Data Model by Power BI. I was instead using a single Date dimension table created using the DAX CALENDARAUTO() function.

A number of Date fields were included in the Data Model. These included Date of Birth in the Referred Contact table and four Date fields in the Referrals table; i.e. Date Referred, Date Active, Date Discharged and Date Repeated. These were all Date rather than Date/Time fields and so were highly compressible; i.e. only 365 possible unique values per year rather than 31 million possible unique values per year.

My initial investigation identified the fact that the issue only occurred when the Date of Birth field from the Referred Contact table was included in the dataset. The issue did not occur with respect to the inclusion of any of the other Date fields. I then realised that unlike the previous smaller and incomplete sample data the new larger and more complete sample data included Date of Birth values for almost all the Referred Contacts and these ranged from 1923 to 2018, a period of 95 years. Prior to this, the sample data did not include any Date of Birth values.

This pointed me to the fact that the issue was related to the Date dimension table which was being automatically-generated using CALENDARAUTO(). This function generates a set of Dates from the start of the year of the lowest Date value found in the Data Model to the end of the year of the highest Date value found in the Data Model. In my case that was from 1/1/1923 to 31/12/2019; i.e. from the start of the year of the earliest Date of Birth to the end of the year of the latest Date value from the Referrals table, a period of 96 years. The CALENDARAUTO() function had only generated just over 35,429 Dates. In addition, my Date table contains 25 columns, 2 hierarchies and 4 measures. This, however, is not completely unreasonable for a Date dimension table and was not by itself the root cause of the issue.

In part two of this article, I will describe the cause and resolution of this issue.