SQL vs HANA Integration in Bods

Introduction:

This Blog post explains step by step procedure. How to Load data to the SQL source to SAP HANA through SAP BODS.

Step 1:

1. First Need to give connection between MS -SQL and SAP BODS because of we can import data easily from Source by giving connection.
2. Create Data store between SQL source and BODS

Load data to the SQL source to SAP HANA through SAP BODS, We need a data store so first we create a data store as shown in below.

Project ->New ->Data store

A Pop-up for create a new data store will appear as shown in below screenshot

◈ Enter Data store name DS_SQL_INT.

◈ Data store Type name as

◈ Enter Database server name, User Name, Pass word.

◈ Click on Apply button after that click on OK button.

Data store will be created and created Data store will be shown in below.

◈ Go to Local Object Library then select desire Data store tab.

◈ Data store DS_SQL_INT will be displayed as shown in below.

3. Import Metadata to BODS.

Already we have created a Data store for SQL BODS. After that we import metadata from SQL into BODS.

◈ Select Data store DS_SQL_INT and then right click after that click on Import by name option.

A dialogue box will be displayed Import by Name as shown in.

◈ Select Type as Table. Enter name of a table, which we want to import after that click on Import button as shown in above screen shot.

Metadata table will be imported, in Data store DS_SQL_INT as shown in below screen shot.

Step 2:

Configure Import Server

As of now we have imported table to Data store DS_SQL_INT created for SQL to SAP BODS connection.

◈ Now we have import data into SAP HANA, need to configure Import server.

1. Quick View->Configure Import Server as below

2.  New dialogue box for select system will come across, select SAP HANA (AID@DAS) system as shown below.

3. Click on “Next” button. Another pop-up for data service credential will appear,enter following particulars

◈ SAP BODS server address after enter SAP BODS Repository Name

◈ Enter ODBC data source after enter default port for SAP BODS server

Click on “Finish” button.

4. Import the metadata to HANA system

1. As of now we have configured Import Server, now import the metadata from SAP BODS                      server.

◈ Click import option in Quick View.

◈ A dialogue box for Import option will be displayed. Choose Selective Import of Metadata option

Click on “Next” button.

2. New dialogue box for Selective Import of Metadata will be displayed, in which we choose                 target system.

◈ Choose SAP HANA system (AID@DAS)

Click on “Next” button.

Step 3:

Create Data store between BODS and HANA

We are all know, in BODS we need to create a separate data store for source and target. We have   already created a data store for the source (between BODS and SQL), right now we need to create   a data store for the (between BODS and HANA). So, we need to create a new data store with name    DS_BODS_HANA_INT.

1. Project ->New ->Data store

2. A dialogue box for create a new data store will appear as shown in below screenshot.

◈ Enter Data store name DS_BODS_HANA_INT after that Enter Data store Type as Database Type.

◈ Enter Database Type as SAP HANA after that choose data base version.

◈ Enter HANA Database server name after that Enter Port name for SAP HANA database.

◈ Enter User Name and Password.

Click on “Apply” and then “OK” button.

3. Data store will be created and created Data store will be shown in below.

◈ Go to Local Object Library then choose desire Data store tab.

◈ Data store DS_BODS_HANA_INT will be displayed as shown in below.

4. Import Metadata to BODS.

Already we have created a Data store for HANA BODS. After that we import metadata from HANA into BODS.

◈ Select Data store DS_BODS_HANA_INT and then right click after that click on Import by name option.

A pop-up will be displayed Import by Name as shown in.

◈ Select Type as Table. Enter name of a table, which we want to import after that click on Import button as shown in above screen shot.

Metadata table will be imported, in Data store DS_BODS_HANA_INT as shown in below screen shot.

Step 4:

Load data to the SQL source to SAP HANA through SAP BODS.

1. First we need to create Project, JOB as JOB_SQL_VS_HANA and then we need to create Work Flow as WF_SQL_VS_HANA and then create Data Flow as DF_SQL_VS_HANA.

2. Drag Source table into data flow and then place query transformation with rename as QRY_EXTR and here we select all the columns from the schema in of the query transformation.

3. QRY_EXTR and here we select all the columns from the schema in of the query transformation and map to output to the schema out.

4. After that Save and Validate the project. And then click on validate button a pop-up will appear for validation success.

5. After that to execute job. Follow the below steps.

Select job and right click choose execute option, to execute job.

6. After executing job, a job log window is displayed.

7. Login to SAP HANA data base through SAP HANA Studio, And select HANA User schema.

Right click on table TARGET_EMP and then select open data preview.