, ,

Workarounds for “Canceled Due to Memory Pressure”

Ah, the dreaded dialog:

The Operation Has Been Cancelled Due to Memory Pressure
The Operation Has Been Cancelled Due to Memory Pressure

You can run into this error via a number of different routes, but one way or another, your computer ran out of memory while PowerPivot was trying to do something.

I’ll list some potential fixes here:

  1. Close some other programs.  Anything you can close down on your computer to free up memory, do it, then retry the operation that failed.  Use Windows Task Manager, look at the Processes tab, and sort by the Memory column to find the biggest memory chewers.
  2. Save the file, close Excel down completely, re-open the file.  Then try again.  Sometimes the addin holds onto more memory than it needs to, and closing Excel down completely is a good way to “flush’ all of that wasted memory and free it back up.
  3. Switch to 64-bit Excel and PowerPivot.  I highly recommend this anyway for anyone who is going to be using PowerPivot a lot.  You’ll see the error above a LOT less, and it will greatly reduce the rate at which you see other errors too.
  4. Switch to PowerPivot V2.  I don’t have a lot of hard-core production experience with V2 yet but I understand that V2 is “friendlier” when it comes to RAM usage.  (Technical detail:  V1 basically refuses to “page out’ to disk, and relies 100% on your physical RAM.  V2 allows for some paging, but I don’t know how much impact that will have for you.  I suspect that 32-bit –> 64-bit is more impactful than V1 –> V2 but can’t be sure.)

***EDIT:  PowerPivot V2 does NOT use paging, only tabular BISM servers do, so upgrading to V2 will NOT improve RAM consumption.  In fact in one comment below, it was reported that V2 requires even MORE RAM than V1.

If it’s happening during data import or refresh…

If you are importing a large table for the first time, consider importing fewer columns if that is an option – a topic that has been covered extensively.  (Fewer rows are also helpful of course).

If you are refreshing a large table that has calc columns in it,  those calc columns are re-evaluated during refresh.  And sometimes those calc columns themselves are the reason why you run out of RAM during the import.

The first thing to consider, then, is whether you can calculate those columns outside of PowerPivot, such as in the source database – this provides a lot of benefits.

In case of emergency, break glass

If the calc columns do seem like the culprit but none of the above is working for you, the other trick I “discovered” this weekend is this:

  1. Copy/paste the calc column formulas into notepad.
  2. Delete the calc columns from PowerPivot.
  3. Refresh your data
  4. Re-add the calc columns one by one

That worked quite well for me this weekend when I was importing 30 million rows of data into a table with multiple (and complex) calc columns, and performing the calc columns in the data source wasn’t an option (because the data source was CSV).  Even on 64-bit PowerPivot (v1) with 16GB of RAM, it was failing until I did the calc column “dance” in steps 1-4.  Tedious, but effective if you need it.

Read more on our blog

Get in touch with a P3 team member

  • Hidden
  • Hidden
  • This field is for validation purposes and should be left unchanged.

This field is for validation purposes and should be left unchanged.

Related Content

Exciting Improvements to Power BI’s Export to Excel

It is a standing joke in the analytics industry that “Export to

Read the Blog

The case of the disappearing Field Parameters: SOLVED

The Case: The new Field Parameters feature from Microsoft had been added

Read the Blog

Completing the Set Up: Field Parameters Using Tabular Editor

May 2022’s release of Power BI Desktop is the best releases we’ve

Read the Blog

Calculation Groups to the Rescue!

o set the stage, I need you to travel back in time

Read the Blog