Client Side Pagination in OData services

This blog post will provide the steps to implement the pagination in OData which helps to handle the large volume of data using OData GET call.

Introduction:

Before we learn about implementing pagination in OData service, i would like to discuss about the reason why we need to use pagination.

Scenario: We are using the OData API GET call to get the data from ACDOCA table. So due to large volume of data where OData cannot handle, we encountered the below short dump.

To resolve the issue and successfully get the volume of data using the same GET call, we will leverage the pagination in OData service.

Agenda:

• Implementation of pagination in OData
• Testing from SAP Gateway
• OSS note 2601445 – STRING_SIZE_TOO_LARGE
• Recommendation

1. Implementation of pagination in OData

• Creating a basic OData service

We will perform the following steps:

• Use the SAP Gateway Service Builder (SEGW) to create a new project
• Create the Z structure including the fields required from ACDOCA.
• Import a DDIC structure created above to create the data model
• Generate, register and test the OData service using the Gateway Client
• Perform a first implementation of the GET_ENTITYSET method

      Create a new gateway project by running transaction SEGW.

Press Continue and save the project.

          Import a DDIC structure.

          Right-click on Data Model and select Import –> DDIC Structure.

In the first step of the wizard provide: In the field Name, enter JournalEntry
In the field ABAP Structure, enter ZSACDOCA.
Click Next.

In the second step of the wizard provide: Select all the required fields and click on next.

Now, select the key and click on Finish.

Expand the entity type and validate the properties.

Press Save and Check Project Consistency button.

If no errors were found ,Press the Generate Runtime Objects button.

Note: Using the Generate Runtime Objects button automatically saves the project.

Leave all values as defaulted and press Enter.

Expand the node Service Maintenance and right-click on GW_HUB and select Register

In the Add Service popup leave all values as defaulted and press the Local Object button to                assign the artifacts to the $tmp package. Then press Enter to continue.

Double-click on the GW_HUB entry under Service Maintenance. Verify that the registration               status on the right-hand side is showing a green traffic light.

Go to -> Run time Artifacts and click on the DPC_EXT method

In the DPC_EXT class, implement the GET_ENTITYSET method by clicking on the Redefine             Method button.

Implement AMDP to get the data:

Go to Eclipse  and create the AMDP class which helps to filter the data at lower level.

Now, in the GET_ENTITYSET, call the AMDP method

Note:

The OData standard supports paging on “Query” aka “retrieveEntitySet” operation via the URL query string parameters $top=x and $skip=y. Where $skip=y means to skip the first y entries, and $top=x means to grab the next x rows proceeding the skipped rows. An example of how this would look on a “Query” URL is this:

/sap/opu/odata/SAP/ZTEST_PAGINATION_SRV_01/JournalEntrySet?$skip=2&$top=2

2. Testing from SAP Gateway

Go to transaction /IWFND/GW_CLIENT.

Note: If we have around 100K records, there will be two calls required from source system to SAP as mentioned below.

/sap/opu/odata/SAP/ZTEST_PAGINATION_SRV_01/JournalEntrySet?$skip=0&$top=50000

/sap/opu/odata/SAP/ZTEST_PAGINATION_SRV_01/JournalEntrySet?$skip=50000&$top=50000

3. OSS note 2601445 – STRING_SIZE_TOO_LARGE

OSS Note stating the volume issue and resolution provided to implement pagination using $skip and $top

​2601445 – Requesting large amount of data through the Gateway is slow and might end up with dumps: STRING_SIZE_TOO_LARGE, TSV_TNEW_PAGE_ALLOC_FAILED – SAP ONE Support Launchpad

Recommendation:

I have implemented the AMDP to filter the data at the data base level along with additional requirements like mapping.

So if the requirement is just to pull the data from data base table, please refer the below blog.

https://blogs.sap.com/2021/04/07/creating-odata-api-based-on-a-mapped-data-source/?update=publish

Conclusion:

By implementing pagination in OData service, we can transfer the large volume of data from SAP.