Extracting Business Partner Data from SAP S/4 HANA to SAP HANA DB using SAP Data Services

Hello folks,

The purpose behind this blog post is to take you through the steps of extraction of business partner Data and their associated addresses from SAP S/4 HANA and load it into a HANA DB using SAP Data Services

Pre-Requisites:

• Access to S/4 HANA system with Business Partner Data
• Access to SAP Data Services Designer
• Access to HANA DB

Solution Landscape:

Solution Landscape

Before we move further into the actual steps involved for the end-to-end ETL process within Data Services, we’ll have a quick glance into what SAP Business Partner is, the tables involved, associated address and supplementary tables etc.

Business Partner:

A business partner can be

• an organization,
• Individual or
• Group

of which the company has a business interest in.

Business Partner is now capable of centrally managing master data for business partners, customers, and vendors. With current development, BP is the single point of entry to create, edit, and display master data for business partners, customers, and vendors.

For more in depth dive into Business Partner, their roles and use cases in S/4 HANA please refer to the following blogpost :

https://blogs.sap.com/2019/09/12/business-partner-sap-s4-hana-insights/

We will be extracting the Business Partner data and it’s associated addresses into Data Services and loading it into SAP HANA DB

Tables Involved in the Use case:

BP Tables	Description
BUT000	BP: General Data
BUT020	BP: Addresses
ADRC	User Address Information
ADR2	Telephone numbers
ADR3	Fax Numbers
ADR6	Email Addresses

• Importing the Business Partner Tables in Data Services

1. In SAP Data services Designer, firstly we will have to create a S/4 HANA Datastore

The steps involved would be

Local Object Library > Datastores> Right Click > New > Datastore Type> SAP Applications

Please input the relevant Application server name, User Name and Password

Once Done your S/4 HANA (SAP Applications) Datastore would be available in the Local Object Library.

Follow the same approach for creating a SAP HANA Datastore. Select ‘Database’ as Datastore Type and input the relevant Database server name, Port, username and password.

2. Expand the S/4 HANA Datastore > Right Click on Tables > Import by name

Importing S/4 HANA tables into Datastore

Import all the tables mentioned in the Table mentioned above in the blog

Imported tables in the Datastore

• Staging the S/4 HANA Tables

Create a New Project > Batch Job > Workflow > Dataflow for Staging BUT000 table

Within the Dataflow add BUT000 as source and map the relevant fields needed to a Query Transform and connect a Template Table as a Target. The template table can be from the corresponding HANA DB Datastore which we created.  These Template tables can be simply dragged from the Tool palette present on the RHS

Creation of Template table in the HANA Datastore

The job design is simple and should be as follows:

Staging Job Design

Similarly, for every remaining Business partner table same approach is to be followed.

Dataflows for staging each table

Our data has now been staged into the Staging tables and we’ll be using the same for further development.

• Loading Business partner data along with associated addresses and supplementary tables

Job Design for BP and associated addresses

The above diagram and numbering is to help understand the different steps in the Job design.

Create a new project > New Batch Job > Dataflow

In the Dataflow, Add all the Staged BP tables as sources.

Step 1: (Please refer to the Red box)

We’ll start with BUT000 (General Data) and BUT020 (BP:Addresses) map them to a Query Transform and join using common field present in both the tables. We’ll be keeping BUT000 as our leading table as we would want all the fields from the general business partner Table

In QRY_BP_GENERAL, in the FROM tab we’ll be inputting our joining criteria

Joining criterion for BUT000 AND BUT020

Now, For Addresses and Supplementary tables we will be keeping ADRC as our leading table and join the associated Email addresses, Telephone numbers and fax numbers on ‘ADDRNUMBER’ using left outer join.

Step 2: (Please refer to the blue box)

For the supplementary tables, it was noticed that for the same ‘ADDRNUMBER’ there were multiple entries. We will only be taking the default values, in the ‘FLGDEFAULT’ field the default value is denoted by ‘X’

FLGDEFAULT value in ADR2 Table

A filtering criteria is to be done before going further and joining  the ADRC and ADR2,ADR3 and ADR6 Tables

We will use a query transform and in WHERE Tab input, Table Name.FLGDEFAULT = ‘X’

Here, we’ll take example of the ADR2 Table, similarly ADR3 and ADR6 are to be followed.

WHERE condition for ADR2

Step 3: (Please refer to the green box)

We’ll now go ahead and join ADRC and the supplementary tables on ‘ADDRNUMBER’ using Left outer join and ADRC as leading table

Joining ADRC and supplementary tables

Step 4: (Please refer to the yellow box)

We’ll now join the QRY_BP_GENERAL (Consisting of fields from BUT000 and BUT020) and QRY_BP_ADDRESSES (Consisting of ADRC and ADR2,ADR3,ADR6) using a new Query Transform ‘QRY_BP_JOIN’

Again we’ll be joining using ‘ADDRNUMBER’ using left outer join

Joining BP General tabes with Addresses/Supplementary tables

Next, we can add another Query transform named QRY_FORMAT to format some of the technical field names to a more end-user friendly fields. This step is optional.

Finally, we’ll add a Template table as a Target named BP_ADDRESSES and finish the Job design.

Validate the job to see if there are any warnings/errors and then execute the job.

Depending on the fields mapped during the development, the final Output dataset in HANA DB should look like this:

Final Output having Business Partner details and their associated Addresses, Telephone numbers, emails, etc.

The Entire end-to-end process and work flow of the steps can be explained with the following Diagram:

Flow Diagram of the end-to-end process

Thank you much for following the blog till the end. I’ve tried to showcase the entire ETL process in SAP Data services using the Business Partner tables in S/4 HANA and loading into SAP HANA database.

Feel free to let me know if there are any questions, alternative approaches which could be followed and engage within the comment section. I’ll try my best to respond !

Thank you and good luck!