SAP DS (Data Services) in HANA

What is SAP Data services?

SAP Data services is an ETL tool which gives a single enterprises level solution for data integration, Transformation, Data quality, Data profiling and text data processing from the heterogeneous source into a target database or data warehouse.

We can create applications (job) in which data mapping and transformation can be done by using the Designer. (Latest version of SAP BODS is 4.2).

Features of Data Services

• It provides high-performance parallel transformations.
• It has comprehensive administrative tools and reporting tool.
• It supports multi-users.
• SAP BODS is very flexible with web-service based application.
• It allows scripting language with rich sets of functions.
• Data Services can integrate with SAP LT Replication Server (SLT) , with the Trigger-based technology. SLT adds delta-capabilities to every SAP or non-SAP source table, Which allows for using data capturing for change and transferring the delta data of the Source table.
• Data validation with dashboards and process auditing.
• Administration tool with scheduling capabilities and monitoring/dashboards.
• Debugging and built-in profiling and viewing data.
• SAP BODS support Broad source and target.
• Any Applications (e.g. SAP).
• Any Databases with bulk loading and changes data capture
• Files: fixed width, comma delimited, COBOL, XML, Excel.

Component of Data Services

SAP DATA services have below component –

1. Designer – It is a development tool by which we can create, test, and execute a job that populates a data warehouse.It allows the developer to create objects and configure them by selecting an icon in a source-to-target flow diagram.It can be used to create an application by specifying workflows and data flows.To Open Data Service Designer go to Start Menu -> All Programs -> SAP Data Services (4.2 here) -> Data Service Designer.

2. Job Server- It is an application that launches the data services processing engine and serves as an interface to the engine and Data Services Suite.
3. Engine- Data Service engine executes individual jobs which are defined in the application.
4. Repository- Repository is a database that stores designer predefine objects and user defined objects (source and target metadata, transformation rules).Repository are of two types –

• Local Repository (Used by Designer and Job Server).
• Central Repository ( Used for object sharing and version control)

5. Access Server- Access server passes messages between web applications, data services job server and engines.
6. Administrator- Web Administrator provides browser-based administration of data services resources detail is as below –

• Configuring, starting and stopping real-time services.
• Scheduling, monitoring and executing batch jobs.
• Configuring Job Server, Access Server, and Repository usage.
• Managing users.
• Publishing batch jobs and real-time services via Web services
• Configuring and managing adapters.

Data Services Architecture –

Data Services architecture have the following component-

• Central Repository – it is used for repositories configurations to jobs servers, security management, version control and object sharing
• Designer – Used for Create Project, Job, workflow, data flow and run.
• Local repository (here you could create change and start jobs, Workflow, dataflow).
• Job server & engine – It manage the jobs.
• Access Server – It is used to execute the real-time jobs created by developers in the repositories.

In below image, Data Services and there component relationship is shown.

SAP BODS Architecture

Designer Window Detail: First we look into the first component of SAP data service- Designer

Detail of each section of data service designer is as below-

1. Tool Bar (Used for Open, Save, Back, Validate, Execute, etc.).
2. Project Area (Contains the current project, which includes Job, Workflow, and Dataflow. In Data Services, all entities are objects.
3. Work Space (The Application Window area in which we define, display, and modify objects).
4. Local Object Library (It contains local repository objects, such as transforms, job, workflow, dataflow, etc.).
5. Tool Palette (Buttons on tool palette enable you to add new objects to the workspace).

Object Hierarchy

The below diagram shows hierarchical relationships for the key object types within Data Services.

>

Note:

Workflows and Conditional* are optional

Objects used in SAP Data services detail is as below –

Objects	Description
Project	A project is a highest-level object in the Designer window. Projects provideyou with a way to organize the other objects you create in Data Services.Only one project is open at a time (where “open” means “visible in the projectarea”).
Job	A “job” is the smallest unit of work that you can schedule independently forexecution.
Scripts	A subset of lines in a procedure.
Workflow	A “work flow” is the incorporation of several data flows into a coherentflow of work for an entire job. Workflow is optional. Workflow is a procedure. Call data flows Call another work flow Define the order of steps to be executed in your job Pass parameters to and from data flows. Specify how to handle errors that occur during execution. Define conditions for executing sections of the project.
Dataflow	A “data flow” is the process by which source data is transformed intotarget data. A data flow is a reusable object. It is always called from a work flow or a job. Identify the source data that you want to read. Define the transformations that you want to perform on the data. Identify the target table to which you want to load data.
Datastore	Logical channel that connects Data Services to source and Datastore target databases. Must be specified for each source and target database. Are used to import metadata for source and target databases into therepository Are used by Data Services to read data from source tables and load datato target tables
Target	Table or file in which Data Services loads data from the source.

Data Services example by load data from SAP Source Table

Everything in Data services is an object. We need to separate data store for each source and target database.

Steps for loading data from SAP source table – SAP BODS have many steps, in which we need to create a data store for source and target and map to them.

• 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.
• Create Job (Batch/Real time)
• Create Work Flow
• Create Data Flow
• Add Object in Dataflow
• Execute the job
• Check the Data Preview in HANA

Step 1) Create Data Store between SAP Source and BODS

1. 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

   

2. A pop-up for Create new data store will appear, enter detail as below –
   1. Enter data store name “ds_ecc”.
   2. Select Data store type name as “SAP Applications”.
   3. Enter database server name
   4. User name and password.
   5. Click on “Apply” button.
   6. Click on “OK” button.

   

3. Data Store will be created and view the created datastore as below-

1. Go to Local Object Library
2. Select DataStore tab.
3. 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 –

1. Select Datastore “ds_ecc” and right click.
2. Select Import by Name option.

   

   A pop-up for Import by Name will be displayed. Enter detail as below –

   1. Select Type as a table.
   2. Enter Name of Table, which we want to import. Here we are importing KNA1 table.
   3. 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,

1. To do this go to Quick View-> Configure Import Server as below –

   

2. A pop-up for Select System will appear, Select SAP HANA (HDB here) System as below-

3. Click on “Next” button. Another Pop-Up for data service credential will appear, enter following details

1. SAP BODS server Address (here BODS:6400 )
2. Enter SAP BODS Repository Name ( HANAUSER Repositery Name )
3. Enter ODBC Data Source (ZTDS_DS).
4. Enter Default port for SAP BODS server(8080).

Click on “Finish” button.

Step 4) Import the metadata to HANA System

1. Till now we have Configured Import Server, now we will import metadata from SAP BODS server.

1. Click Import option in Quick View.
2. A pop-up for Import option will be displayed. Select “Selective Import of Metadata” option.

Click on “Next “Button.

2. A pop-up for “Selective Import of Metadata” will be displayed, in which we select target System.

1. 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”.

1. Go to Project -> New -> Datastore.

2. 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-

3. 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.

4. 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) Define 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.

1. Select Project (BODS_DHK) created in step 1, Right click and select “New Batch Job”.

   

2. 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 –

  

1. 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-
2. Specify object for Target- Select Data store “DS_BODS_HANA” from the repository and select table KNA1.
3. 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.

4. 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.
   1. Select Query Transform icon from tool Palette, and drag and drop it between source and target object in workspace as below –
   2. Link Query object to Source.
   3. Link Query Object to Target table.

   

   4. 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 –

1. Source Table KNA1 is selected.
2. Select all column from the source table and right click and select a map to output.
3. Target Output selected as Query, and column will be mapped.

5. Save and Validate project.

1. Click on validate Icon.

2. A pop-up for validation success appear.

Step 11) Execute Job – To execute Job, follow the below path-

1. Select Project Area icon to open Project, and select created Project.
2. Select Job and right click.
3. Select Execute option, to execute Job.

   

   1. After Executing Job, a Job Log window is displayed, in which all message regarding Job will be displayed.
   2. The last message will be Job < > is completed successfully.

   

   Step 12) – Validate / Check Data in SAP HANA Database.

   1. Login to SAP HANA database through SAP HANA Studio, and select HANAUSER schema.
   2. Select KNA1 table in Table node.
   3. Right Click on table KNA1 and Select Open Data Preview.
   4. Table (KNA1) Data loaded by BODS processes as above will be displayed in data preview screen.