0

The table I am trying to refresh using the Excel 2010 PowerPivot add on is currently 2,670,634 rows, and approximately 473 MB when I export the query results from SQL server into a CSV file format. The operating system is in 64bit but my installed Excel + Powerpivot add on are both 32bit.

I get the Memory error: Allocation failure when I refresh my PowerPivot to retrieve the entire table. At the last PowerPivot refresh, I was able to get 2,153,464 rows into PowerPivot. But today I am unable to refresh and always get the memory error. I am a bit confused by this. I thought I have yet to exceed the max row limit of PowerPivot 2010? I thought that the row limit was 1,999,999,997. What can I do to make it work in 32bit Excel?

Thank you in advance for your tips.

mwigdahl
  • 16,268
  • 7
  • 50
  • 64
Pho
  • 98
  • 1
  • 9

1 Answers1

1

PowerPivot on 32bit can be a memory hog, requiring about a 1 GB or so of memory. So given how much memory you have available, you can easily run into memory allocation issues with PowerPivot. If you can't use 64bit version, then for starters, simplest thing is to continue to filter the data - reduce the number of rows, reduce the number of columns. After that, you'll have to look at the calculations and such being done, the more rows/data you have, the more expensive it can get.

Greg
  • 3,861
  • 3
  • 23
  • 58
  • I have 4 Gb RAM with a i5-2500 @ 3.1GHz CPU. Shouldn't that be more than sufficient? I have problems reducing the rows, i have already reduced the columns to what i need. The data must be at least a quarter (3 months), and i have 2.6 Mil rows in that period. What else can i try besides 64bit? Would 64bit really resolve this problem? – Pho Oct 06 '15 at 08:38
  • 4 GB would be great if that was all free memory, but your OS and other software you have running in the background is probably consuming more than half of that. once you past the 3 GB mark, then most likely it's the cause of your memory issue. Do an internet search of 32 bit vs 64 bit powerpivot, you'll find plenty of articles detailing the differences between the two. – Greg Oct 06 '15 at 14:19