Technical Articles
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
- Creating a Simple Calculation View
- Create an XS Project
- Configuring xs access, to authenticate specific users or domains to access the exposed services.
- Creating a Role and assigning select access to the Calculation view
- Create XS OData
- Adding a user in SQL Connections for the created Role.
- Activating the user in SQL Connection Configuration
- Using the XS Odata service
Let’s start the tutorial!
Step 1: Create a Calculation View
- Open HANA Studio and open Development Perspective.
- Then Go to Systems tab and add your system.
- Expand System and go to Content Folder.
- Create a new package with any name in my case “Elvin.ExposedCV”
- Right Click and Create New Calculation View
- Add VBAK to Aggregation Node and Select Some Columns. In the View Properties make Default Client = Cross Client.
- Activate and See if data is coming in the View or not.
Step 2: Create XSJS Project
- Goto Project Explorer Tab.
- Create a new XS Project. And Select your repository workspace.
- Create Objects
- 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! 🙂
Very Helpful!!
Superb Elvin !!!
Hi Elvin,
in 2020 I would suggest to use SAP Cloud Application Programming Model (CAP) which has support for Using Native SAP HANA Artifacts instead of XSOData.
Best regards
Gregor
Hi Elvin,
I am facing error in .xsaccess file on cors statement. Below is my code snippet.
Please suggest.
anonymous.hdbrole
anonymous.xssqlcc
These files are activated successfully.
Hi @Sanketh Teegala,
As the error description said. Your JSON is invalid.
You have missed ":" after "allowOrigin".
To validate your JSON you can use online tools like https://jsonformatter.curiousconcept.com/.
BR,
Elvin Baghele
Thanks Elvin, it worked.
Also want to highlight anonymous.hdbrole and anonymous.xssqlcc files should have role name as anonymous instead of anonymous1 otherwise it will activation error
Error Message -
Message :
Syntax error: role name "Himanshu.ExposedCV.XSSPandCVDemo::anonymous1" must be identical to the file name "Himanshu.ExposedCV.XSSPandCVDemo::anonymous" (without extension)
Thanks, Sanketh
As the error says in the above blog post, filename needs to be the same as the role name i.e anonymous1.hdbrole (corrected above). It was a typo error.
In the .xsaccess file, you should mention the connection name, which is the xssqlcc file name.
And we add the role information under the SQL connection configuration file (xssqlcc). Here our role name is annonymous1.
Then we add the objects in hdbrole file; Objects which we want to give access to the anonymous connection.
If you will notice the select statement and its output. You can see that the connection name is anonymous but role name is anonymous1.
BR
Elvin Baghele
Hi Elvin,
Nice blog. How to transport XS Project/ XS OData from Dev to other environments? I have created an XS Project, collected in a change id but I'm unable to assign the objects to a Delivery Unit.
How to assign delivery units to these objects so that it can be transported.
Appreciate any help!
Thanks.