Memory error: Allocation failure

Question

I catched an interesting case for memory error.
Scenario: I have a 6.5 millions rows fact table in a data mart and I wanted to load it in PowerPivot.
I was looking for differences between normalizing dimensions in different PowerPivot tables or denormalizing them in a single table (joining all dimensions I want in a single query).
Well, everything worked well using a large dimension with 254K rows. But, at a certain point, trying to understand a particular behavior, I included a column that is an identity key of the fact table (I know, Kimball's theory don't require it, but we often use it for maintenance purposes). And, in this mode, boom, as soon as all the rows are imported, the PowerPivot generate this error:

Memory error: Allocation failure : Not enough storage is available to process this command. .
The operation has been cancelled.


Thus, my understanding is that the cardinality of attributes is very important for memory usage while importing data.
Just before throwing this error, the Excel process was using 650Mb of Commit Size (read on task manager) and there was plenty of free RAM (more than 1Gb).

At this point, my understanding is that the cardinality of attributes you are importing (especially in the fact table, when you have millions of rows) is very important in determining the size of the file and (more important) the memory required to process data.

Is there someone that can explain better what's happening, so that I can understand how to anticipate possible memory problems - and in general how to optimize data for a PowerPivot model?

Marco

---

Marco Russo

Answer 1

There are many experts who can provide lot of insight about this. Here are my simple views on your situation,

PowerPivot does smart column based compression when the column contains non-unique values the compression rate is going to be lower and thus you might encounter the error while importing millions of rows. If you are using x32 bit, I would recommend try x64 bit Excel which should overcome this memory address limitation issue and will let you import fine.

I will let other experts to provide their deep insight on the situation.

Thanks,
Deva [MSFT]

---

Deva

Answer 2

Deva,

I know x64 bit Excel will solve the issue, but I know there will be a lot of users still using x32 bit, myself included (on my primary notebook and workstation).
At least two reasons for that:

• You cannot mix 32 and 64 bit versions of Office applications, and I have addins in Outlook that are not available in 64 bit
• Several customers will have 32 bit versions of Excel. I have to know what to do if I want them to be able opening an Excel PowerPivot sheet.

And, of course, I know publishing Excel on SharePoint with PowerPivot is another option to share data with users that don't have a 64 bit version of Excel.

---

Marco Russo

Answer 3

Knowledge is power.
Finding out how much space is being taken by the various tables and columns is the first step to tuning your memory use.
See a tip here: http://powerpivotgeek.com/troubleshooting/client-side-issues/ (see item C.)

Hope that helps.

---

Dave Wickert (MSFT) blog: http://www.powerpivotgeek.com/ Enjoy!

Answer 4

Dave,

this is exactly what I did to find the consuming columns. The strange (or not) behavior I have seen is that removing a column that consumed a lot of spaces sometime doesn't reduce the space requirements of the same amount, because at that point another column starts consuming much more space.
I can intuitively understand why it happens, but I'm not able to explain it, how to anticipate it and (most important) how it really works - for this reason I started the thread! :)

Marco

---

Marco Russo

Answer 5

Marco, that sounds very strange. I am not aware of any case where we are adjusting one-columns's in-memory allocation based on a different column.

---

Dave Wickert (MSFT) blog: http://www.powerpivotgeek.com/ Enjoy!

Answer 6

Dave, I tried to repro the case and I wasn't able to do :(
I don't remember the case where I observed this behavior - maybe I wrongly interpreted some numbers. I'll come back to this thread if I will be able to observe that behavior again.

In the meantime, I published a blog post with my considerations.

---

Marco Russo

Answer 7

I catched an interesting case for memory error.
Scenario: I have a 6.5 millions rows fact table in a data mart and I wanted to load it in PowerPivot.
I was looking for differences between normalizing dimensions in different PowerPivot tables or denormalizing them in a single table (joining all dimensions I want in a single query).
Well, everything worked well using a large dimension with 254K rows. But, at a certain point, trying to understand a particular behavior, I included a column that is an identity key of the fact table (I know, Kimball's theory don't require it, but we often use it for maintenance purposes). And, in this mode, boom, as soon as all the rows are imported, the PowerPivot generate this error:

Memory error: Allocation failure : Not enough storage is available to process this command. .
The operation has been cancelled.


Thus, my understanding is that the cardinality of attributes is very important for memory usage while importing data.
Just before throwing this error, the Excel process was using 650Mb of Commit Size (read on task manager) and there was plenty of free RAM (more than 1Gb).

At this point, my understanding is that the cardinality of attributes you are importing (especially in the fact table, when you have millions of rows) is very important in determining the size of the file and (more important) the memory required to process data.

Is there someone that can explain better what's happening, so that I can understand how to anticipate possible memory problems - and in general how to optimize data for a PowerPivot model?

Marco

---

Marco Russo

If you have high cardinality, such as an IDENTITY column, that pretty much makes the column not compressable. So let's say you have 6.5M rows, so on a 32-bit machine, this IDENTITY column will take more than 2G memory to hold the data. You only have more than 1G ram, that's why you will run into a memory allocation error.

HTH,

Chu

---

-- This posting is provided "AS IS" with no warranties, and confers no rights

Answer 8

Hi, Is this problem fixed? and what's the solution?

I encounted with the same problem recently. My OS is Windows Server 2003(32bit), DataBase: Sql Server 2008 R2.

My server has memory of 18GB. But when the memory of msmdsrv.exe reaches 1.82GB as I process a mining model, the error shows.

I guess there must be some setting, about the OS, or about the Analysis Service. But I just miss them.

Waiting for your help.

Thanks…

Answer 9

Marco, thanks for the information, I tried to click on your link about but got a 403 error.  Do you happen to have an updated link?

Answer 10

I don't understand why, but the link doesn't work, however if you copy http://sqlblog.com/blogs/marco_russo/archive/2010/01/26/memory-considerations-about-powerpivot-for-excel.aspx in the address bar manually it seems to work.

Marco

---

Marco Russo http://www.powerpivotworkshop.com http://sqlblog.com/blogs/marco_russo