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.