3744231 - Slow performance when trying to import large volume data (more than 5000) from S/4HANA OData Service in SAP Analytics Cloud

Symptom

Slow performance when trying to import large volume data (more than 5000) from S/4HANA OData Service in SAP Analytics Cloud

Environment

SAP Analytics Cloud, Enterprise Edition
SAP S/4HANA
OData Service (API)

Reproducing the Issue

Log in to SAC tenant.
Try to create a new model/query based on either of:
- Import S/4HANA Connection
- Import OData Connection to one OData service hosting on SAP S/4HANA.
Select one entity to build query.
Keep the default value 50000 for Batch Size in Pagination Settings dialog.
- When the number of records is less than 5000, the performance is acceptable.
- However, when the number of records is more than 5000 (i.e., 6000), the performance will degrade significantly.

Cause

The batch size corresponds to the number of records retrieved in a single request as client-side pagination.
According to SAC help, in the first request, the $top parameter sets the number of records that corresponds to the batch size (here, it is $top=50000).
However, as per SAP KBA 3416745, when $top is larger than 5000, SAP Gateway will only return 5000 records, and in addition a next link with a $skiptoken at the end of the response
In this case, as the number of records return in the first request (5000) is less than the requested batch size (50000), SAC will respect the server-side pagination, so in the subsequent requests, SAC will send the next link directly without appending $top.
However, as per SAP KBA 3416745, without $top, SAP Gateway will only return 100 records.
As a result, SAC has to send totally 11 requests to get all 6000 records (The following OData calls could be observed in Gateway Tracing Tool as per SAP KBA 2960901) .
Request $top Returned Records nextLink
1st 50000 5000 $skiptoken=5000
2nd - 100 $skiptoken=5100
3th - 100 $skiptoken=5200
......
10th - 100 $skiptoken=5900
11th - 100 -

Resolution

Please ensure that the OData service should return nextLink including both $skiptoken and $top (see Server side paging enforced - part 2 in SAP Blog How to deal with OData services that return only 1... - SAP Community).

=> If you have any question to implement this, you can submit a support case to component OPU-GW-COR.

Workaround from SAC side:

Change Batch Size in Pagination Settings dialog to 5000.
In this case, as the number of records return in the first request (5000) is equal to requested batch size (5000), in the next request, SAC will still append $top=5000 to the next link and then get the remaining 1000 records. As a result, It will require 2 requests to get all 6000 records.

Request	$top	Returned Records	nextLink
1st	5000	5000	$skiptoken=5000
2nd	5000	1000	-

Workaround from SAP Gateway side (only available for S/4HANA On-Premise):

Increase either Default Paging Size (impacting the returned records without $top) or Maximum Paging Size (impacting whether SAC will append $top in the subsequent calls)
Note: as the parameters will impact all OData calls not only from SAC but also from other OData client, please only tune the two parameters based on overall performance of of S/4HANA system.
If you need more help or suggestion on what value should be set for them, you can also submit a support case to component OPU-GW-COR.

Keywords

SAP Cloud for Planning, sc4p, c4p, cforp, cloudforplanning, Cloud for Analytics, Cloud4Analytics, CloudforAnalytics, Cloud 4 Planning, BOC, SAPBusinessObjectsCloud, BusinessObjectsCloud, BOBJcloud, BOCloud., SAC, SAP AC, Cloud-Analytics, CloudAnalytics, SAPCloudAnalytics, S4, S4HANA, S/4HANA, OData, Batch, Size, Page, Paging, Pagination, client, server, 5000, 50000, 100, slow, performance, long , KBA , LOD-ANA-AQU-ODATA , Acquiring Data into SAC using an ODATA connection , LOD-ANA-AQU , Import Data Connections (Acquiring Data) , Problem

Product

SAP Analytics Cloud 1.0