You want to search for valid values for input-ready cells in an Analysis for Office (AO) planning query, but you get the below exception instead of any result:
The trace of AO (see SAP note 2129389) contains the following entries:
Invalid program status\: RFC call BICS_PROV_GET_MEMBERS returned an exception: RFC_ERROR_SYSTEM_FAILURE: The memory request for <X> bytes could not be complied with. - SYSTEM_NO_ROLL
In the back-end BW system, you can see a memory related dump, like the below:
SAP BusinessObjects Analysis, edition for Microsoft Office
Reproducing the Issue
- Insert an input-ready query in Analysis for Office;
- Select any input-ready cell and open the Value Help (Member Selector);
- After a while, get an exception in Analysis for Office and a back-end memory dump (SYSTEM_NO_ROLL or TSV_TNEW_PAGE_ALLOC_FAILED).
Backend memory dump due to too many possible combinations of member values when using Member Access Mode "P" ("PLANNING"). For Access Mode P the valid combinations with respect of the characteristic relationships (CRs) are generated as the basis of the value help. The used filter for the value help is the intersection of the global query filter (for the relevant characteristics) and the already entered values in the new line. The inner join of these two lists might be simply too larte to be handled.
This is not an AO program error, but a possible expected behavior depending on the access mode used in AO and the amount of member values combinations.
According to SAP Note 2180059, if AO setting InputReadyCellsValueHelpMemberAccessMode is not set (empty by default), AO will always try to use Member Access Mode "P" ("PLANNING" = Only values for characteristic combination).
When using this Member Access Mode, the backend system has to generate all the possible combinations of member values of the current line based on the characteristic relationships to offer a list of available members in the current context. Depending on the scenario, this may generate so many possible combinations that the process will run out of available memory on your backend.
There are two possible solutions to avoid the large number of valid CRs combinations:
- If using the Member Access Mode "P" is not a requirement, you can simply change the it in AO according to KBA 2636570.
- If you really want to keep the Member Access Mode as "P", you'll have to model the CRs in the back-end in a way that either only relatively small number of valid combinations exist or use a filter to constrain the creation of the combination.
2180059 - AO: Planning - Value Help for input-ready cells always opens with Member Access Mode P
2636570 - How to change the access mode of Value Help in input-ready cells
Analysis Office, AO, Access Mode, Value Help, F4 Help, Planning, Member Selector, Member Access Mode, Query Designer, New line, input-ready, P Mode , KBA , BI-RA-AO-XLA , Excel Addin , How To