How to Expose a HANA Table via oData

With IOT gaining traction and SAP’s HANA DB as well as applications becoming warehousing tools, we’re seeing a greater need to integrate IOT data into HANA. One method of achieving this is by allowing sensor data from IOT devices to publish or post data directly into HANA database tables. Once data is in HANA, additional data modelling can be carried out and formatted data exposed for end user consumption to front end tools such as SAP Analytics Cloud or Business Objects.

In this blog, I’ll walk through the steps required to expose a HANA table via oData.

Note that both WEB IDE or Eclipse can be used. I’ll be using Eclipse

Authorizations

For WEB IDE, you will need:

Role: sap.hana.xs.ide.roles::Developer
Description: This role has all the privileges required to use all the tools included in the SAP HANA Web-based Development Workbench
Role: sap.hana.xs.debugger::Debugger
Description: This role enable a developer to use the debugging features of the browser-based IDEs.

In addition, to work with HANA XS, the following roles and privileges are required:

◈ EXECUTE privilege on SYS.REPOSITORY_REST
◈ SELECT privilege on _SYS_BI and _SYS_BIC schemas
◈ Analytic privilege _SYS_BI_CP_ALL
◈ Following privileges on package where HANA XS artifacts will be created:
     ◈ READ
     ◈ EDIT_NATIVE_OBJECTS
     ◈ ACTIVATE_NATIVE_OBJECTS
     ◈ MAINTAIN_NATIVE_PACKAGES
◈ If you are using modeling views (Attribute, Analytic and Calculation views), then you also need REPO.READ privilege on their package.

Step 1: Setup a package for the oData artifacts. Ensure you’re in the HANA Development perspective

Under the Repositories tab, ensure you have a repository created that is pointing to your HANA DB.

Within the repository, right click, select New then Repository Package

Give it a name and assign it to the corresponding Repository Workspace, then hit Finish to create it.

We now need to create the following XS artifacts:

.xsaccess – The application-access file enables you to specify who or what is authorized to access the content exposed by a SAP HANA XS application package and what content they are allowed to see. For example, you use the application-access file to specify if authentication is to be used to check access to package content and if rewrite rules are in place that hide or expose target and source URLs

.xsapp – Each application that you want to develop and deploy on SAP HANA Extended Application Services (SAP HANA XS) must have an application-descriptor file. The application descriptor is the core file that you use to describe an application’s framework within SAP HANA XS.

Anonuser.xssqlcc – The SQL-connection configuration file specifies the details of a connection to the database that enables the execution of SQL statements from inside a server-side (XS) JavaScript application with credentials that are different to the credentials of the requesting user.

table.xsodata – this is the table that will be exposed via the oData framework

ANONUSER.XSSQLCC

In order for applications to access the table, we will configure the service to allow for anonymous access.

Right click on the package, go to New and select Other

Select the XS SQL Connection Configuration File

Select the appropriate parent folder and the oData package previously created. Provide the file name as anonuser and hit the finish button

Go to your package and refresh and expand it to see the file:

You will notice it is inactive. Double click to open it and enter the following:

{ “description” : “Anonymous SQL connection” }

Right click on the artifact in the package and select Activate

The artifact will be activated:

.XSACCESS

The application-access (.xsaccess) file enables you to specify who or what is authorized to access the content exposed by the application package and what content they are allowed to see.

Right click on the package, go to New and select Other

Type XS in the search bar to narrow down the search and select XS Application Access File and hit next

Select the parent folder and the correct package, leave everything else with default settings and hit the Finish button

Go to your package and refresh and expand it to see the file:

The file will be inactive. Open the file and enter the following:

{

"exposed": true, 

"authentication": null,

"mime_mapping": [{

"extension": "jpg",

"mimetype": "image/jpeg"

}],

"prevent_xsrf" : false,

"force_ssl": false,

"enable_etags": true,

"anonymous_connection": "oData::anonuser",

"cors": [{

"enabled": true,

"allowMethods": ["GET","POST","PUT","DELETE", "HEAD"],

"allowOrigin": ["*"]

}],

"allowHeaders": [

"Accept",

"Authorization",

"Content-Type",

"X-CSRF-Token",

"Access-Control-Allow-Origin"

],

"exposeHeaders": [

"x-csrf-token"

],

"cache_control": "no-cache, no-store"

}

Pay special attention to the “anonymous_connection” row, as that will need to map back to the connection file previously created. Semantically, the first part (oData) is the package name and the second part is the name of the connection file without the .xssqlcc.

Right click on the artifact to activate it.

.XSAPP

Each HANA XS application must have an application descriptor file called .xsapp.

Right click on the package, go to New and select Other

Type XS in the search bar to narrow down the search and select XS Application Descriptor File

and hit next

Keep all default settings ensuring the correct package is selected and hit finish

Expand the package to see the new file

Open the file. It will remain blank, with the exception of curly brackets, so it should look as follows:

Activate the file to have all three files activated and ready

.XSODATA

The OData service definition is the mechanism you use to define what data to expose with OData, how, and to whom. Data exposed as an OData collection is available for display by client applications, for example, a SAPUI5 app. To expose information by means of OData to applications, you must define database views that provide the data in a .xsodata file.

Right click on the package, go to New and select Other

Type XS in the search bar to narrow down the search and select XS ODATA File and hit next

Provide the file a meaningful name. In my case I’ll be exposing a table containing jump sensor data

Hit Finish and open the new .xsodata file.

We need to point that file to the table that we want to expose, so enter the following:

service { "SCHEMA"."TABLE" as "ALIAS"; }

SCHEMA: that’s the schema name for the table you wish to expose

TABLE: is the table name you wish to expose

ALIAS: an alias for the table name for the oData service

For my particular example, I will be exposing the table below:

here what my .xsodata file looks like:

Right click and activate the file.

To complete configuration for anonymous access, a backend user must be defined in the XS Admin tool.

Open a new browser window and open

https://{HANADB}{accountName}.hanatrial.ondemand.com/sap/hana/xs/admin

I am using my user with the correct authorizations to login to the XS Admin page:

Find the oData package and hit the right arrow:

You should see your anonymous user file anonuser.xssqlcc and select it, then click on Edit

Enter the database user and password in the corresponding fields and hit Save

And you are now ready:

To get the link for the oData file created, go back to HANA and open the .xsodata file created, and right click anywhere on the page and select Run As -> 1 XS Service

Your browser will open with the oData link:

If you want to see the metadata of the oData file, simply add /$metadata to the URL:

And there you have it. External applications can now use the link to post data to a HANA table using the oData framework.