Technical Articles
Create low-code Cloud-driven OData Services fetching on-premise S/4HANA system
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
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"]
}
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
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).
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"
]
}
}
}
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.
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:
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.