Memory Pressure Issues with PowerPivot

[MD610]

I've started a new PowerPivot project using a single table that has about 340,000 rows of data and 20 columns of data consisting of mostly dates and abbreviated codes (4 to 5 letters or numbers). My Pivot is all set up and I have no trouble using slicers to view the data. However, if I try to remove all filters and view all the data I continually get the error below:

Data could not be retrieved from the external data source.
Error message returned by the external data source:

The operation has been cancelled due to memory pressure.

I have already viewed more data from larger source files without such an error. I'm assuming the external data source being referenced is the PowerPivot data and not the actual SQL Server data that was imported. I can view this data with a lot of other program with no issue at all on the same machine. Is this issue likely related more to PowerPivot itself or the 4GB of RAM that my computer currently has? The saved PowerPivot file is only 35MB which is much smaller than some I have created that didn't have this issue.

Is this a common issue? Does anyone have any tips or suggestions? I could get upgraded RAM, it will just be a drawn out process to wait for it to be ordered and installed. Since this hasn't happened on other projects I was hoping for an alternative solution since there is no guarantees that 8GB will fix the issue.

 

[MD610]

Haven't found a solution to this exactly in PowerPivot, but I tried doing things the "old fashioned" way by just doing a normal excel external data connection to my source data.

Then once I had the source data as a table in an excel worksheet, I created a normal excel pivot table from it. I created the same slicers I was using in PowerPivot which is slightly more tedious in regular excel.

The new file done without PowerPivot on the exact same data set is 3-4 times larger than the PowerPivot version but it gives me no errors or memory pressure warnings no matter how I slice and dice it.

 

[powerpivotpro]

Is the resultant pivot a 340,000 row pivot?

PowerPivot is amazing at crunching large data sets - I have single workbooks with 300 Million rows in them that work great, even when producing 10,000-row pivots.

I don't suppose this workbook is one I can take a look at?

 

[barnettjacob]

If you are using the 32 bit version of Excel my guess is that going to 8gb will make absolutely no difference!

I've got 8gb on my laptop and when I was using 32bit I regularly came up against 'memory' issues despite the fact that the instance of excel i was working in never went above 1gb of RAM usage.

Now I've switched to 64 bit, I can literally do anything I want as excel will now happily use 3gb+ of RAM.

The only downside is that virtually no add-ins work on 64 bit.

 

[MD610]

I do have the 64 bit version of Excel installed. I also have the 32 bit version on a laptop in case I need something not available for the 64 bit version. I rarely use the laptop otherwise though. I will just go ahead and put in a request for more RAM. Either way it might help for this and/or other projects I am working on.

Rob,

I would love nothing more than to show you the file and get your opinion! Unfortunately, it contains a lot of confidential data. That is the issue here I believe...the size of the pivot. The source data has 340,000 rows. The pivot table will probably never realistically need to show everything because the whole point of a pivot is to summarize everything into more managable views. In real world use, I don't think anyone would ever use this pivot without applying one or more filters. However, this particular report is currently in "development" so as I was demoing it to the eventual end users, of course someone asked if they could view the entire data set at once in the pivot if they wanted. When I cleared all the filters, that is when the memory pressure issue occurred. In actual use, I can't imagine anyone needing to see all 340,000 rows at once. They wouldn't even know where to begin. If they really need to see it all at once they could always view the source data directly. I think I just need to communicate this with them.

Thanks!

 

[buclao]

have you tried hiding some of the fields to the client tools? I can tell you for sure that it actually works. I had the same issue but with a pivot of almost 500K rows and I had like 9 tables imported, I started hiding the Id's and stuff like that were not needed in the client tools and is working completely fine.

PS: I'm using PPv2.0 with a 32bit Excel with 4GB of RAM on a W7 OS

 

[MD610]

On this particular project there aren't many fields that aren't being used for pivot fields or slicers. However, there are 3 that aren't being used so I will try hiding those to see what happens. Thanks for the suggestion!