skip to Main Content

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.

Rob Collie

One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

This Post Has 2 Comments
  1. Switching from V1 to V2 while using 32 bit PowerPivot had exactly the opposite effect for me. Models that never gave me any memory issues would not refresh no matter what I closed in the background with V2. I would switch to 64 bit, but I can not pull data from our 2000 servers. It is a bit dis-heartening. I was an early adopter, but find myself creating more and more work-arounds because of lack of centralized IT support. I have gone back to vanilla pivot tables and VBA/scheduled tasks for most of my day-to-day reporting.

    I am curious to hear if there are other non IT-pros out there that are having similar trouble.

  2. The move to 64bit completely changed my PowerPivot experience – I haven’t seen the memory error since I did whereas it was a regular occurrence on 32 bit.

    When I was restricted to 32bit I did find that my much lower spec 2nd PC running XP was much less prone to memory errors than my main machine, it sometimes took it a while but it generally got there eventually!


Leave a Comment or Question