Report runs in Bex Analyzer but not in Analysis for Office

[Former Member]

We have several reports that retrieve a large amount of data that can run in Bex Analyzer 7.0 but will not run in Analysis for Office 1.4.8 or 1.4.10

  we get this error message:   ‘Size limit of result set exceeded’

Does anyone know what to do to get this to work?

    We have set the following parameters:

BW version 7.30 SP7

AFO version 1.4.10

In BW - BICS_DA_RESULT_SET_LIMIT_MAX set to 5,000,000

In AFO -  HKEY_Current_user "resultsetsizelimit = "-1"

     In RSRT we see this particular query returns 4.3 million cells ( under the 5 million set)

The query in SM04 shows the memory getting as high as 6GB

Each user is enable to use 20GB of memory

[filipe_zeuch · Accepted solution]

Hi Patty,

In addition to my previous reply, if you believe that Analysis for Office is not using the value that you have defined in BICS_DA_RESULT_SET_LIMIT_MAX, you can do the following:

1. Reproduce the scenario in Analysis for Office recording a backend trace;
2. Enter this trace in your RSTT transaction;
3. Click on display;
4. Click on program module BICS_PROV_GET_RESULT_SET and hit "Parameters";
5. Check the tag <I_MAX_DATA_CELLS> to make sure Analysis for Office is using the 5 million value that you defined.

If you find the 5 million value on this tag, you can do the following to know how many data cells are being retrieved from your backend:

1. Run transaction SE37, BICS_PROV_GET_RESULT_SET
2. Set an external break point here:
   ----------------------
     IF e_state = cl_rsbolap_qv_result_set=>c_state_data.
   *   Result set size
       e_n_rows    = l_r_rs_rows_axis->n_sx_axis-setxx_size.
       e_n_columns = l_r_rs_columns_axis->n_sx_axis-setxx_size.
       l_n_data_cells = e_n_columns * e_n_rows.
     ENDIF.
   
   IF i_max_data_cells = 0 OR i_max_data_cells >= l_n_data_cells.
   ----------------------
3. Reproduce the scenario in AO
4. Check the value in >> l_n_data_cells
   This value is the number of data cells that Analysis for Office is trying to retrieve from your backend.

Regards,
Filipe Zeuch

[filipe_zeuch]

Hi Patty,

I strongly recommend that you check the link mentioned by Martin.

The message that you are getting is because your data source is trying to retrieve more data cells than the limit used by Analysis for Office.
Analysis for Office uses a default value of 500K data cells for this limit if you do not have the registry setting "ResultSetSizeLimit".
If you do have this setting under the path below:
>> HKEY_CURRENT_USER\Software\SAP\AdvancedAnalysis\Settings\DataSource
Then you may want to check which value you have set for the ResultSetSizeLimit parameter.
If you set the value "-1", Analysis for Office will consider the value defined in your backend for the object BICS_DA_RESULT_SET_LIMIT_MAX of RSADMIN table.
If you set a value >= 0, Analysis for Office will consider the value defined as the limit for the number of data cells that your data source can retrieve.

This setting is just a safety belt to avoid out of memory exception (like mentioned by Martin). Depending on the value that you set for this limit (in case it is too big) Analysis for Office will try to retrieve the data cells until show the results or raise an out of memory exception if you reach the maximum memory that the Excel process can allocate.

The wiki page contains the details about these limitations and also shows how to check the exact value that your data source is trying to retrieve from the backend.

Let me know if any questions or concerns about this topic.

Regards,
Filipe Zeuch

[Former Member]

Hi Patty,

I'm not sure if you know that Wiki post? It covers the topic very well:

Analysis for Office Result Limit and Memory Consumption - Business Intelligence (BusinessObjects) - ...

Your Problem here is actually not the size limit of the cells but it is the limit of the Main Memory that Excel is allowed to allocate. In 32bit Version it's ~ 1,2 GB. For 64 Bit I don't know the exact number.

My personal opinion is that in Versions < 1.4.5 you saw a specific AO error message window with "System.OutofMemoryException" ... I cannot remember having seen it after 1.4.7. Instead it looks like the "size limit" error message replaced that, so that the high data volume doesn't even get transferred to the client...

(I would appreciate if anyone could confirm that)

Best regards,

Martin