Powerpivot keeps crashing - is 64 bit the answer?

[mrclarence]

Hi Excel buddies

We have a company wide problem - our PowerPivots which hold 300,000 rows or so of data (around 100mb in file size) keep crashing and will not allow us to refresh and update data. The data is from an excel file so really basic stuff.

If I watch the task manager for Excel whenI refresh a table it will go up to around 1gb of RAM though i know that 32 bit is restricted to 2gb of system RAM.

Given this problem is across a number of laptops, all on 32 bit Windows & Excel, could a 64 bit machine and PP help us? It's not like our files are that big, we have tried everything from trimming down calculated fields, only pulling through essential fields, saving as a binary file. PP has prompted me about 32 bit during refreshes, saying I need 64 bit.

Any help would be much appreciated!

 

[gazpage]

100MB and its only 300k rows? What else is in the spreadsheet? Or is it 100s of columns? I have c. 3m rows and 40 cols I my current workbook and it 42MB?

 

[mrclarence]

It has 8-9 lookup tables, and 3 data tables (budget, actuals and our revised forecast). Each of these data tables has around 150k rows, so actually around 450k if I can get it to work.

I do have quite a few tabs within the sheet (maybe 15 or so). The file has around 200 calculated fields doing various things: ratios, mix models etc.

If I saved as an XLSB it is only around 25mb or so, loooking up to data files around 40mb so it isn't overly heavy on memory. Given other people are running millions of rows 150k should be nothing!

 

[mrclarence]

Of the data table, there are around 100 columns in the data set though I'm only pulling through the essential columns (around 20)

 

[gazpage]

And the dataset is in a different file? All the data is in Power Pivot only and not duplicated in actual excel tab?

 

[mrclarence]

And the dataset is in a different file? All the data is in Power Pivot only and not duplicated in actual excel tab?

I've experimented with one large data source file (containing tabs for the dataset and lookup tables) and separating the data sets into their individual datasets i.e. one for actuals, one for budget & one for previous forecast.

I've made the file as lean as possible so I'm not pulling through columns of data I do not need or ones with lookups. Also, I've removed calculated fields I do not need however they do not make a huge difference to the size of the file (though the number of calculated fields could be impacting performance?)

I'm at a bit of a loss, I think it's the version of excel (32 bit) and the laptops - though we do have 8gb of RAM so excel and the other applications should be able to run comfortably off that.

 

[gazpage]

Sounds like you are doing this already, but just to check as I can't understand why your file is double the size of the one I am currently working on but I have maybe 5x as much data.

Is your process like this?

1. Data is in an excel file
2. A completely separate file connects to the first file using Power Query
3. On the Load To step you have Connection Only and Load To Data Model selected, ie data is not loaded to an excel table.

Otherwise to answer your original question, you are limited to 2GB memory if you are on 32bit Excel and Windows. I understand about a third of that is available for PowerPivots; bear in mind that the file size is the compressed value. Using 64 bit Excel will increase the RAM available. NB that if even just the 64bit windows would help if you are on the latest Excel 2013 or 2016 patch (https://support.microsoft.com/en-us/help/3160741/large-address-aware-capability-change-for-excel).

 

[mrclarence]

Hi Gaz

Thanks for the response on the 64 bit upgrade, I have asked IT to try that - will feedback on here results.

My process is:
1) Update data source excel spreadsheet which contains my data and lookup tables
2) Open powerpivot model and refresh lookup tables if any changes (e.g. new products)
3) Refresh data tables
4) Use model!


Should be that simple right!

 

[gazpage]

So your source data and the power pivot data model are in the same file? That's going to be the core of your problem there. All of your data is in the file twice, and once in uncompressed form.

Split it up into one file with your data and then have a separate file that uses Power Query to pull in the data from the first. Make sure to choose Close and Load To and select Connection Only and Load to Data Model. Would expect this to solve your issue.

 

[mrclarence]

No two separate files. The source file is just to look up to