This blog helps to replicate data from HANA on-premise system to HANA cloud using Smart Data Integration. And also explains creating Flowgraphs using Business Application Studio for data transformation.
Let us understand below concepts:
SAP HANA Cloud :
SAP HANA Cloud is a fully managed in-memory cloud database as a service (DBaaS). As the cloud-based data foundation for SAP Business Technology Platform, it integrates data from across the enterprise, enabling faster decisions based on live data.
Smart Data Integration:
SAP HANA smart data integration is an ETL tool, which can be used to load data( either in batch or real-time) in HANA (on premise or in the cloud) from different sources by using pre-built and custom adapters.
Data Provisioning Agent :
The Data Provisioning Agent manages all SDI Adapters and connections to HANA database. It acts as the communication interface between HANA and the Adapter. The DP Agent version must match with the HANA version. Different types of adapters are deployed, these adapters are used to make communication between the HANA database and the source systems.
Now lets see how to access data from HANA on-premise to HANA cloud.
Log onto HANA cloud system and run the below script commands from SQL console.
CREATE SCHEMA “TESTSCHEMA”;
CREATE USER TESTUSER PASSWORD “Testdemo7”;
ALTER USER TESTUSER DISABLE PASSWORD LIFETIME;
CREATE ROLE TESTROLE;
grant SELECT, UPDATE, INSERT, DELETE, EXECUTE, SELECT METADATA ON SCHEMA “TESTSCHEMA” TO TESTROLE with grant option;
grant TESTROLE to TESTUSER with admin option;
grant “CREATE VIRTUAL TABLE”, “DROP”, “CREATE REMOTE SUBSCRIPTION”, “PROCESS REMOTE SUBSCRIPTION EXCEPTION” on remote source “Z_TEST_DEMO” to TESTROLE with grant option;
Remote Data Source Creation:
Create Remote Data Source in HANA Cloud system as shown:
Log onto HANA DB explorer, connect to HANA cloud system, Open Catalog, choose Remote Source and click on Add remote source. In the pop-up, enter remote source details to be created.
![](https://i0.wp.com/www.erpqna.com/wp-content/uploads/2021/09/SDI1.jpg?resize=405%2C339&ssl=1)
Enter Source Name, Adapter Name, Select Source Location( Agent Details), Host, Port number, Log in credentials ( for HANA on-premise).
![](https://i0.wp.com/www.erpqna.com/wp-content/uploads/2021/09/SDI2.jpg?resize=730%2C655&ssl=1)
Once remote source is created, we will be able to view HANA On-premise system Table details.
Development in Business Application Studio:
Log onto Business application studio and follow below steps.
Create HANA DB Project.
To create HANA DB project from Business application studio, please refer to below URL
https://developers.sap.com/tutorials/hana-cloud-create-db-project.html
Create cross schema service as shown below:
Click on SAP HANA Projects.
Select the project which you have created in the previous step.
Goto Database Connections and click on + symbol.
![](https://i0.wp.com/www.erpqna.com/wp-content/uploads/2021/09/SDI3.jpg?resize=729%2C164&ssl=1)
![](https://i0.wp.com/www.erpqna.com/wp-content/uploads/2021/09/SDI4.jpg?resize=603%2C121&ssl=1)
Enter below details to add Data Base connection.
![](https://i0.wp.com/www.erpqna.com/wp-content/uploads/2021/09/SDI5.jpg?resize=406%2C466&ssl=1)
Now we can see added cross container service as shown:
![](https://i0.wp.com/www.erpqna.com/wp-content/uploads/2021/09/SDI6.jpg?resize=687%2C188&ssl=1)
Create .hdbgrants file.
Open HANA DB project->src folder
Goto view->Find command-> SAP HANA :Create SAP HANA Database Artifact
![](https://i0.wp.com/www.erpqna.com/wp-content/uploads/2021/09/SDI7-1.jpg?resize=453%2C71&ssl=1)
Select SAP HANA: Create SAP HANA Database Artifact
Navigates to below screen:
Enter details as shown below and click on Create button.
![SAP HANA, SAP HANA Cloud](https://i0.wp.com/www.erpqna.com/wp-content/uploads/2021/09/SDI8.jpg?resize=343%2C509&ssl=1)
Write the below logic in .hdbgrants file:
{
“CC_SDITEST”: {
“object_owner”: {
“global_object_privileges”: [
{
“name”: “Z_TEST_DEMO”,
“type”: “REMOTE SOURCE”,
“privileges”: [
“CREATE VIRTUAL TABLE”,
“DROP”,
“CREATE REMOTE SUBSCRIPTION”
]
}
],
“roles”: [
“TESTROLE”
]
},
“application_user”: {
“roles”: [
“LKMROLE”
]
}
}
}
Click on below deploy button & deploy the file.
![SAP HANA, SAP HANA Cloud](https://i0.wp.com/www.erpqna.com/wp-content/uploads/2021/09/SDI9.jpg?resize=460%2C105&ssl=1)
Create Virtual table as shown:
![SAP HANA, SAP HANA Cloud](https://i0.wp.com/www.erpqna.com/wp-content/uploads/2021/09/SDI10.jpg?resize=492%2C882&ssl=1)
It provides the popup as shown and gives remote source which we have chosen in the previous steps.
![SAP HANA, SAP HANA Cloud](https://i0.wp.com/www.erpqna.com/wp-content/uploads/2021/09/SDI11.jpg?resize=730%2C403&ssl=1)
It provides all the details from the remote source system.
Select the table from remote system and click on Next button.
![SAP HANA, SAP HANA Cloud](https://i0.wp.com/www.erpqna.com/wp-content/uploads/2021/09/SDI12-1024x570.jpg?resize=730%2C406&ssl=1)
Provide Object name(Virtual Table name) in the next page and click on Finish button.
![SAP HANA, SAP HANA Cloud](https://i0.wp.com/www.erpqna.com/wp-content/uploads/2021/09/SDI13-1024x573.jpg?resize=730%2C408&ssl=1)
It creates the virtual table as shown below:
![SAP HANA, SAP HANA Cloud](https://i0.wp.com/www.erpqna.com/wp-content/uploads/2021/09/SDI14-1024x338.jpg?resize=730%2C241&ssl=1)
Deploy the object.
To view the object from HANA DB explorer, click on Open HDI container as shown below:
![SAP HANA, SAP HANA Cloud](https://i0.wp.com/www.erpqna.com/wp-content/uploads/2021/09/SDI15-1024x226.jpg?resize=730%2C161&ssl=1)
It navigates to HANA DB explorer, here we can view HDI Container.
![SAP HANA, SAP HANA Cloud](https://i0.wp.com/www.erpqna.com/wp-content/uploads/2021/09/SDI16.jpg?resize=506%2C487&ssl=1)
Click on tables to view Virtual table created in previous step.
![SAP HANA, SAP HANA Cloud](https://i0.wp.com/www.erpqna.com/wp-content/uploads/2021/09/SDI17.jpg?resize=511%2C529&ssl=1)
Click on table to view data.
Create flow graph as shown below:
![SAP HANA, SAP HANA Cloud](https://i0.wp.com/www.erpqna.com/wp-content/uploads/2021/09/SDI18.jpg?resize=676%2C628&ssl=1)
Flowgraph is created as shown below:
![SAP HANA, SAP HANA Cloud](https://i0.wp.com/www.erpqna.com/wp-content/uploads/2021/09/SDI19.jpg?resize=476%2C404&ssl=1)
Right click on .hdbflowgraph and Open with Flowgraph Editor.
![SAP HANA, SAP HANA Cloud](https://i0.wp.com/www.erpqna.com/wp-content/uploads/2021/09/SDI20.jpg?resize=730%2C113&ssl=1)
It opens as shown below
![SAP HANA, SAP HANA Cloud](https://i0.wp.com/www.erpqna.com/wp-content/uploads/2021/09/SDI21.jpg?resize=730%2C268&ssl=1)
Click on + it opens pop-up as shown.
![SAP HANA, SAP HANA Cloud](https://i0.wp.com/www.erpqna.com/wp-content/uploads/2021/09/SDI22.jpg?resize=730%2C453&ssl=1)
Click on Data Source.
![SAP HANA, SAP HANA Cloud](https://i0.wp.com/www.erpqna.com/wp-content/uploads/2021/09/SDI23.jpg?resize=600%2C462&ssl=1)
Click on Settings and it opens as shown below
![SAP HANA, SAP HANA Cloud](https://i0.wp.com/www.erpqna.com/wp-content/uploads/2021/09/SDI24.jpg?resize=730%2C410&ssl=1)
Click on HANA Object and in pop-up enter details.
Search with Virtual table created in previous step, select it and click on finish button.
![SAP HANA, SAP HANA Cloud](https://i0.wp.com/www.erpqna.com/wp-content/uploads/2021/09/SDI25.jpg?resize=730%2C493&ssl=1)
Now click on Apply button.
Create one more data source in the flow graph pointing to the VT_SD_PRODUCT_SALES
( i have created one more virtual table)
Click on + in Flow graph editor and select Join as shown below:
![SAP HANA, SAP HANA Cloud](https://i0.wp.com/www.erpqna.com/wp-content/uploads/2021/09/SDI26.jpg?resize=572%2C556&ssl=1)
Now map the table to Join node as shown.
![SAP HANA, SAP HANA Cloud](https://i0.wp.com/www.erpqna.com/wp-content/uploads/2021/09/SDI27.jpg?resize=502%2C317&ssl=1)
Click on settings in Join node.
Goto Criteria and make the Join condition as shown:
![SAP HANA, SAP HANA Cloud](https://i0.wp.com/www.erpqna.com/wp-content/uploads/2021/09/SDI28.jpg?resize=730%2C379&ssl=1)
Create Data Target as shown:
![SAP HANA, SAP HANA Cloud](https://i0.wp.com/www.erpqna.com/wp-content/uploads/2021/09/SDI29.jpg?resize=461%2C452&ssl=1)
Map Join_OUT to target as shown:
![SAP HANA, SAP HANA Cloud](https://i0.wp.com/www.erpqna.com/wp-content/uploads/2021/09/SDI30.jpg?resize=730%2C352&ssl=1)
Click on Data Target setting to view output.
![SAP HANA, SAP HANA Cloud](https://i0.wp.com/www.erpqna.com/wp-content/uploads/2021/09/SDI31.jpg?resize=730%2C403&ssl=1)
Click on Apply button and deploy the Flowgraph. Click on Execute button and in the pop-up click
on OK button.
![SAP HANA, SAP HANA Cloud](https://i0.wp.com/www.erpqna.com/wp-content/uploads/2021/09/SDI32.jpg?resize=730%2C191&ssl=1)
Post successful deployment, you can see below message.
![SAP HANA, SAP HANA Cloud](https://i0.wp.com/www.erpqna.com/wp-content/uploads/2021/09/SDI33.jpg?resize=730%2C89&ssl=1)
Now go to HANA DB explorer and open procedures.
You can see the deployed Flowgraph under procedures.
![SAP HANA, SAP HANA Cloud](https://i0.wp.com/www.erpqna.com/wp-content/uploads/2021/09/SDI34.jpg?resize=344%2C482&ssl=1)
Goto tables to view Data Target.
Right Click on Data Target to view data.
![SAP HANA, SAP HANA Cloud](https://i0.wp.com/www.erpqna.com/wp-content/uploads/2021/09/SDI35.jpg?resize=314%2C539&ssl=1)
Result:
Data as shown below:
![SAP HANA, SAP HANA Cloud](https://i0.wp.com/www.erpqna.com/wp-content/uploads/2021/09/SDI36.jpg?resize=730%2C42&ssl=1)