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:
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
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:
1. In SAP Data services Designer, firstly we will have to create a S/4 HANA Datastore
The steps involved would be
Once Done your S/4 HANA (SAP Applications) Datastore would be available in the Local Object Library.
2. Expand the S/4 HANA Datastore > Right Click on Tables > Import by name
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
The job design is simple and should be as follows:
Similarly, for every remaining Business partner table same approach is to be followed.
Our data has now been staged into the Staging tables and we’ll be using the same for further development.
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
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’
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.
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
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
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:
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!
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
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!
- SAP Managed Tags:
2 Comments
