Skip to Content
Technical Articles
Author's profile photo Denys van Kempen

OData with SAP HANA Cloud | Hands-on Video Tutorials

With this blog post series we provide an update with the latest information on getting started with SAP HANA Cloud on the SAP Cloud Platform.

  1. About SAP HANA Cloud
  2. SAP HANA Cloud Getting Started
  3. SAP HANA Cloud and SAP Business Application Studio
  4. HDI with SAP HANA Cloud
  5. SAP Analysis for Microsoft Office and SAP HANA Cloud
  6. Cloud Foundry Advanced
  7. SAP HANA Cloud and SAP BTP Trust
  8. Data masking and data anonymization
  9. Predictive Analysis Library (PAL) and Automated Predictive Library (APL)
  10. Remote data sources and virtual tables
  11. OData with SAP HANA Cloud <=
  12. SAP HANA Cloud Graph
  13. Role Attributes
  14. SAP HANA Cloud and Smart Data Integration

For more information about the free trial, see

For the new features overview posts, see

Questions? Post as comment.

Useful? Give us a like and share on social media.

Thanks!

/wp-content/uploads/2016/02/sapnwabline_885687.png

Hands-On Video Tutorials

Philip MUGGLESTONE just added another video tutorial to the SAP HANA Cloud series. In this blog, you will find the video embedded with some additional information and resources. Following along in the patented zero-to-hero format, you will be ready to start working with OData with minimal effort and no time wasted.

You can watch the video tutorial in a little under 10 minutes. What you learn is

  • How to use a Yeoman app generator to create a sample multi-target app (MTA) which automatically creates the OData services for a chosen schema in SAP HANA Cloud
  • How the code works and what apps, services, and database catalog objects get created
  • How to use Postman to GET, POST, and DELETE data using the OData Services

As development environment we use the SAP Business Application Studio and the free 90-day SAP Cloud Platform trial environment for which you can sign up here:

To follow along you will need some basic understanding of the SAP Cloud Platform concepts and be familiar with global and subaccounts, Cloud Foundry, destinations, subscriptions, identity provider, SAP HANA Cloud, and HDI. In case you are new to the SAP Cloud Platform or could use an update, please first join our onboarding program:

As we know your time is precious, this will be a low-code affair as we will be using Yeoman to provide the scaffolding for our app supplemented with sample code from SAP HANA Academy GitHub repository:

Ready? Let’s make some REST calls!

/wp-content/uploads/2016/02/sapnwabline_885687.png

What Exactly is OData (again)? 

According to Microsoft (where the technology is coming from), as documented in Welcome to OData

OData (Open Data Protocol) is an ISO/IEC approved, OASIS standard that defines a set of best practices for building and consuming REST APIs. It enables creation of REST-based services which allow resources identified using Uniform Resource Locators (URLs) and defined in a data model, to be published and edited by Web clients using simple HTTP messages.

In more digestible terms, it is a way to interact with data sources over the web.

See Understanding OData in 6 steps for a bit more background from odata.org or (when you have bit more time), see blog series

 

/wp-content/uploads/2016/02/sapnwabline_885687.png

How Hard Can It Be? 

Working with OData using the SAP HANA XS classic model is not that difficult.

Create a file with the .xsodata file extension in your favourite IDE, say SAP HANA Web-based Development Workbench, and enter the service definition.

As documented,

However, this technology is deprecated and no longer available for SAP HANA Cloud.

service namespace "my.namespace" {
         "sample.odata::table" as "MyTable";
}

 

Working with OData using SAP HANA XS Advanced model and the SAP Cloud Platform Cloud Foundry environment for SAP HANA Cloud is a bit more challenging in particular when you need the current OData v4 version or have a larger selection of tables and views to configure. Let’s say this approach has some limitations.

An alternative and more recent approach involves the Cloud Application Programming (CAP) model but this provides a bit of a learning curve as it assumes a certain familiarity with CAP, Core Data and Services (CDS), Cloud Foundry, SAP HANA HDI, and OData.

/wp-content/uploads/2016/02/sapnwabline_885687.png

In Comes Yeoman

What if we would have a generator that writes the entire project including the CAP CDS modeling and also performs the SAP HANA configuration – so you can have OData up and running on all the tables and views of a given classical SAP HANA schema in about 5 minutes?

For free?

yo Saphanaacademy Odata

/wp-content/uploads/2016/02/sapnwabline_885687.png

OData on a Classical Schema

In this video tutorial, we install the yeoman OData generator and run it to scaffold the project. We can choose to include support for OData v2 (in addition to v4), add authentication and authorization, and directly build and deploy the app. After adding a role collection and inspecting the apps and services in SAP Cloud Platform cockpit, we interact with some of the OData services using Postman. The tutorial ends with a code walkthrough.

0:00 – Introduction

0:30 – SAP HANA Cloud > Advanced Settings: Allowed connections: Allow all IP addresses

0:45 – SAP HANA database explorer and execute sample schema, table, and view (source: travel_OData.sql)

1:30 – SAP Business Application Studio, create SAP Cloud Business Application dev space, and connect to the Cloud Foundry environment (trial org and space)

2:00 – open Terminal and install a Yeoman generator

npm i -g generator-saphanaacademy-odata

2:30 – Run the yo command and select Saphanaacademy Odata template: enter the app name, SAP HANA Cloud endpoint, database user, password, schema, OData v2 support, authentication and authorization, build and deploy.

3:45 Go to SAP Cloud Platform cockpit > subaccount Security > Trust Configuration: <e-mail> and assign the desired role collection

  • myappodata_read
  • myappodata_write

4:00 Run the application (app router) and the OData service is displayed with all tables and views.

4:45 – for OData v2 append /v2 to the URL, e.g. /v2/srv/Customers

5:00 – Open Postman

5:30 – Authorization: OAuth 2.0 > Get New Access Token:

grant type = password credentials

username = <e-mail trial account)

clientid, clientsecret, and url are defined as environment variables for the app router.

cf env myappodata-app

6:50 – Change URL to srv (instead of app)

7:00 – Use HTTP POST to insert data using JSON format specifying in the header

Content-Type = application/json/IEEE754Compatible=true

7:55 – Code walkthrough

Running travel_OData.sql script in SAP HANA database explorer

Running Yeoman generator in SAP Business Application Studio

Assigning role collection using SAP Cloud Platform cockpit.

 

Postman access token configuration

/wp-content/uploads/2016/02/sapnwabline_885687.png

Share and Connect

Questions? Please post as comment.

Useful? Give us a like and share on social media.

Thanks!

If you would like to receive updates, connect with me on

For the author page of SAP PRESS, visit

Over the years, for the SAP HANA Academy, SAP’s Partner Innovation Lab, and à titre personnel, I have written a little over 300 posts here for the SAP Community. Some articles only reached a few readers. Others attracted quite a few more.

For your reading pleasure and convenience, here is a curated list of posts which somehow managed to pass the 10k-view mile stone and, as sign of current interest, still tickle the counters each month.

/wp-content/uploads/2016/02/sapnwabline_885687.png
For the SAP HANA Cloud e-bite, see

Assigned Tags

      37 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Naveen Jain
      Naveen Jain

      Dear Denys,

      Great blog, many things are at one place, your efforts reflecting in this blog.

      Thanks

      Naveen Jain

      Author's profile photo Zacky Burhani Hotib
      Zacky Burhani Hotib

      Dear Denys,

      what a great blog and I have a question.

      Is that possible to expose the data using XML format as a standard return from Odata?

      because from this tutorial we only get the JSON format when we put the resource path

       

      Thank you,

      Zacky

      Author's profile photo Denys van Kempen
      Denys van Kempen
      Blog Post Author

      Thanks Zacky,

      This is not exactly my expertise but I have been informed that the OData services created by CAP don’t support query results in XML (including OData v2).

      Maybe Thomas Jung can comment on how to best approach this?

      Thx

      Author's profile photo Thomas Jung
      Thomas Jung

      The XSJS compatibility layer version of XSODATA also does NOT support XML format for OData.

      Author's profile photo Denys van Kempen
      Denys van Kempen
      Blog Post Author

      Thanks, and regarding the original question?

      Is that possible to expose the data using XML format as a standard return from Odata?

      Author's profile photo Thomas Jung
      Thomas Jung

      That's what I thought I was answering.  XSODATA in the XSJS compatibility layer does not support the XML output format.  Only JSON.

      Author's profile photo Zacky Burhani Hotib
      Zacky Burhani Hotib

      Thank you Denys and Thomas for your information

      Author's profile photo Fernando Maria Vega Villasante
      Fernando Maria Vega Villasante

      Hi Denys,

      I am in contact with you, because I have seen that your link on: OData with SAP HANA Cloud can solve a problem that I currently have.

      I have managed to carry out all the steps that you indicate in the blog, except the change of the ulr, where it says "app"
      by the value "srv".
      My service is deployed in foundry and when it generates the url, it does not create it with the value "app".

      https://cep---xxxxxxxxxx--s-a--dbcex-spacecex-db-xxxxxx.cfapps.eu20.hana.ondemand.com/

      Would you know how this problem can be solved?

      His blog is very interesting and very well explained

      Thank you very much and greetings.

      Author's profile photo Denys van Kempen
      Denys van Kempen
      Blog Post Author

      Hi Fernando,

      Checked with Philip but it is not clear to us what might cause this issue.

      The approuter obtains an access token and with this token we can call the back-end (srv) module(s).

      Maybe you can write out the steps and add some illustrations where needed so we can reproduce the issue.

      Thx

      Author's profile photo Shyam Bahadur
      Shyam Bahadur

      Hi Denys van Kempen ,

      Really helpful blog. I had a query, if there is another table added to my schema.

      Can I pull the details in my CDS automatically or do I have to create a new project everytime?

      Thanks in advance

      Shyam

      Author's profile photo Denys van Kempen
      Denys van Kempen
      Blog Post Author

      Hi Shyam,

      What happens when you try?

      Author's profile photo Shyam Bahadur
      Shyam Bahadur

      Thanks for your prompt response.

      Say for example when I created the project, I had 2 tables in the schema . The application also shows 2 entityset for 2 tables respectively.
      Now another table is added to the schema, still 2 entity set are there.
      My query is, do I have to update the schema.cds ,cat-service.cds files manually ? Is there a command which can update them.

      Creating a new project with the schema fetches all the tables which are present at that moment.

      Hope I am able to explain what I am trying to do.

      Thanks

      Shyam

      Author's profile photo Denys van Kempen
      Denys van Kempen
      Blog Post Author

      Hi Shyam,

      Thanks for asking,

      >>  Is there a command which can update them.

      Not yet (nor is there anything in the works) but you can run the generator again to recreate everything again based on what's in the schema at the time.

      >> My query is, do I have to update the schema.cds ,cat-service.cds files manually ? 

      That is also an alternative. The initial run provides an indication of what needs to be done.

       

      Author's profile photo Kerry Dong
      Kerry Dong

      Thanks for your blog.

      But i went some issues when I try to read the data via post man.

      it shows 401 error when i try to request a token.  but i followed all the steps you mentioned in the video.

      do you know why? Thanks!

      Author's profile photo Denys van Kempen
      Denys van Kempen
      Blog Post Author

      Hi Kerry,

      Best to post your question to the forum > https://answers.sap.com for follow up and knowledge sharing.

      Copy/paste is fine but maybe include some screen prints so the issue can be reproduced.

      Thx!

      Author's profile photo Davide Dal Bosco
      Davide Dal Bosco

      Thanks for your blog.

      I followed the guide, but once the token is created with Postman, there is no possibility to change 'app' to 'srv' as the application name does not appear in the URL.

      I tried to put the URL I find inside SCP under myappodata-srv but Postman returns error 401 not authorized.

      Has the procedure changed with respect to this guide?

       

      Thanks.

      Author's profile photo Philip MUGGLESTONE
      Philip MUGGLESTONE

      Hi Davide,

      The procedure has not changed however a more recent video tutorial has been published that's based on the main CAP jumpstart generator.

      It's normal to get 401 when trying to access the back end service directly and authentication/authorization has been activated.The srv app requires a valid JWT and this is most often achieved via the approuter but it's also possible to authenticate via Postman using Oauth2.

      For the latest video see Denys' blog and search on "Data Classical Schema": https://blogs.sap.com/2021/05/31/sap-business-technology-platform-extension-generators-hands-on-video-tutorials/

      Or directly here - I recommend to follow the steps exactly as shown in that video especially @16:45: https://www.youtube.com/watch?v=8Bh7farUun8&list=PLkzo92owKnVwQ-0oT78691fqvHrYXd5oN&index=12

      Philip

      Author's profile photo Sebastiano Marchesini
      Sebastiano Marchesini
      Hi @denys.kempen , thanks for the blog. 
      But when I deploy the application it's said like: 
      
      Creating service "myappodata-db" from MTA resource "myappodata-db"...
      Service operation failed: Controller operation failed: 502 Updating service "myappodata-db" failed: Bad Gateway: Error creating service "myappodata-db" from offering "hana" and 
      plan "hdi-shared": CF-ServiceBrokerRequestRejected(10001): Service broker error: Service broker hana-broker failed with: Can not create service instance 'myappodata-db': 
      There is no database available. Ensure that you have a database available in space 'dev' within organization '4164c669trial'.

       

      When I have for sure the HANA Cloud connected and right configured.
      Do you know why?

      Author's profile photo Denys van Kempen
      Denys van Kempen
      Blog Post Author

      Hi Sebastiano,

      There are two Schema and HDI Container services. The one with Trial appended points to the HANA Service (not HANA Cloud). Could it be the wrong HDI service is configured?

       

      Author's profile photo Ingo Peter
      Ingo Peter

      Hello,

      Is there any easy way to update the  OData service if new tables or views were added to the base  schema? I tried starting yo with the same parameters again, but that runs into an error which says that the user name already exists:

      [...]FAILED
      Server error, status code: 400, error code: 60002, message: The service instance name is taken: streaming-db-STREAM
      Error: user name already exists: STREAMING_STREAM_GRANTOR: line 1 col 13 (at pos 12)[...]

      So it seems rather cumbersome if I have to invent new names for each object change in the base schema. Is there a clean way to delete all the artifacts which have been created with yo?

      Thx, Ingo

      Author's profile photo Pal Rajib
      Pal Rajib

      Hello,

      Question: Is there any way to open 2 different db connection(to HDB) in CAP application?

       

      I am using below 2 statements to connection to HANA database.

      const cds = require('@sap/cds')

      const db = await cds.connect.to('db');

       

      In CAP, I am looking for 2 different  db connection that I can use in Handlers.

      1. One db collection: I am using for functional operation, if any error happens, it should rollback  automatically (above 2 statements are used).
      2. 2nd db connection: I wanted to use for logging activity. It should not rollback on error scenario.

      I could able to achieve this in HANA Classic framework. When user post any data to HANA database, in XSJS framework, the handler catch incoming data with help of “param” as parameter to xsjs function.

      Example:

      function WarehosueTaskCreate(param){

      var conn = param.connection;

      ….

      var dbconn = $.db.getConnection();

      }

      To do any functional db operation , I am using “param. connection” to connect to db. If any error happens then rollback automatically.

      At the same time, we are logging errors in table (Inserting error information to a table). So error information should commit to table on error scenario and rollback should happen only to functional data. To achieve this, we are using another db connection “$.db.connection”. Using this connection, it commits log data to database.

      Can we achieve similar functionality in CAP?

      Author's profile photo Denys van Kempen
      Denys van Kempen
      Blog Post Author

      Hi Pal,

      Best to post the question to the forum instead (copy/paste will do)

      > answers.sap.com

      Author's profile photo Barbara Obremska
      Barbara Obremska

      Thank you very much for this blog, it is very helpful!
      I have noticed one typo and it took me a couple of minutes to figure out what was wrong, so I am sharing this:

      instead of Content-Type = application/json/IEEE754Compatible=true

      it should be Content-Type = application/json;IEEE754Compatible=true

      (the difference is the semicolon)

      Also, I think the way to establish a connection to the loud Foundry has changed (for me it didn't work when I clicked in the left bottom corner on "Connect") I managed to do it so: Select View > Find command > search for CF: Login to Cloud Foundry and select it

       

      Author's profile photo Denys van Kempen
      Denys van Kempen
      Blog Post Author

      Thanks for the feedback Barbara Obremska

      Author's profile photo Tiago Benjamim
      Tiago Benjamim

      Hi Denys,

      I'm having a weird behavior when using yo -Saphanaacademy Odata from a Classic Schema,

      The content (tables name and field names) in the schema.cds file created are all I lowercase is not

      reflecting the name convention from my schema and i need to change the file before deploy the project, any idea why or how to resolve ?

      Example:

      __ Schema_01

      |__ TableNumber01

      |__ Field_Id

      Field_Name

       

      Schema.cds

      namespace xxxxxxxxxxdb;
      context nnnnnnnn {
          @cds.persistence.exists
          entity tablenumber01 {
              field_id        : String(10);
              field_name  : String(10);
          };

      }

      Thanks a lot
      Author's profile photo Denys van Kempen
      Denys van Kempen
      Blog Post Author

      Hi Tiago,

      It appears that CAP doesn’t directly support SAP HANA mixed-case naming. The generator takes this into account.

      Note that we recommend to use the more recent generator-saphanaacademy-cap, as it’s more actively maintained and has a lot of additional capabilities (including a refresh subgenarator)

      Author's profile photo Saranya Sampath
      Saranya Sampath

      Hi,

       

      Getting error "usergroup cannot be deduced automatically when creator has more than one USERGROUP OPERATOR privilege" while executing command yo saphanaacademy-odata.

       

      With regards

      Saranya

      Author's profile photo Denys van Kempen
      Denys van Kempen
      Blog Post Author

      Hi Saranya,

      This error is new to us but using a user which only has a single USERGROUP OPERATOR privilege should provide a workaround.

      Author's profile photo Saranya Sampath
      Saranya Sampath

      Hi Denys,

      Thanks for your reply. I have executed  below sql statements. After I tried  same steps

      yo saphanaacademy-odata . Now I am getting insufficient privilege error

      
      
      CREATE SCHEMA "PLAIN";
      CREATE USER PLUSR PASSWORD "HanaRocks01" SET USERGROUP DEFAULT;
        ALTER USER PLUSR DISABLE PASSWORD LIFETIME;
      
      CREATE ROW TABLE "PLAIN"."REGIONS" (	REGION NVARCHAR(5), 	DESCRIPTION NVARCHAR(100) );
      
      CREATE ROLE CCROLE;
      grant  SELECT, UPDATE, INSERT, DELETE, EXECUTE, SELECT METADATA ON SCHEMA "PLAIN" TO CCROLE with grant option;
      grant  CCROLE to PLUSR with admin option;

       

      If I try to check missing privilege getting result as

      privilege - USER ADMIN

      IS_MISSING_ANALYTIC_PRIVILEGE - FALSE

      IS_MISSING_GRANT_OPTION - FALSE

      DATABASE_NAME -

      SCHEMA_NAME

      OBJECT_NAME

      OBJECT_TYPE

       

      Regards

      Saranya

      Author's profile photo Tiago Benjamim
      Tiago Benjamim

      Hi Denys,

      The client I'm working to update the BTP Neo to CF, and now i need to create the Schemas and Tables again in the new environment, it is already done.

      Now I am exposing the objects from the Classic Schema using yo command "yo saphanaacademy-odata", it works pretty good, I can reach all tables from Postman with my BTP user.

      My doubts are:

      1 - how set up a new "Destination" to the new end-point ? and

      2 - is it possible to access the exposed objects from a generic user ? "no BTP user" ?

      PS: I'm not using saphanaacademy-CAP

      Thanks a lot,

      Author's profile photo Philip MUGGLESTONE
      Philip MUGGLESTONE

      Hi Tiago,

      In BTP authentication and authorizations are normally managed via the xsuaa service - however this does imply a user in the active identity provider (IdP).

      Interactive users are normally authenticated via the approuter module. It's also possible to authenticate directly eg: from another client (whether hosted in BTP or not).

      Best practice for a "generic" user would be to create a "technical" user in the active IdP to be used for this specific purpose. Assign BTP role collections to this "technical" user for authorization.

      The BTP destination service is a great way to encapsulate the authentication process for non-interactive access. Create a destination something like this:

      Type=HTTP
      Name=app-srv
      ProxyType=Internet
      URL=https\://<org>-<space>-<projectName>-srv.cfapps.<region>.hana.ondemand.com/srv
      Authentication=OAuth2Password
      User=technicalUser@mydomain.org
      Password=<technicalUserPassword>
      tokenServiceURL=https\://<subdomain>.authentication.<region>.hana.ondemand.com/oauth/token
      clientId=<clientId>
      clientSecret=<clientSecret>

      It points to the <projectName>-srv backend endpoint and authenticates with the "technical" user and password with authentication type "Oauth Password Authentication". You can find the other values like clientId, clientSecret and token service URL in the service binding between <projectName>-srv and xsuaa (once the app is deployed).

      Then you can simply access the destination from whatever client you have  to get access to your OData service.

      See also: https://help.sap.com/docs/CP_CONNECTIVITY/cca91383641e40ffbe03bdc78f00f681/452357cdd82a4c0ba6095b36c0057526.html

      Regards,

      Philip

      Author's profile photo Tiago Benjamim
      Tiago Benjamim

      Hi Phillip,

      Thanks for your help.

      I created the destination as you mentioned using the client details and could reach the service, but i'm getting 401 - unauthorization, and now i figured out that the destination should be created on an "extensions NEO environment subaccount"  not CF, and the db artifacts remain in CF.

      I guess the better way should be create trust between both environment right ? I will do more research about it.

      Thanks again.

      Regards,

      Tiago

      Author's profile photo Philip MUGGLESTONE
      Philip MUGGLESTONE

      Hi Tiago,

      Indeed subaccounts are isolated by design so trust may need to be configured.

      This video tutorial may be of interest as it covers setting up trust between two CF subaccounts (with user propagation): https://www.youtube.com/watch?v=x5RlLzhFRlI&list=PLkzo92owKnVy6eAKxRAhVC5Vp_AjsZ23U&index=19

      The BTP Connectivity docs also cover trust between Neo and CF: https://help.sap.com/docs/CP_CONNECTIVITY/cca91383641e40ffbe03bdc78f00f681/95dde761bb0d4df3acfe9e32e80bb6b7.html

      Regards,

      Philip

      Author's profile photo Amin Ullah
      Amin Ullah

      Can we create/deploy OData services without using SAP BTP (SAP HANA Cloud system) with non sap systems ?

      Author's profile photo Denys van Kempen
      Denys van Kempen
      Blog Post Author

      Hi Amin,

      OData is an open protocol, there is no requirement to have SAP HANA Cloud as database (as in "can we use ODBC without using HANA").

      Author's profile photo Zhiyi Tang
      Zhiyi Tang

      Dear Denys van Kempen ,

      it's a very useful blog! Could you please also shed lights on how to fetch Odata (e.g. JSON format) to SAP HANA cloud instances? Is it possible without (or with little) manual coding?

      Many thanks and best regards,

      Zhiyi Tang

      Author's profile photo Denys van Kempen
      Denys van Kempen
      Blog Post Author

      Hi Zhiyi,

      Good question but this concerns a different scenario. Have not looked into this yet. Others might > consider posting the question on the SAP Community (answers.sap.com) or on Stack@SAP (employees)