Technical Articles
Suggestion to reference the result of requests in subsequent requests of an OData $batch query
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 (CAP) App, 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/pingtakes 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:
- 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
- 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
- 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)
- 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 Ethan Jewett 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.
Hi Gregor - Totally agree, since this will also help us in "keeping the core clean" by allowing to replace tightly coupled selects in on-premise to SbS - thereby making more custom report programs as relevant candidates to move to SbS..
Thanks & Regards
Srinivas
An interesting proposal - I could see this being very useful for us in our OData services integrations. To be honest I was not even aware of the Content-ID syntax in Insert - we have been handling situations like that with chained, asynchronous API calls, like you show for the current GET setup.
One thing I don't quite understand is how the "in" operator would work here? Does this assume all involved entities have a single key? or would this only be valid for associations?
Hi Matthew,
thank you for your comment. I would hope that the any operator can be used. In my suggestion to use the result of a previous request I expected a single key. Entities with multiple keys bing another callenge.
Best regards
Gregor
Hi Gregor,
adding support to what you have proposed would require changes to
a) the OData V2 protocol which is owned by Microsoft and which has not been changed in the past 9 years.
b) the SAP Gateway OData V2 framework (if SAP would decide to add new SAP proprietary changes its OData V2 framework implementation)
b) all existing OData V2 API's provided by SAP S/4 HANA since such a feature would not be supported "out of the box" by simply changing the OData V2 framework.
Your requirement would be supported if the API would support OData V4 (any and all).
The current guidance in SAP S/4 HANA development is the following.
So if there is the need to have OData V4 support for an existing OData V2 API this has to be addressed to the respective product group.
For feedback to existing API's or to post a requirement for new API's you can use the customer influence process:
SAP Innovation Management
Any additional functionality on top of existing API's will be supported via the use of SAP Graph.
SAP Graph
SAP Graph is the new easy-to-use API for the data of the Intelligent Enterprise from SAP. It is available as a private beta shipment. However, the registration for the beta phase is currently closed, but you can sign up for the newsletter and stay tuned for more exciting news from SAP Graph!
Best regards,
Andre