A step by step guide for creating an OData Service on HANA Calculation Views – XSODATA | XSJS

This blog is a short tutorial where you will learn how to publish the HANA Calculation View as an OData service.

What we will be implementing

1. Creating a Simple Calculation View
2. Create an XS Project
3. Configuring xs access, to authenticate specific users or domains to access the exposed services.
4. Creating a Role and assigning select access to the Calculation view
5. Create XS OData
6. Adding a user in SQL Connections for the created Role.
7. Activating the user in SQL Connection Configuration
8. Using the XS Odata service

Let’s start the tutorial!

Step 1:  Create a Calculation View

1. Open HANA Studio and open Development Perspective.
2. Then Go to Systems tab and add your system.
3. Expand System and go to Content Folder.
4. Create a new package with any name in my case “Elvin.ExposedCV”
5. Right Click and Create New Calculation View
6. Add VBAK to Aggregation Node and Select Some Columns. In the View Properties make Default Client = Cross Client.
7. Activate and See if data is coming in the View or not.

Step 2: Create XSJS Project

1. Goto Project Explorer Tab.
2. Create a new XS Project. And Select your repository workspace.
   
3. Create Objects
   
4. Check the newly created project

Step 3: Configure .xsaccess file

There are many other things that we can configure in the below file like security, cache etc. It is currently out of scope for this blog so not covering here.

{
     "exposed" : true,                 
     "authentication" : null,
     "anonymous_connection": "ELVIN.ExposedCV.XSSPandCVDemo::anonymous",
     "cors" :                      
            {
             "enabled":true, 
             "allowMethods": ["GET","HEAD","OPTIONS","PUT"], 
             "allowOrigin"["whitelist urls"], 
             "maxAge":"3600"
            }
}

Authentication:  can be Form/ Basic/ Null. Where Basic can be used to call the rest api (XS OData Service) from HCP cockpit destination. In the above script we have set authentication to null to give anonymous access to the users accessing the services from the allowed https origin.

CORS is used to Allow Cross Origin Access. Here in the above script access to only one domain is allowed to call the API.

Allowed Methods: These allows the API to send data to backend and retrieve from it.

Exposed true specifies that the xsodata service will be exposed for use to outside world.

anonymous_connection: Here I have specified the role name which is going to have access to my catalog objects.

Note: This file can be activated only when all the steps from 4 to 7 are completed.

Step 4: Create Role for accessing the catalog objects

Right click on the project name in the project explorer and create a new file with name anonymous.hdbrole. Add the below code. Where you can replace Elvin.ExposedCV with your package name.

We need this to give access to calculation view and stored procedure that we created above. Here the role name has to be the same as the full name of the file.

To learn more about roles in HANA. You can see this blog.

anonymous1.hdbrole

role ELVIN.ExposedCV.XSSPandCVDemo::anonymous1{ 
catalog sql object "_SYS_BIC"."ELVIN.ExposedCV/EXPOSEVBAK":SELECT; 
catalog schema "_SYS_BI": EXECUTE;
catalog analytic privilege: "_SYS_BI_CP_ALL"; 
}

Here we are creating an anonymous role and giving him select access to the calculation views we want to expose as Rest APIs.

A basic analytic privilege is also added which is required to be able to select the view from SYS_BI schema.

Step 5: Create XS OData

Create a services folder. Inside it create our fist file.

services.xsodata : This is publishing our calculation view as an Odata service.

service namespace "sap.hana.xstest" {

    "_SYS_BIC"."ELVIN.ExposedCV/EXPOSEVBAK"

    as "salesdata" key("VBELN");

}

Step 6: Adding a user in SQL Connections for the created Role

Create an anonymous.xssqlcc file.

{
    "description" : "anonymous1",
    "role_for_auto_user" : "ELVIN.ExposedCV.XSSPandCVDemo::anonymous1"
}

When this file is activated an entry in the SQL_CONNECTIONS table in _SYS_XS is created.

You need to have SELECT permission on this table to view its data.

Step 7: Activating the user in SQL Connection Configuration

Now you have to activate the above user in HANA XS Admin.

For this, you need to have SQLCCAdministrator Role

Then open HANA XS Admin from URL.

{hostname}:{port}/sap/hana/xs/admin/

Select SQL Connection Configuration in filters and find anonymous file.

Then click on apply search.

You will see an anonymous role file.

Then click on the anonymous.xssqlcc and then activate it.

Once this is active. Go to HANA Studio and activate .xsaccess file.

Step 8: Using the XS Odata service

Right-click on xsodata service and Run AS XS service. It will open the URL in the internet explorer.

Add “/salesdata” to the URL to check the data from the Calculation view.

https://{hostname}:{port/ELVIN/ExposedCV/XSSPandCVDemo/services/services.xsodata/salesdata

Conclusion

You need to follow all the above steps to publish your HANA Calculation view to the OData service. Now you can add more to .xsaccess file to whitelist your endpoint URLs and also you can modify the roles and users’ access to make it a more secure connection.

If you like it, I appreciate your rating for this blog post! 🙂