OData Service in SAP Cloud Platform Integration with $expand and DeepInsert

[saranya_baskaran2]

This blog post covers how to expose an OData Service in SAP Cloud Platform Integration with $expand and deepInsert Scenario.

Refer OData Service for Beginners

OData Sender Behavior

Below is a sample integration flow with OData Sender.

1. OData sender converts the incoming OData Request payload (atom+xml / json) ① to simple XML. ②


2. For OData Operations which has response body (create, read), the final step in the IFlow has to be a simple XML which represents the Entities defined in OData sender Edmx.  ③


3. This final XML is converted back to atom+xml / json to the client. ④

Contents

1. 
   

   Create OData Sender Artifact

   
   


2. 
   

   Query with $expand

   
   


3. 
   

   Create with DeepInsert

   
   

1. Create an OData Sender Artifact

1. I used Northwind service edmx to create an OData Service "NorthwindCopy".


2. Choose the entities that you need. I have chosen Category, Customer, Employee, Order, Product, Supplier, Territory.


3. Edit the metadata to define the navigation properties for each of the Entities as desired. I have edited the metadata as required and below is the graphical representation of my metadata.
   

2. Query with $expand

I will configure $expand for EmployeeSet?$expand=Orders,Territories,Orders/Customer

1. Bind the Query operation for EmployeeSet.


2. The default integration flow will have only the root entity in response mapping.
   In this case only EmployeeSet is mapped.
   


3. We need a mapping for $expand with Orders, Territories, Order/Customer. For this, I will use a dummy OData Receiver. (This will be deleted later)
   


4. In the Processing tab of this dummy receiver, open the query wizard by clicking Select in Resource Path.


5. Choose Connection Source as Local EDMX File. Choose the OData sender metadata.


6. Choose Query operation with the desired sublevel. Choose the fields. In this case, Orders,Territories,Orders/Customer. Click Finish and this will generate the required XSD. Note the name of the XSD created.
   


7. Delete the dummy OData receiver that was created in previous step.


8. Similarly, use the query wizard to generate the XSD for the OData Receiver.


9. Use this generated XSD in response mapping step.
   


10. Note that the expanded entities are mapped respectively.


11. GET call <serviceRoot>/EmployeeSet?$expand=Orders,Territories,Orders/Customer will return expanded entities.

NOTE: If the Expanded entities are of cardinality 1:n and if not present, it is necessary to have an empty tag with the navigation property name as below.

<EmployeeSet>

	<Employee>

		...

		<Orders/>

		<Territories/>

	</Employee>

</EmployeeSet>

3. Create with DeepInsert

I will configure DeepInsert for Categories -> Products -> Supplier

1. Bind the Create operation for Categories.


2. The default integration flow will have only the root entity in request mapping.
   In this case, only CategorySet is mapped.


3. Add a dummy OData receiver to generate the XSD for the sender.
   


4. Open the query wizard, and choose the Sender EDMX. Select Create operation with sub level  and choose the entities.
   


5. Delete the dummy OData receiver that was created.


6. Similarly, configure the Create operation in Odata receiver with required sub levels.


7. Select the created XSD in request mapping.
   


8. Response for deepInsert can contain only the root entity. Hence, there will be no change in response mapping.
   UPDATE: Software increment 2101 allows deepinsert response to be of deep structure. You have to map the nested structure like request mapping.


9. Deepinsert from Postman.
   

[Badhusha]

Hi Saranya,

In the case of the On-premise TM system, what is the way to do the integration?

I have created the OData Service in SEGW. What is the further process to achieve the same?

 

Thanks,

Badhusha

[saranya_baskaran2]

Hi Badhusha,
I do not know about SEGW. In general, SAP cloud connector is recommended to have a secure connection between the On-premise SAP systems. You could look for blogs and other documents on this topic.

 

[juliozambrano]

Hello Saranya

How can I do a deep insert if the ID field in the LINK section is the ObjectID which is a field that is created automatically in the insert (when I put sublevel 2 the creation doesn't work anymore)

 

Thanks,

 

Julio

[former_member1413]

Hello Saranya ,

I have done message mapping and calling POST url but system is not passing whole payload to oData backend . How can I solve this issue . What do you mean by mapping only root entity ?

[former_member1413]

Hello Saranya,

Deep insert is not working for me . Could you please confirm if my message mapping is correct ?

[saranya_baskaran2]

By Root entity I mean only the parent entity. For Eg, IF you are doing deepinsert of Products and suppliers, then the entity that you mention in the POST url will be the root entity.

POST https://<serviceroot>/Products  ---> root entity is Products. The request body can contain Products and the navigation entities.

 

The system will pass the whole payload provided your mappings are done correctly.
You can enable trace on the integration flow and monitor the trace.

[saranya_baskaran2]

Hi Tisha,
I suppose this screenshot is for request mapping where LHS is OData sender source and RHS is OData receiver target.
In RHS you have mapped to SubProjects under 'link'. This is not deepnsert. This is a different use case of reference link. Hence the receiver payload will not be of deep insert structure.
More about 'link' here https://blogs.sap.com/2017/09/06/payload-structures-in-odata-v2-adapter-for-sap-cloud-platform-integ...

 

[former_member1413]

Hello Saranya ,

Thanks for reply . I have tried to map subprojects to subprojects as well but it doesnot seem to work .

Regards

 

[dineshhome1361_7]

Hi Saranya,

This final XML is converted back to atom+xml / json to the client. -- Is this point correct?

I tested API recently with ODATA V2 receiver adapter. It gives only XML as output eventhough I pass JSON as $format in query parameters

Thanks,

Dinesh

[former_member689727]

Hello Saranya,

I developed the ODATA service in CPI which has been tested as working successfully in Development environment. Now the idea is to transport and test in QA, where for CPI DEV and QA tenant being same, I tried coping the same tested Artifact and change the endpoints to point to our C4C Quality system along with newly shared Technical user shared by the C4C team.

Now the C4C standard ODATA Service when tested from POSTMAN using same technical user is working as expected while when we try to test  the CPI ODATA service for Quality environment using CPI credentials, it gives HTTP 500 error and the logs in attachment as under show the following error description.

 

 

HTTP 500

Log_AsAttachment

Error Log description:

<error xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"><code>ERROR</code><message xml:lang="en">Inconsistent Authorization: Re-activate Communication Arrangement.</message></error>

We tried re-Activating the Communication Arrangement multiple times but it does not help.

 

Could you please help what exactly might be wrong here.

Is there any specific way to manage Dev->Quality changes for an ODATA artifact in CPI?

 

Regards,

Shikha

 

[former_member721185]

Hi Saranya,

In section 1, Point 3.. You have mentioned that we need to edit the EDMX file. Do you suggest any tool to do the same? I am having difficulties is building a valid EDMX file with Navigation properties.