Workarounds for “Canceled Due to Memory Pressure”

 
Ah, the dreaded dialog:

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.