Skip to Content
Author's profile photo Stefan Geiselhart

HANA EIM SDI/SDQ SPS12 – OData Adapter in HANA SPS12 – GET, PUT operation and REPLICATION flowgraph

Working with the OData Adapter in HANA SPS12


In this blog entry the following activities are covered:

  • OData Adapter creation
  • Using a public OData service with read and write capabilities
  • OData remote source & virtual table creation
  • Virtual table select (OData GET)
  • Virtual table insert (OData PUT)
  • Modeling in SAP HANA WebIde in Google Chrome Browser
  • Flowgraph to capture the delta of OData source (each step for creation is described)


This is an extract from the “Whats New in HANA smart data integration – SPS12” slidedeck. It describes some of the new abilities that the OData adapter has in SPS12.




Used documents:

– HANA EIM SPS12 Administration Guide

– HANA Academy Video “SAP HANA Academy – Smart Data Integration/Quality: The Table Comparison Transform [SPS09″


Used resources:

– HANA SPS12 system

– Chrome browser with WebIde




Now let’s begin…


It is a really simple scenario we are dealing with: Accessing a public OData URL, read from and write to the tables exposed there. HANA is running on SPS12 on premise. The purpose of this blog entry is to better illustrate the different steps analogous as they are explained in the HANA EIM Administration Guide (chapter 8.13.1).


In this scenario we are accessing the following public OData Service URL (ODATA V4) which allows read and write operations.


The following describes the context of tables that we can access in this example:



In order to realize the above mentioned points, you can consider the following steps:


1.) Create the OData Adapter in HANA studio manually via the following SQL command (this is necessary and an exception as the OData Adapter is NO system adapter! Other than common adapters/drivers used in the SDA context):


create“ODataAdapter”‘display_name=OData Adapter;description=Odata Adapter’ AT LOCATION DPSERVER;

The following architecture outlines the location of the ODataAdapter which is not communicating through the DP-Agent. That aspect can become important in scenarios where e.g. IP filtering on OData APIs is considered.

In cloud scenarios, your DP-Agent establishes a tunneled connection to your on-premise network and can access on-premise data sources. Your ODataAdapter remains in the cloud and is therefore still outside of your company network. I.e. OData APIs that you can authenticate against from your on-premise applications do not necessarily work in that constellation.


(select * from “SYS”.“ADAPTERS”):




2.) Create new remote source, choosing the previously create OData Adapter:

(System priviledge “CREATE REMOTE SOURCE” required”)




Enter your proxy, trust, CSRF and format settings according to your requirements. For further information on each of the fields consider reading the EIM Administration Guide.


In my case I set “Support Format Query” to true which allows me to receive the dataset in JSON-format. In the credentials section (if you also use a public OData service to test e.g. the adapter capabilities you need to enter anything… I entered for instance “test” as user and “test” as password. It will allow you to connect as it doesn’t require any user).


In productive and secure environments you most probably need a user, password and/or a certificate to be on the safe side! 😉




3.) Browse remote tables and create virtual tables of your choice you want to play with


Now go to Provisioning and browse your remote tables. You should now be able to access them via your new OData remote source:





4.) Working with your virtual table (1) – READ (or GET…)


You can now browse the entries of your virtual table.




You can verify the entries displayed in HANA studio with the entries you get when calling the service directly from a URL$format=json




5.) Working with your virtual table (2) – WRITE (or PUT…)


If your OData service allows read and write operations you can also insert new entries with e.g. using the corresponding insert statement:


insert into “SYSTEM”.“MV1_RW_ODATA_Products” values(13,‘Afri Cola’,‘The Original Cola’,‘01.10.2005 00:00:00.0’,‘01.10.2006 00:00:00.0’,3,9.9);





You should now see the new entry when firing a GET request from your browser on that table:$format=json




6.) Create a replication task with the table comparison transform node to capture the delta only. In order to achieve this, you need to follow the subsequent steps:


(1)    In the flowgraph builder in the WebIDE drag and drop a new data source node and select the virtual table your OData source table. Remember that you use WebIde only with Google Chrome Browser.




(2)    Drag and drop a new Table Comparison node onto the canvas and connect your data source node with it.




(3)    Intermediate step (this could also be done in advance…) Create your target table. You could do it as follows:


CREATE TABLE “ODATA_Products_target” LIKE “SYSTEM”.“MV1_RW_ODATA_Products”;

It will create a similar table with the same table structure.

(4)    Adjust your target table and a new primary key of type integer, e.g. “SURR_ID” (this approach follows the concept as introduced in the SAP HANA Academy Video “SAP HANA Academy – Smart Data Integration/Quality: The Table Comparison Transform [SPS09]”).


In my example I need to drop the already existing primary key “ID” first in order to realize adding a new primary key “SURR_ID”.




You can then add a new column called “SURR_ID” of type integer to your target table (in edit mode).




(5)    After adding this new column you can specify this column as a new primary key with the following command:



You need to do this for the table comparison transform to work properly. We will see this later on in this document



(6)   What you also need to do is create a database sequence for your data sink in your flowgraph. You can do this either in WebIde or in HANA Studio. You need to create a new file that ends with *.hdbsequence. Just right click on your package and select “File”.




Enter your sequence name with the correct file ending.




Specify your sequence properties. Adjust the schema property according to your schema’s name. Save your sequence.




(7)    Now you can edit your table comparison node. First and foremost choose your comparison table. This needs to be your previously created target table that has the “SURR_ID” and ideally the same structure as your source table (…or another structure, depending on your requirements).




In the table comparison transform specify the generated key attribute which is your “SURR_ID” field of your target table.




If you OData source allows delete operations you can select “Detect Deleted Rows From Comparison Table”. This will ensure that deleted rows in the source      will also be deleted in your target table.



Click on attributes in your Table Comparison node and drag and drop the fields which you want to be considered for comparison. You need to set the key      column as “Key = true”. The table comparison node will capture every change that occurs in one of these columns and correspondingly transfer the delta to      your target table.




(8) Select a data sink node. Choose your target table as your data sink.



Enter the database sequence into the corresponding field of your data sink. You can find it on the “Settings” tab of your data sink node. What you should also do is specify your key generation attribute which is “SURR_ID” in our case.




(9) Save your flowgraph and execute the task to conduct an initial load of your target table.


If you can´t save your flowgraph in WebIde you’re probably lacking priviledges. Make sure you have object priviledges on:








You may also refer to the EIM Administration Guide chapter “7.1 Assign Roles and Priviledges”



(10) Check in system table “M_TASKS” how many records where processed with the initial load.






(11) Insert a new record into the source table of the OData service with e.g. an insert command, re-run the task and check how many records have been processed. It is assumed that only the delta or respectively changed rows were transferred.


INSERT INTO “SYSTEM”.“MV1_RW_ODATA_PRODUCTS” VALUES(14,‘NEW COLA’,‘THE ORIGINAL WOW COLA’,‘01.10.2005 00:00:00.0’,‘01.10.2006 00:00:00.0’,3,39.9);








Alternatively you could also execute an update statement on your OData source table, re-run the task, see how many records were processed and what has changed and compare your source and target table if it really worked out for you.


(12) Finally see what is in the target table. We can see that our inserted record was appended to the table:


SELECT * FROM “SYSTEM”.“ODATA_Products_target”;




On top of what we modelled we could now create a replication task to let the batch task run e.g. every ten minutes to capture the delta of our OData source.


If you have any comments, hints, tips and tricks what I did please share and let us all know. I also embrace suggestions to enhance this example.


Kind regards



Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Nice blog Stefan. Appreciate you posting this information.


      Author's profile photo Former Member
      Former Member

      Thanks, Stefan

      In my case when I am adding Northwind OData tables are showing from metadata.

      But When I adding Gateway(ECC) Odata Service tables are not showing from Odata, I tried with standard Odata but still, tables are not showing. I got the message that connected successfully.

      Do you know what is the probable reason for that?




      Author's profile photo Stefan Geiselhart
      Stefan Geiselhart
      Blog Post Author

      Hello Kalpan,

      which type of OData Service are you trying to use?

      Which version of the OData protocol do you want to use? To my knowledge, the OData Adapter support only Odata V1/2 + V4.

      Unfortunately I have not tested the adapter with any ECC based OData service but I have some doubts for the proper functioning here.

      Best regards,


      Author's profile photo Ajay Sandal
      Ajay Sandal

      Hi Stephan

      Appreciate it if you can through some light on issue we are having.

      We are facing connection issues to MS Dynamics 365 using Odata due to auth2 authentication.

      HANA 2.0 SPS04 Rev 46 SDI 2.0.

      Do we need custom adapters for this connectivity or how to go about that?



      Ajay Sandal

      Author's profile photo Tisha Haria
      Tisha Haria

      Hi Ajay,

      Were you able to solve this issue or did you create custom adapter ?



      Author's profile photo Ajay Sandal
      Ajay Sandal

      Hi Tisha


      Sorry for the delay in reply, we are still checking this issue.

      We ended up doing some workaround like pulling MS Dynamics data to a SQL server and creating a connection from HANA to SQL server, just as a workaround to get going.

      Our issue is more on authentication with MS Dynamics from HANA Odata adapter.



      Ajay Sandal

      Author's profile photo Naresh Eedara
      Naresh Eedara

      Hi Stefen,


      I am getting below error while insert the data into OData . Can you please help.


      Could not execute 'insert into "EXTUSER"."MV1_RW_ODATA_Products" values(13,'Afri Cola','The Original ...' in 944 ms 617 µs .
      SAP DBTech JDBC: [403]: internal error: Error executing query Failed to create new entry because Moved Permanently for query " INSERT INTO "Products" ( "ID", "Name", "Description", "ReleaseDate", "DiscontinuedDate", "Rating", "Price") VALUES (?, ?, ?, ?, ?, ?, ?)"


      Thanks & Regards


      Author's profile photo Amrith Kumar
      Amrith Kumar


      I have a strange issue, I created an OdataAdapter using the below query

      create“ODataAdapter”‘display_name=OData Adapter;description=Odata Adapter’ AT LOCATION DPSERVER;

      But, what I see is while creating the new remote source using the previously created ODataAdapter, the location of this adapter is wrong. Its showing as 'Indexserver' instead of 'DPSERVER' (refer screenshot attached)

      I tried to drop the adapter and create it again, but still face same issue. Any help is highly appreciated.


      Thanks and Regards,

      Amrith Kumar V M

      Author's profile photo Stefan Geiselhart
      Stefan Geiselhart
      Blog Post Author

      Hi Amrith,

      unfortunately I can't help in this.

      I never faced the issue before and I suggest to either search for SAP-Notes or raise an incident.

      Best regards,


      Author's profile photo Amrith Kumar
      Amrith Kumar

      Hi Stefan,


      Thanks for your reply, after further investigation it was found out that the issue is because of a bug in Hana studio or the Eclipse version being used. I finally was able to setup the remote source using the HANA web based development.

      Thanks and Regards,

      Amrith Kumar V M