Create low-code Cloud-driven OData Services fetching on-premise S/4HANA system

[LucaFalavigna]

I have a scenario where I want to create some OData Services, have them published in the Cloud so that they can be consumed from everywhere, and underlying data is fetched from an on-premise S/4HANA system without customizing its core.

It looks like extremely complex, but leveraging some powerful tools available in Business Technology Platform we can reach this goal with just a bit of configuration, a few elements from Cloud Application Programming Model, and great help from several resources linked in this post! 🙂

 

 

Scope

Although it's possible to access every table and even existing CDS views, to keep the example simple we will limit to create a simple SELECT OData to fetch data from TCURR table (Exchange Rates), with the following requisites:

• Select KURST, FCURR, TCURR, GDATU and UKURS fields


• Filter by TCURR equal to 'EUR'


• Filter by MANDT equal to '200'


• Convert GDATU from inverted date to proper date

 

 

Prerequisites

To complete the exercise, we need the following tools:

• On-premise SAP S/4HANA system


• User credentials on S/4HANA's database


• SAP Business Technology Platform (BTP) account (a trial one would suffice)


• Cloud Connector


• SAP Business Application Studio instance


• SAP HANA Cloud instance

 

 

Setting up SAP BTP instances

First of all, we need to make sure SAP BTP services and instances are up & running correctly before we can start using them. Here is what we need:

• Deploy SAP HANA Cloud


• Set up Business Application Studio


• Create a dev space in Business Application Studio

 

 

Connect on-premise S/4HANA to BTP

We need to connect the on-premise S/4HANA database to BTP to consume its data. We first need to connect the BTP subaccount to Cloud Connector. Finally, we need to create a new TCP connection towards the port defined in our on-premise HANA instance:

 

If the connection is established, we should be able to see the entry in the Destinations for our subaccount:

 

 

Access remote objects in HANA Cloud

We need to create a remote source to access tables found in the on-premise S/4HANA system. We start by creating a remote source towards the on-premise database. Then we can proceed by creating a virtual table, which can be derived from tables or views.

 

If we want to access existing CDS Views, it's important we pass the CDS_CLIENT session variable as part of the remote source definition this way:

CREATE REMOTE SOURCE <NAME> ADAPTER "hanaodbc"

CONFIGURATION 'ServerNode=<virtual_host_name>:<portnumber>;use_haas_socks_proxy=true;sessionVariable:CDS_CLIENT=200'

WITH CREDENTIAL TYPE 'PASSWORD' USING 'user=<username>;password=<password>';

 

 

Create objects in HANA Cloud

It's time to create a few objects in the HANA Cloud instance to access the shared resources from within Business Application Studio and SAP BTP.

 

We start by creating a database schema, for simplicity we can use the same name as the virtual HANA host defined previously in Cloud Connector:

CREATE SCHEMA shd;

 

Then, we create an user:

CREATE USER onpremise_grantor PASSWORD <password> NO FORCE_FIRST_PASSWORD_CHANGE SET USERGROUP DEFAULT;

 

Then, we create two roles. The first is used to grant privileges, the second one is used at runtime by Business Application Studio and BTP to access the objects:

CREATE ROLE "shd::external_access_grant";

CREATE ROLE "shd::external_access";

 

Finally, we bind everything together:

GRANT SELECT, SELECT METADATA ON SCHEMA shd TO "shd::external_access_grant" WITH GRANT OPTION;

GRANT SELECT, SELECT METADATA ON SCHEMA shd TO "shd::external_access";

GRANT "shp::external_access_grant", "shp::external_access" to onpremise_grantor WITH ADMIN OPTION;

 

 

Connect to BTP services

We need to create a User-Provided Service in the Cloud Foundry space where the HANA Cloud instance is running. It will be used to bind the user we created in HANA Cloud within Business Application Studio.

JSON parameter should be like the following:

{

  "user": "onpremise_grantor",

  "password": "<password>",

  "schema": "SHD",

  "tags": ["hana"]

}

 

The final configuration would look like below:

 

Then, we need to create an HDI container for the HANA Cloud instance (up to and including point 9).

Finally, we need to bind the User-Provided Service we created above in Business Application Studio. To do so, we need to adjust mta.yaml file in our project root folder to include the following snippet under the resources section:

 - name: OnPremise-SHD

   type: org.cloudfoundry.existing-service

   parameters:

     service-name: OnPremise-SHD

 

We also need to add the service under the requires section in the OData-db-deployer module:

- name: OnPremise-SHD

 

These changes should enable a new database connection we can bind to by clicking on the corresponding icon:

 

When asked, select the User-Provided Service we created, and after a few seconds binding should be connected:

 

 

Create HANA artifacts in Business Application Studio project

Before creating our first HANA artifact, we need to integrate the project with the CAP service layer (up to and including point 5).

 

First, we create a Grants. From View --> Find Command... type and select SAP HANA: Create SAP HANA Database Artifact and proceed with a Grants artifact:

 

Let's open the newly created file with Code Editor and store the following content:

{

    "OnPremise-SHD": {

        "object_owner": {

            "roles": [

                "shd::external_access_grant"

            ]

        },

        "application_user": {

            "roles": [

                "shd::external_access"

            ]

        }

    }

}

 

Then, following the same procedure as the artifacts above, we create a synonym which is required to expose the virtual table we created on HANA Cloud inside the HCI container to actually consume the data:

 

Let's open the newly created file with Synonym Editor and look for our data source:

 

Then, we should be able to see our table in the Synonym Editor:

 

After deploying our changes, we should be able to see the new synonym in HANA Database Explorer, and we should be able to preview our data from the on-premise system:

 

Now we can create a calculation view, which will be the foundation of our OData Service. We proceed creating a calculation view of type Dimension. It's important artifact name has a structured name like ONPREMISE_VIEW_TABLENAME, it will be important when creating the CDS elements later:

 

Open the newly created file with Calculation View Editor and add the TCURR table we just made available through a synonym by clicking on the plus symbol which is displayed clicking on the Projection box. Then we can proceed mapping the columns we need:

 

In our requirements, we need to filter by a specific client and currency, we can do that in the Filter Expression tab:

 

Then, we need to convert the date column, which is a inverted date string, into a proper date. We can do so by using a calculated column:

 

Finally, we can hide the MANDT and GDATU columns from the Semantics box because are not useful for our view:

 

After deploying our changes, we should be able to see the new view in HANA Database Explorer, and we should be able to preview our data from the on-premise system in the way we defined:

 

 

Create CAP elements

Now that all the database objects are created, we need to create the CAP elements to consume data from an OData Service.

 

We start creating OnPremise.cds file under db folder (not under db/src). Let's store the following content:

namespace OnPremise;



context View {



    @cds.persistence.exists

    entity TCURR {

        key RATE_TYPE : String(4);

        key FROM_CURRENCY :  String(5);

        key TO_CURRENCY : String(5);

        key DATE : Date;

        RATE : Decimal;

    }



}

Here we define the structure of the CDS view we want to create with our fields, keys and types. Given we created the calculation view already, it's important we use the cds.persistence.exists annotation to instruct Business Application Studio and HANA Cloud the object is already created and therefore they should not try to create it a second time, leading to an error. Although CAP will not not be responsible of creating the actual database artifact, we need to create this element anyway to make it aware of the existence of our calculation view, otherwise it cannot be consumed from the OData Service.

We named our calculation view ONPREMISE_VIEW_TCURR, and this name is exploded in the OnPremise.cds file in the namespace, context and entity labels. These labels must match the calculation view name, otherwise CAP will not be able to find the underlying HANA Cloud artifact!

 

Now we can proceed creating the service definition by creating OnPremise_srv.cds file under srv folder. Let's store the following content:

using OnPremise from '../db/OnPremise';



service OData {



 entity TCURR

	as projection on OnPremise.View.TCURR;



};

Here we simply link the OnPremise.cds file and create the service based on that calculation view.

 

All the pieces of the puzzle are now placed, so we need to raise the following command:

cds build

This will build the CAP elements into the OData Service. After a final deployment, we need to run the following command:

npm start

 

Business Application Studio will prompt us with the possibility to open the service in another browser tab, where we can test the service:

From here, we can place our first OData query:

 

 

Create app to consume data

Now that the OData Service is working fine, we could decide to go further by creating a Fiori/UI5 app, or pushing the service in Cloud Foundry as a Node.js app to consume the service from 3rd party services to consume our data.

 

 

Conclusions

After setting up our platform, it will become easier to add new tables or views to our service, creating calculation views with low-code tools and without touching our on-premise S/4HANA system, allowing to reduce custom code footprint in prevision of a system upgrade or to enable power users to create their own calculation views without the need of ABAP / Basis developers.

 

 

Credits & References

• Remote Table Replication


• Combine CAP with SAP HANA Cloud to Create Full-Stack Applications


• HANA Cloud: Access Schema from HDI Container