Really in deep trouble Memory Error allocation Failure

[masplin]

Hi

I have been running an Excel 2010 model for 3 years and turned into one of those projects that has just spiraled out of control. It was only designed as a temporary solution while we move to a new software system. that system is 2 years late and due to go live next month. In order to migrate the data form my model is critical. This month it collapsed. I know it is badly written, but if anyone can help i'd really appreciate.

What has got me completely baffled is I update the model each month with new data and during that month might add some new measures. so at end Aug I took the Jul model and update with new data and it worked no problem. If i now reload that July model and do the same it throws me the "Insufficient quota to complete" Error. This makes no sense as the same data in the same model worked previously. I have 64 bit excel on a 32GB RAM machine. I cut all the related tables down to a few hundred rows to cut down the amount of calculations, but makes no difference.

Currently I'm using a linked table so I thought I would rebuild the man table using power query but got the same result even when I cut the data set down from 4000,000 rows to 10,000 and disabled all the relationships and deleted all the calculated columns!!!! the same query loads fine in a blank workbook, but not the one with the existing model.

I even tried the same model on another computer with the same result so can't be the PP or PQ installation.

I am now at my wits end because this £1mio project is now going to stall because we can't extract the data form the old system!!!! I've resisted rewritting in in 2016 because you always think it will limp over the line. That would be a massive exercise as over 300 measures and 100 calculated columns in the whole workbook. That seems to be nuclear option.

I just can't understand how it can be working one day and not the next with not change in anything. If anyone has any suggestions on things to try before a complete rebuild I'd be very grateful

Mike

 

[ImkeF]

Ouch, that sounds painful. Still in trouble here?

Have no idea what the reason might be, but I would try the following:

1) Transfer to Power BI desktop and see if that engine can handle it better
2) Transplant just PP model to new Excel-file (in case one of the other elements in your Excel-files are damaged)

 

[akice]

a quick google found this article: What happens when a SSAS Tabular model exceeds memory? | James Serra's Blog . Although the article refers to a Tabular DB and not power pivot as per se, by my understanding they both use the same xvelocity engine. To sum up the article it is saying you ran out of physical RAM, and by default the Power Pivot model doesn't page memory to disk. The article is a few years old though so not sure if still applicable....hope this helps.

 

[masplin]

Well i found the solution. I was having to reinstall everything as machine had blown up. i pretty sure i installed visual studio 2010 fist then powerpivot then power query. Any way I uninstalled everything and reinstalled and it worked. So presumably something was causing a conflict which resulted in it Erroring and chewing up the memory. Seems like that is the correct first step whenever anything you can't explain happens!!!

Thanks for your responses

Mike