out of memory error from excel

Question

out of memory error from excel

Ian C0ckcroft

Hall of Fame

Points: 3373
More actions
November 5, 2014 at 1:01 pm

#148768

Hi guys,
Having a bit of an issue at a client.
When pulling a report in excel, as long as the date range is not too big, it runs. Very slow though.
When I select more than 3 or 4 years’ worth, I get an error.
They need to run the report for dates since inception. Maybe 10 or more years.
It is using a tabular model as the source.
This is the report with just 2 years selected(works):
This is the same report with more years selected:
ATTACHMENT 2
The tabula model memory settings
ATTACHMENT 3
I have played around with the memory setting with no joy.
Any idea why its saying memory is full but clearly it has lots left?
Any idea how to fix it?
Thanks for the help
Ian
Ian Cockcroft
MCITP BI Specialist

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

PB_BI SSCoach Points: 17461 More actions · Answer 1

On the surface, I would say that your issue is that a tabular model is being used where a multidimensional one should be used (10 years of history is potentially a lot of data). With that in mind I would run a trace (and monitor perfmon) on the analysis server whilst the query is running to see the memory consumption. You could also use SSAS DMVs to see what's going on (http://msdn.microsoft.com/en-us/library/hh230820.aspx).

If the issue is not with the memory of the machine running the query or the server then it may be a network one.

As an alternative to all of the above.....depending on how much data is being pulled in to excel for this "report" it might be better to create an SSRS report for the purpose. This way you could relieve the end user memory requirements and if latency isn't an issue (I'm guessing not due to the process-then-query paradigm of SSAS) you could pre-cache the report, say, daily and run it out of hours.

I'm on LinkedIn

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 2

Also, which version of Excel are you using? 32-bit or 64-bit?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Ian C0ckcroft Hall of Fame Points: 3373 More actions · Answer 3

hI GUYS, THANKS FOR THE HELP.

Its a 32 bit environment.

Another suggestion I got was to run the SQL service packs.

Will let you know.

Ian Cockcroft
MCITP BI Specialist

Ian C0ckcroft Hall of Fame Points: 3373 More actions · Answer 4

Thanks for the help guys. we installed a64bit excel and that worked.

reports seem much faster now. 😎

Ian Cockcroft
MCITP BI Specialist