In previous article Introduction to SAP Data Services
, we explained what SAP Data Service is and its main features.
In this article we would like show the procedure to load data from a source system to SAP HANA using SAP Data Services (SDS).
Do not get confused between SAP Data Service (SDS) and SAP BusinessObjects Data Service (BODS). They are more or less same. Refer to History of SAP Data Services
for more detail.
Major steps involved in loading data from source to HANA through SDS are:
- Create Data Store between Source and BODS
- Import the metadata (Structures) to BODS.
- Configure Import Server
- Import the metadata to HANA system.
- Create Data Store between BODS to HANA.
- Create Project.
- Define Job
- Define Work Flow
- Define Data Flow
- Add Object in Dataflow
- Execute the job
- Validate/Check the Data in HANA
Step 1: Create Data Store between Source and BODS:
- To Load data from SAP Source to SAP HANA through SAP BODS, we need a data Store. So we create a Data store first as shown below –
Project -> New - > Data Store
- A pop-up for Create new data store will appear, enter detail as below -
- Enter data store name "ds_ecc".
- Select Data store type name as "SAP Applications".
- Enter database server name
- User name and password.
- Click on "Apply" button.
- Click on "OK" button.
- Data Store will be created and view the created datastore as below-
- Go to Local Object Library
- Select DataStore tab.
- Data store "ds_ecc" will be displayed.
Step 2: Import Metadata (Structure) to BODS Server.
We have created a data store for ECC to BODS; now we import metadata from ECC into BODS. To import follow below steps -
- Select Datastore "ds_ecc" and right click.
- Select Import by Name option.
A pop-up for Import by Name will be displayed. Enter detail as below –
- Select Type as a table.
- Enter Name of Table, which we want to import. Here we are importing KNA1 table.
- Click On "Import" Button. KNA1 table will appear under table node of "ds_ecc" data source.
Table Metadata will be imported, in datastore ds_ecc as below –
Step 3: Configure Import Server
Till now we have imported table to data stored "ds_ecc" created for ECC to SAP BODS Connection. To import data into SAP HANA, we need to configure Import server,
To do this go to Quick View-> Configure Import Server as below
A pop-up for Select System will appear, Select SAP HANA (HDB here) System as below
Click on "Next" button. Another Pop-Up for data service credential will appear, enter following details
- SAP BODS server Address (here BODS:6400 )
- Enter SAP BODS Repository Name ( HANAUSER Repositery Name )
- Enter ODBC Data Source (ZTDS_DS).
- Enter Default port for SAP BODS server(8080).
Click on "Finish" button.
Step 4: Import the metadata to HANA System
Till now we have Configured Import Server, now we will import metadata from SAP BODS server.
- Click Import option in Quick View.
- A pop-up for Import option will be displayed. Select "Selective Import of Metadata" option.
Click on "Next "Button.
A pop-up for "Selective Import of Metadata" will be displayed, in which we select target System.
- Select SAP HANA System (HDB here).
Click on "Next" Button.
Step 5: Create Data Store between BODS and HANA
As we know, in BODS we need to create a separate datastore for source and target. We have already created a data store for the source, now we need to create a data store for the target (between BODS and HANA). So, we create a new data store with name "DS_BODS_HANA".
Go to Project -> New -> Datastore.
A screen for Create new Datastore will appear as below.
1. Enter Datastore name (DS_BODS_HANA).
2. Enter Datastore type as Database.
3. Enter Database type as SAP HANA.
4. Select Database Version.
5. Enter SAP HANA Database server name.
6. Enter Port name for SAP HANA Database.
7. Enter Username and password.
8. Tick on "Enable automatic data transfer".
Click on "Apply" and then "OK" button.
Data store "DS_BODS_HANA" will be displayed under datastore tab of Local Object Library as below-
Now we import table in data store "DS_BODS_HANA".
1. Select data store "DS_BODS_HANA" and right click.
2. Select Import By Name.
A pop-up for Import by Name will appear as be below-
1. Select Type as Table.
2. Enter Name as KNA1.
3. Owner will be displayed as Hanauser.
4. Click on Import Button.
Table will be imported in "DS_BOD_HANA" datastore, to view data in table follow below steps –
1. Click on table "KNA1" in data store "DS_BODS_HANA".
2. Data will be displayed IN TABULAR Format.
Step 6: Create Project
Project group and organize related objects. The Project can contain any number of jobs, Workflow, and dataflow.
1. Go to Designer Project menu.
2. Select new option.
3. Select Project option.
A POP-UP for New Project Creation appears as below. Enter Project Name and Click on Create Button. It will create a project folder in our case BODS_DHK.
Step 7: Define Job
A Job is a reusable object. It contains workflows and dataflow. Jobs can be executed manually or as a schedule. To Execute BODS Process we need to define the job.
We create a Job as JOB_Customer.
- Select Project (BODS_DHK) created in step 1, Right click and select "New Batch Job".
- Rename it to "JOB_Customer".
Step 8: Define Workflow
1. Select Job "JOB_Customer" in project area,
2. Click the workflow button on the tool palette. Click on the Black Workspace area. A workflow icon will appear in the workspace.
3. Change the name of the workflow as "WF_Customer".
Click the name of workflow, an empty view for the workflow appears in the workspace.
Step 9: Define Dataflow
1. Click On Workflow "WF_Customer".
2. Click the Dataflow button on the tool palette. Click on the Black Workspace area. A Dataflow icon will appear in the workspace.
3. Change the name of the Dataflow as "DF_Customer".
4. The Dataflow also appears in the project area on the left under job name.
Step 10: Add Object in Dataflow
Inside data flow, we can provide instruction to transform source data into the desired form for the target table.
We will see below object –
· An object for the source.
· An object for the target table.
· An object for Query transform. (Query transform maps the columns from source to target.)
Click on the dataflow DF_Customer . A blank workspace will appear as below -
- Specify object from Source – Go to Data store "ds_ecc " and Select table KNA1 and drag and drop to data flow blank screen as below screen-
- Specify object for Target- Select Data store "DS_BODS_HANA" from the repository and select table KNA1.
- Drag and drop to the workspace and select "Make Target "option. There will be two table for source and target. Here we will define the table as source and target.
- Query Transformation – This is a tool used to retrieve data based on input schema for user specific condition and for transport data from source to target.
- Select Query Transform icon from tool Palette, and drag and drop it between source and target object in workspace as below.
- Link Query object to Source.
- Link Query Object to Target table.
- Double Click On Query Icon. By this, we map a column from input schema to output schema.
- When we click on Query icon, a next window for mapping will appear, in which we do the following steps -
- Source Table KNA1 is selected.
- Select all column from the source table and right click and select a map to output.
- Target Output selected as Query, and column will be mapped.
- Save and Validate project.
- Click on validate Icon.
- A pop-up for validation success appear.
Step 11: Execute Job
To execute Job, follow the below path
- Select Project Area icon to open Project, and select created Project.
- Select Job and right click.
- Select Execute option, to execute Job.
After Executing Job, a Job Log window is displayed, in which all message regarding Job will be displayed.
The last message will be Job < > is completed successfully.
Step 12: Validate / Check Data in SAP HANA
- Login to SAP HANA database through SAP HANA Studio, and select HANAUSER schema.
- Select KNA1 table in Table node.
- Right Click on table KNA1 and Select Open Data Preview.
- Table (KNA1) Data loaded by BODS processes as above will be displayed in data preview screen.