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 –
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.
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).
And this time…success! I was able to import an additional 7.5MM rows of data into PowerPivot.
After this import my Excel file was hovering around 700MB of RAM consumption.
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: