Analysis Office Size Limit of result set exceeded with small query

It has been a quite while since the last post. But with a go live and a reorganization of my current project I hadn't time to look into some topic. But here is a new kind of interessting bug. We had an open task from a user of the controlling department, that a query display the message:

"Size Limit of result set exceeded."

That's an odd thing, because the query has only six columns and 110 rows and when the user dragged the calendar month into the columns it should have 12 columns for the actual year and 110 rows. Which is defintily lower than the limit of Analysis Office, which is 500.000 cells.

Query with a simple layout
Query with a simple layout

So I tested the query in the BW backend with the transaction rsrt. It worked perfectly.

Query with calendar month in the columns
Query with calendar month in the columns

So the query is working correctly. But why Analysis Office throw me the error? It appears on different computers and the rsadmin parameter was default by 500.000 cells. After a little search I found the problem. The query dimensions were all set to show Vales from Master Data instead of Posted Values and the query has the option to suppress zeros in columns and rows. So when I deselect the suppress zero option, the query now shows around 83.000 rows and 6 columns which are 498.000 cells and this is short below the limit of 500.000 cells. 

 

Now we change the option from Show Values of Master Data to Posted Value and the query worked for the controlling deparment.The only question which is not answer is why does it work in RSRT with the same option it doesn't work in Analysis Office? Does anyone have an idea?

author.


I am Tobias, I write this blog since 2014, you can find me on twitter and youtube. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.

Analysis Office - The Comprehensive Guide 4th edit
Analysis Office - The Comprehensive Guide is a pdf book about SAP BusinessObjects Analysis Office. It is based on Analysis Office 2.7 and contains 299 Pages.
59,99 €
Analysis Office - The Comprehensive Guide 3rd edit
Analysis Office - The Comprehensive Guide is a pdf book about SAP BusinessObjects Analysis Office. It is based on Analysis Office 2.6 and contains 272 Pages.
39,99 €
Analysis Office - The Comprehensive Guide 2nd edit
Analysis Office - The Comprehensive Guide is a pdf book about SAP BusinessObjects Analysis Office. It is based on Analysis Office 2.4 and contains 243 pages.
29,99 €
Analysis Office - The Comprehensive Guide
Analysis Office - The Comprehensive Guide is a pdf book about SAP BusinessObjects Analysis Office. It is based on Analysis Office 2.3 and contains 227 Pages.
9,99 €

Write a comment

Comments: 4
  • #1

    Guy Lamoureux (Wednesday, 10 July 2019 20:08)

    My guess : there is no .NET involved in RSRT (?)

  • #2

    Tobias (Thursday, 11 July 2019 12:56)

    Hi,

    I think this must be a problem with the BICS Connection, because the limit is the BICS_DA_RESULT_SET_LIMIT_DEF of the table rsadmin, but it worked in rsrt with the ABAP BICS Query Display. See here https://twitter.com/reyemsaibot/status/1149270699510554625

  • #3

    Aaron Benner (Thursday, 11 July 2019 22:30)

    I have seen this before. Are you using IP functions to add planning data and thus need the "all values from master data" option?
    Internally what is happening is that the tuple of all combinations of master data across all the dimensions is being calculated (although it is not displayed in the query) and is running up into the 500K limit. I believe it is more of a in-memory-calculation limit than the number of cells actually displayed.
    Separate thought - have you tried the 64 bit version of Excel / AO? It lets you bring in much more data and may resolve the issue, if it is possible to install in your environment politically.

  • #4

    Tobias (Friday, 12 July 2019 10:59)

    Hi,

    I haven't tired the 64Bit version. But this is hard in a bigger company to accomplish. But I will give it a try when I have time to test it and give feedback.