Technical Articles
Bring your data from HANA Cloud to HANA On-prem via DP Agent
This feature is available and demonstrating how you can bring the data from BTP – SAP HANA Cloud Instance to SAP HANA Onpremise (SAP Commissions). It allows through DP Agent and managed instance to read data and execute commands against remote database source, for instance, HANA database server.
Using this feature, we can fetch data and update it in other databases without developing custom application.
Benefits
- Allow to access data from outside of SQL Servers
- It has the ability to perform distributed queries including select, update as well as other commands and transactions across heterogeneous data sources.
- It has the ability to address different databases similarly.
Prerequisites
- SAP BTP Cockpit – SAP HANA Cloud + WebIDE (database explorer)
- DP Agent Configured
- SAP Commissions – SAP HANA Onprem + WebIDE (database explorer)
High Level Flow
Data is moving from HANA Cloud to HANA Onprem via DP Agent and you can find the step by step explained below.
Let’s dive in SAP BTP – SAP HANA Cloud + WebIDE
Go to SAP BTP Cockpit – Select Service Marketplace – Select SAP HANA Cloud
Click Manage SAP HANA Cloud to check database status and launch WebIDE
Once you’re database is up and running,we are good to start
Go to Web IDE and follow the below steps to create a sample table
Create Table Demo_yoga (
ID INTEGER,
NAME VARCHAR(10),
PRIMARY KEY (ID)
);
Insert a sample data into a table created above
INSERT INTO Demo_yoga VALUES ('11','Yoga');
Let’s find out the table
select * from Demo_yoga
now, we can see table contains one row with dummy data in SAP HANA Cloud
Let’s dive in SAP Commissions SAP HANA On Prem + WebIDE
Follow the link for DP Agent Installation SAP Commissions – Smart Data Integration[SDI] – Part 3
you can go to your command prompt and your command path should be DP Agent.. run the CLI and select option 8 for Adapter Registration and select HanaAdapter
Once Adapter is registered sucessfully, now you can register for the Agent
Now you can login to your WebIDE and go to database explorer and expand the catalog to see Remote Source
Add Remote Source with all the information related to your Agent Registered and SAP HANA Cloud database credentials to link the server.
now you can see Remote Sources, you can find your source Name ( In my example, its HANA Cloud)
Go to your webIDE and try this below command to check if you’re getting result from SAP HANA Cloud database schema
CALL "PUBLIC"."GET_REMOTE_SOURCE_OBJECT_TREE"('HANA_Cloud','',?,?);
CALL GET_REMOTE_SOURCE_PROPERTIES ('HANA_Cloud', ?)
CALL PUBLIC.CHECK_REMOTE_SOURCE('HANA_Cloud');
now you can check Virtual table which will get the results from SAP HANA Cloud
select * from VT_yoga
As you can see from below screenshot, the results are pulled through Virtual table which is connected to SAP HANA Cloud table.
Thanks to Jon Cadby SPM Architect brought this PoC idea to reality for helping customers.
Final thoughts
we can bring the data by linking SAP HANA Cloud database server to HANA On-prem database.
Note : Also, you can get the data from SAP Commissions other environment tenant to Target environment through this process.. without exporting or importing the data manually.
Why using SDI&DPAgent when it is possible to connect HANA on-prem to HANA Cloud using SDA ODBC Driver directly like described here https://help.sap.com/docs/SAP_HANA_PLATFORM/6b94445c94ae495c83a19646e7c3fd56/275839492fef49318d92d0e31656ea0a.html ?
Thanks Michael Cocquerel for reading my blog !!
SAP Commissions runs on HANA On-prem and having restricted access to database connection. Only offers WebIDE for users. We have only way through this DP Agent.