Skip to Content
Technical Articles
Author's profile photo Karan Sawant

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%20Landscape

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

1.1%20Datastore%20Type

 

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

1.2%20S/4%20HANA%20Credentials

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%20S/4%20HANA%20tables%20into%20Datastore

Importing S/4 HANA tables into Datastore

 

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

 

Imported%20tables%20in%20the%20Datastore

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%20of%20Template%20table

Creation of Template table in the HANA Datastore

 

The job design is simple and should be as follows:Staging%20Job%20Design

Staging Job Design

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

Dataflows%20for%20staging%20each%20table

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%20Design%20for%20BP%20and%20associated%20addresses

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%20criterion%20for%20BUT000%20AND%20BUT020

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%20value%20in%20ADR2%20Table

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%20condition%20for%20ADR2

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%20ADRC%20and%20supplementary%20tables

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%20BP%20General%20Data%20with%20Addresses/Supplementary%20data

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%20Diagram%20of%20the%20end-to-end%20process

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!

Assigned Tags

      2 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Dietmar Splitt
      Dietmar Splitt

      instead of building this kind of dataflow with 6 tables,
      I recommend to do this in the source-HANA within a single CDS- or Calculation View.
      That will increase performance because every join or filtering is done in the source
      and only the final result-set will be transfered.

      Author's profile photo Karan Sawant
      Karan Sawant
      Blog Post Author

      Thanks Dietmar! Great Suggestion. I've tried Implementing everything with Data services as the tool in focus. 🙂