Wednesday, 4 July 2018

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

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Tutorials and Materials

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

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Tutorials and Materials

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

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Tutorials and Materials

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

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Tutorials and Materials

Select the XS SQL Connection Configuration File

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Tutorials and Materials

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

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Tutorials and Materials

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

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Tutorials and Materials

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

{ “description” : “Anonymous SQL connection” }

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Tutorials and Materials

Right click on the artifact in the package and select Activate

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Tutorials and Materials

The artifact will be activated:

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Tutorials and Materials

.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

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Tutorials and Materials

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

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Tutorials and Materials

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

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Tutorials and Materials

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

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Tutorials and Materials

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.

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Tutorials and Materials

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

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Tutorials and Materials

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

and hit next

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Tutorials and Materials

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

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Tutorials and Materials

Expand the package to see the new file

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Tutorials and Materials

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

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Tutorials and Materials

Activate the file to have all three files activated and ready

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Tutorials and Materials

.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

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Tutorials and Materials

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

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Tutorials and Materials

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

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Tutorials and Materials

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:

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Tutorials and Materials

here what my .xsodata file looks like:

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Tutorials and Materials

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:

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Tutorials and Materials

Find the oData package and hit the right arrow:

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Tutorials and Materials

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

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Tutorials and Materials

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

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Tutorials and Materials

And you are now ready:

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Tutorials and Materials

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

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Tutorials and Materials

Your browser will open with the oData link:

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Tutorials and Materials

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

SAP HANA Study Materials, SAP HANA Learning, SAP HANA Certifications, SAP HANA Tutorials and Materials

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