Skip to Content
Technical Articles

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 /wp-content/uploads/2016/05/image018_964944.pngbutton.

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.

 

2 Comments
You must be Logged on to comment or reply to a post.
  • Hi Lakshmi,

    I am wondering why you are using AMDP and why you are not using the Mapped Data Source approach.

    AMDP is only available as of 7.40 SP5 and this is also the release where you can leverage CDS views and the SADL framework for a generic read access.

    That means you would create a CDS view on top of table ACDOCA and than you would create a SEGW project using this approach:

    Mapping a Data Source - SAP Help Portal

    1. Create an entity set based on DDIC import using the structure that has been created alongside with the CDS view
    2. Map the entity

    This way not only $skip and $top would be supported but all kinds of $filter, $sort, $orderby, ... would be handled by the SADL framework out of the box.

    One side remark with regards to your implementation.

    You should implement the entity set specific method JOURNALENTRY_GET_ENTITYSET that has been generated by SEGW instead of redefining the generic method GET_ENTITYSET method.

    When you nevertheless decide to use the generic method you would have to call the implementation of the super method of the _DPC class and you would have to add a check that would make sure you are calling this method only for the entity set JournalEntrySet.

    Best regards,

    Andre

     

    • Hi Andre,

      Thank you for your time and comments.

      Yes, that’s also a possible approach leveraging the SADL framework for pagination. But in actual client requirement, we have additional requirement like mapping and read only last 24 hrs of data. So it was designed to use the AMDP method. For this blog purpose, I have created the gateway project to made it simple to understand the concept of pagination only.

      but I will implement the pagination by leveraging the CDS view with SADL framework and publish it.

      Regarding the get entity set method,

      I remember first we leveraged the Get entity set method of the particular entity set but we encountered some limitation and I couldn’t recall. If I am correct we cannot use the $batch or something. If required I can provide the details why we were not using direct get entity set method.