Data loads to SAP HANA DB using SAP DS (Data Services)

In this Blog I’m going explain the data loading process to SAP HANA DB using SAP ECC as Source System.

SAP Data services:

SAP Data services is an ETL tool which gives a single enterprise level solution for data Integration, Transformation, Data quality, Data profiling and Text data processing from different source systems into a target DB.

SAP Data services Features:

• Single development user interface
• Metadata repository
• Data connectivity layer
• Run-time environment
• Management Console

Once we login to DS and open Designer layout, we can see the following elements:

Menu bar across the top, Tool bar under the menu bar, Project Area, Local Object Library with following tabs.

• Projects : Use the project area pane to create and manage projects, to monitor job executions, and to view job execution logs.
• Jobs : Job is the only object that you execute. A job contains all other subordinate objects in hierarchical order.
• Work flows : A work flow defines the decision-making process for executing data flows.
• Data flows : A data flow is the process by which the software transforms source data into target data.
• Transforms : Transforms operate on data sets by manipulating input sets and producing one or more output sets.
• Datastores : Datastores contain connection configurations to databases and applications that contain your data.
• Formats : Format is a set of properties that describe the metadata structure of a flat ASCII file.
• Custom Functions : When none of SAP Data Services built-in functions match your needs, create a custom function.

Scenario:

We have SAP ECC as source system and HANA DB as target system, this is used to store organizational data centrally for reporting purpose. (and also have different sources systems like MS SQL, Oracle and other applications on different databases, but in this case I’m taking only SAP ECC as source system).

The below is the initial screen of DS.

Give the SAP ECC details like below.

Import the required tables from SAP ECC into DS.

Give table name MARA and Import it.

Create Datastore for HANA DB, we are going to create and load MARA Table data in HANA DB under my Schema.

I’m going to take MARA, MAKT tables and join them, apply some filters and load the data into HANA DB.

Now Under Projects, create new project, under that create a new job, under that create new work flow, under that create new data flow.

Bring two Sources Tables (MARA and MAKT from Datastore) and add Query Transform, join both tables to Query Transform.

Once double click on Query Transform, it will open the below window and there we can Join tables and give filters etc…

Join two tables using MATNR and filter the data using SPRAS (only English). Apart from these we can also have additional functions but I’m not using them, this is simple demo.

Save All and Execute the job.

We can see the Error message, the job is failed. We have to adjust the Data types and again execute.

Save All and Execute again, the job will be SUCCESS and it will create tables in HANA under given Schema.

See the definition and Data of MATERIALS_ECC table.

We can also load the data to NON-SAP systems to do this we have to create separate Datastore in DS and use them for Target (and also Source).

Please ignore brevity and typos.