Post reply

out of memory error from excel

  • 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

  • November 6, 2014 at 2:35 am

    #1756904

    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

  • November 6, 2014 at 2:43 am

    #1756906

    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

  • November 6, 2014 at 4:29 am

    #1756938

    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

  • November 7, 2014 at 9:02 am

    #1757237

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

    reports seem much faster now. 😎

    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