Suggestion to reference the result of requests in subsequent requests of an OData $batch query

[gregorw]

Fiber cables (Credits: titanium22)

I've attended the SAP Tech Night Belgium – First edition on 29. April 2020 where andre.fischer gave an introduction into the new SAP ABAP Platform 1909 on SAP HANA 2.0 Developer Edition. After that there where I nice discussion on various technical topics. There I asked for feedback for the idea that I want to describe here in more detail.

I'm suggesting a to enhance SAP Gateway, so it allows referencing the result of a request in a subsequent requests of an OData $batch query. In other words, the result of a first query in the $batch requests should be provided as a filter to the next request.

Constrains of a Side-by-Side Extension

As described in Combine Power Automate, Microsoft Teams Approvals and an SAP Cloud Application Programming Model (CA..., I’m involved in an SAP S/4HANA Side-by-Side Extension Product development. The product should be implemented without installing an ABAP AddOn to the SAP S/4HANA on Premise system. So, we need to constrain our self to use only the released APIs.

Another limitation is the latency between the SAP BTP Cloud Foundry environment and the SAP S/4HANA on Premise system. Even a simple ping from SAP Business Application Studio to the Backend that I've measured with the command:

time curl http://s4h.https.devstudio:9999/sap/public/ping

takes 0.211 seconds. So, when I need multiple requests to an API to get to the intended result this adds up quickly.

Scenario

Let's take the following sample scenario:

The app should allow the user to filter customers by Country, City and Sales Organization. The result should be displayed in a table. For that we find the API API_BUSINESS_PARTNER. If you want to try on your own, you have to use the SAP S/4HANA Cloud API. Only there a Sandbox is connected. When the user now searches for Country = DE and SalesOrganization = 1010 we must issue the following requests:

1. The request the BusinessPartners that are in Germany we call: /sap/opu/odata/sap/API_BUSINESS_PARTNER/A_BusinessPartnerAddress?$filter=Country eq 'DE'&$select=BusinessPartner


2. For the Customers assigned to the Sales Organization 1010 we use:
   
   
   

   /sap/opu/odata/sap/API_BUSINESS_PARTNER/A_CustomerSalesArea?$filter=SalesOrganization eq '1010'&$select=Customer

   
   


3. Now we have to find which Business Partners match the Customers of list 2 (assuming that Customer ID's and Business Partner IDs are the same)


4. Then we can read all the details with:
   
   
   

   /sap/opu/odata/sap/API_BUSINESS_PARTNER/A_BusinessPartner?$inlinecount=allpages&$filter=BusinessPartner eq '9980000043' or BusinessPartner eq '9980000043'&$expand=to_BusinessPartnerAddress,to_Customer

   
   

And more than a second is gone. You might ask why don't you use a query like:

/sap/opu/odata/sap/API_BUSINESS_PARTNER/A_BusinessPartner

  ?$expand=to_BusinessPartnerAddress,to_Customer,to_Customer/to_CustomerSalesArea

  &$filter=to_BusinessPartnerAddress/Country eq 'DE' 

   and to_Customer/to_CustomerSalesArea/SalesOrganization eq '1010'

Because for that query you will receive the error message:

Left hand expression of memberaccess operator 

has wrong cardinality (to many not allowed)

Update (2020-05-03)

Thanks to the input from db48526ccb71414183a64aa46e31e784 on Twitter:

I assume this is an OData 2.0 API? 4.0 APIs (and the UI5 v4 models) should support the “ANY”/“ALL” filter operator to filter based on dependent sub items. In other words, I think your first approach is probably the better one to enhance towards as it’s already standard in 4.0.

I'm adding the following suggestion:

As the released OData V2 API's for S/4HANA (Cloud) will not be migrated to OData V4 I would suggest to enhance SAP Gateway to Support the “ANY”/“ALL” filter operator. So the request would look like that:

/sap/opu/odata/sap/API_BUSINESS_PARTNER/A_BusinessPartner

  ?$inlinecount=allpages

  &$expand=to_BusinessPartnerAddress,to_Customer,to_Customer/to_CustomerSalesArea

  &$filter=to_BusinessPartnerAddress/any(d:d/Country eq 'DE') 

   and to_Customer/to_CustomerSalesArea/any(d:d/SalesOrganization eq '1010')

Possible Solution

OData allows to combine requests in a $batch request. What I suggest is similar to what is already possible in Insert requests. In the specification Referencing New Entities in a Change Set you find the description how a step can define a Content-ID header. This ID can be used in subsequent steps by e.g. $1.

Here is my proposal to get the data that needed before 3 separate calls in just one:

POST /sap/opu/odata/sap/API_BUSINESS_PARTNER/$batch HTTP/1.1 

Host: host 

Content-Type: multipart/mixed; boundary=batch_36522ad7-fc75-4b56-8c71-56071383e77b 



--batch_36522ad7-fc75-4b56-8c71-56071383e77b

Content-Type: application/http 

Content-Transfer-Encoding:binary

Content-ID: 1



GET A_BusinessPartnerAddress?$filter=Country eq 'DE'&$select=BusinessPartner HTTP/1.1

Host: host



--batch_36522ad7-fc75-4b56-8c71-56071383e77b

Content-Type: application/http 

Content-Transfer-Encoding:binary

Content-ID: 2



GET A_CustomerSalesArea?$filter=SalesOrganization eq '1010' and Customer in ($1)&$select=Customer HTTP/1.1 

Host: host 



--batch_36522ad7-fc75-4b56-8c71-56071383e77b

Content-Type: application/http 

Content-Transfer-Encoding:binary 

Content-ID: 3



GET A_BusinessPartner?$inlinecount=allpages&$filter=BusinessPartner in ($2)&$expand=to_BusinessPartnerAddress,to_Customer HTTP/1.1 

Host: host 



--batch_36522ad7-fc75-4b56-8c71-56071383e77b--

Another nice addition would be an option to specify what responses from the batch request should actually be returned to the client.

Looking forward for your input.