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

[elvin_baghele]

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! 🙂

[sonamthote]

Very Helpful!!

[former_member629468]

Superb Elvin !!!

[gregorw]

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

[former_member590188]

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.

[elvin_baghele]

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

[former_member590188]

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)

[elvin_baghele]

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

[former_member550544]

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.

[rushikesh11]

Hi Elvin,

I created xs project, repository workspace, schema, XS javascript as per the steps.

When I opened. xsaccess file it is showing 'No connection to database'. Could you please help why it is showing like this. Is there anything I am missing?

Thanks in advance.

[elvin_baghele]

The error message "No connection to the database" in an .xsaccess file typically indicates that there is an issue establishing a connection to the underlying database. Here are a few steps to troubleshoot and address the issue:

1. Check Database Connection:
   
   
   
   • Ensure that the database server is running.
   
   
   • Verify the connection details (hostname, port, username, password) specified in your .xsaccess file. They should be correct and correspond to the database server you intend to connect to.
   
   
   • Make sure that the specified database user has the necessary permissions to connect to the database.
   
   
   
   


2. Check Database User Permissions:
   
   
   
   • Ensure that the database user specified in the connection details has the required permissions to access the database objects (tables, views, etc.) that your .xsjs script or XSJS service is trying to interact with.
   
   
   
   


3. Check Database Configuration in HANA Studio or Web IDE:
   
   
   
   • If you are using SAP HANA, you can use SAP HANA Studio or SAP Web IDE to check the connection configuration. Make sure that the connection details in your .xsaccess file match those in your development environment.
   
   
   
   


4. Check xsodata or .xsjs Service Configuration:
   
   
   
   • If your .xsaccess file is associated with an XSODATA service or XSJS script, ensure that the service or script definition has the correct database configuration.
   
   
   
   


5. Check for Syntax Errors:
   
   
   
   • Ensure that there are no syntax errors in your .xsaccess file. Even a small syntax error can prevent the database connection from being established.
   
   
   
   


6. Restart the Service or Application:
   
   
   
   • After making changes to the .xsaccess file, restart your XS project or service to apply the changes.
   
   
   
   


7. Review Logs and Error Messages:
   
   
   
   • Check the logs or error messages generated by your XS application for more details on why the database connection is failing. These logs can provide valuable information about the root cause of the issue.
   
   
   
   

If the issue persists after checking these points, you may need to provide more specific details about your XS project, .xsaccess file, and the context in which you are encountering the error for further assistance. Additionally, reviewing the documentation or seeking support from the SAP community for SAP HANA XS development can be helpful.

[rushikesh11]

hello,

how to set up a configuration as run as XS service?

Please guide.

Thanks in advance,

Rushikesh