Dan English's BI Blog

Welcome to my random thoughts in regards to Business Intelligence, databases, and other technologies

PowerPivot Memory Error – Not Enough Storage

Posted by denglishbi on June 21, 2010

I just got the Professional Microsoft PowerPivot for Excel and SharePoint book and I started working through the tutorials.  Well right out of the gate I received an error message – Memory error: Allocation failure : Not enough storage is available to process this command. . The operation has been cancelled.  My initial reaction was to check the hard drive space.  I had around 15GB of space available, so that wasn’t the issue.  I then checked my available memory (RAM).  And I had just about nothing left.  I went ahead and shutdown every service and application that was not needed, along with restarting my SQL Server database service, so that I just had Excel open.  At this point I had 1.6GB of RAM free and tried to reload the table, FactSalesQuota from the new Contoso Retail DW database, that I was unsuccessfully able to load initially.  Still no luck -

image

I was baffled.  Initially I was able to load the required dimension tables, FactInventory (over 8MM rows), FactSales (over 3MM rows), but I couldn’t load the FactSalesQuota (over 7MM rows) data.  I found it odd that the sales quota data was double the sales fact table, but i believe this is because of the scenario dimension.  Anyway, I was try to figure out what to do next.  I should rewind a bit and state that I am run the 32-bit (x86) version of Excel 2010 and PowerPivot add-in.  The reason for this was because it was the easiest upgrade path for me from Office 2007 since I didn’t want to uninstall the 32-bit version of Office 2007 in order to install the 64-bit version of Office 2010.

I should also mention that when I initially loaded all of my fact tables that were required for the first tutorial I had removed the ETLLoadID, LoadDate, and UpdateDate fields since these would not be needed for analysis and didn’t want them to consume memory and space.

image

So now that I was stuck in a rock and a hard place because of my 32-bit limitation I reviewed the fact table to see if I could possibly remove some unneeded data that was possibly outside the related fact sales information, but it was the same range of dates.  Then I check the columns in the table and realized that there was an identity field setup on the table.  Well I don’t need this column for analysis or relationships, so I went ahead and removed this column as well during the import process to load the data into PowerPivot.  Plus this column is unique per record, so no compression would be performed during import (I would recommend removing the other fact table identity columns as well).

image

And this time…success!  I was able to import an additional 7.5MM rows of data into PowerPivot.

image

After this import my Excel file was hovering around 700MB of RAM consumption.

image

Now I was off to the races in my 32-bit environment.  I would highly recommend going with Office 2010 64-bit at least for Excel if you are going to be doing anything with PowerPivot – at least this way I can experience what other users will go through and be able to help troubleshoot their issues.  I will have more posts coming out shortly related to the tutorials and my experiences and feedback in regards to PowerPivot, so stay tuned.  For more information in regards to PowerPivot memory consumption issues check out the following additional links:

About these ads

4 Responses to “PowerPivot Memory Error – Not Enough Storage”

  1. [...] PowerPivot Memory Error – Not Enough Storage [...]

  2. Mark Z said

    I removed all the key columns and still got the error importing FactSalesQuota. This is on a 32-bit Win 7 system with 4G of ram and 32G of disk. Nothing else was running but IE.

    • denglishbi said

      Did you remove the ETL related columns as well? I never retried to import everything again after I received this error. You do need to define the relationships on the table when you import it successfully since it doesn’t get imported during the time the dimensions are imported. I ended up installing x64 of Office 2010 to avoid running into this issue again.

  3. JSchutt said

    That was a great suggestion. I did the same and it worked.

    Thanks for saving me from searching for hours for a solution!

    -J

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 90 other followers

%d bloggers like this: