In this Blog, I am going to show “How to” create a Flowgraphs in HANA Web IDE with SDI (Smart Data Integration) connection.
SAP HANA flowgraph helps you to transform data from a remote source (SDI) into SAP HANA either in batch or real-time mode. In this case, I am using Hadoop database as remote source and loading the data into HANA Database tables.
Below are the step-by-step guide on How to create a Flowgraph:
1) First make sure the Basis/Administration team created the SDI connection with remote source for Hadoop DB in Hana Studio with all the information like Adapter Name, Source location and all the required connection details as per below screenshot.
2) Make sure to check the connection by clicking the Test connection button. If the connection successful, you will see message : Connection to remote system established.
3) Before building the Flowgraph, you need to create a Virtual table based on Hadoop source table.
Login into Hana environment -> Catalog -> Your Schema ->Tables -> right click and select “New Virtual Table”.
4) Click on the Browse button to select the source connection and table you want to be loaded. In this I selected ods_delivery_header table and click OK.
5) After you select the table, you can see all the table definition can be viewed under columns tab. Also you can change the virtual table name as you like and also you can select the schema where you want to save this virtual table. In this case, I named virtual table as DELIVERY_HEADER under my schema and click the Save button.
6) Now the newly created virtual table is created under your schema. Go to your schema -> Tables. You can see the Virtual table. You see the connects of the table and test if the connection works fine, you can click on Open Data Preview option.
7) Now can see the data in the virtual table. Remember this data is not loaded into Hana yet, this is just viewing the data virtually from the remote source.
8) Now login into Hana Web IDE -> Contents -> Your schema, right click on you schema -> New -> Flow Graph.
9) Give Flowgraph Name, In this case, I mentioned as ETL_DEL_HEADER and click Create button.
10) Click on the properties button.
11) In the Flowgraph properties, you can select Target schema to create the Hana DB table and also you can select the Runtime type as Batch or real-time load. Also you can create a Variables.
In this case, I have selected the runtime type as Batch Task and click OK.
12) In the Flowgraph editor -> In palette general -> drag the Datasource into the editor layout and select the virtual table which you created under your schema. and click OK.
13) Now Drag Template Table as target object into layout and link the source and target objects as show below.
14) If you want to use more transformations to the data, you can perform by using the Palette “General” and “Data Provisioning”.
On the right hand side of the editor is a palette and depending on the features being installed in Hana, it shows different categories. If the smart Data Integration and Smart Data quality feature is installed, there are two categories.
15) But In this case, I am going to create a straight forward flowgraph which loads data from Hadoop tables into Hana DB table.
Double Click on the Template Table and change the Node name to TAB_DEL_HEADER in 2 places as shown below and also you can select the Authorizing schema to save the Hana DB table in particular schema and click Back button.
TAB_DEL_HEADER will be the Hana DB table name which will be saved under my schema.
16) Click on Save button. Now the Flowgraph is saved.
17) In order to test the Flowgraph, you can click on execute button for this flowgraph to test if the data is loading into Hana DB table TAB_DEL_HEADER.
18) After you click on the Execute button, you can see message saying the Flowgraph execution is started.
19) If the data load is taking longer time, it will give you a message saying you can check the status of the loading in the Task Monitor UI interface.
So in order to check the jobs status of this flowgraph, you can login into Task Monitor for this system and check your job.
You can see the job is in running Status.
After sometime, you can see the job status is Completed.
If you have the proper security access, you can schedule the job periodically as well from Task Monitor UI interface.
20) Now go back to Hana Studio and go to Catalog -> your schema -> Refresh the Table.
You can see the newly created DB table TAB_DEL_HEADER will be saved. You can select the Data preview to view the data inside the Hana DB table.
Hope this blog is helpful to you!!