This is blog is intended to improve as always the SAP Integration Suite ( our Swiss Knife in the cloud 🙂 ).
As you may know your are able to create OData APIs in SAP Integration, but only OData Version 2:
Till there there is no problem if you executing basic Querys & Read operations, but if you have business requirements that involve “complex” filters you may need to be creative to overcome the limitations of OData V2 regarding to filtering.
Let’s take one example:
One of the OData V2 limitations regarding to filtering is that it is not allowed to filter on expanded entities where the multiplicity of the target entity is “*”
Step 1 – Get metadata & create OData V2 Artifact
To see this in practice you need to create an ODATA API V2 Artifact in Integration Suite using as metadata the Northwind V2( metadata ) and paste it in your artifact:
You can open it in the Graphical Modeler( viewer ) to understand it:
Step 2 – Find an Association where multiplicity of the target entity is “*”
In the Edmx Editor you will find the following Association “FK_Order_Details_Orders”:
As you can imagine from the “Orders” entity you can expand to “Order Details” entity, but you can not filter on the expanded entity( it is a limitation of OData V2 ), it means that you can not process the following query “give me all the orders that include product A”:
Step 3 – Create bind operations
Now to deploy your API you must bind the operation “Query” of the Entities “Orders” and “Order_Details”:
Step 4 – Test it
Open postman and try to filter on the expanded entity “Order Details”:
You can get more information about this error in the log files:
Implementation of ODATA V4 APIs Artifacts in Integration Suite( feature totally missing and it’s not part of the road-map -surprisingly for me), to get more details about the benefits of OData V4 you can read this
SAP is implementing OData V4 slowly in their products, and in the meantime there are a lot of workarounds there to fix this issues of OData V2, for example in SAP Netweaver Gateway there are “exits” to handle this scenarios, or Successfactors where the OData V2 APIs are not complaint with OData V2( it’s allowed to filter in expanded entities -surprisingly for me ).
If you believe that this missing feature is relevant for you then vote this “improvement Request” LINK