0 Flares Twitter 0 Facebook 0 Google+ 0 LinkedIn 0 Email -- Pin It Share 0 0 Flares ×

Merge Transformation 

Merge TransformationSummary:   

Merge Transformation is used to combine the multiple input data sets into a single output data set. It is equivalent to SQL UNION ALL statement. In order to eliminate duplicate records from output dataset basically to attain UNION operation, add a Query transform with DISTINCT option enabled after the Merge transform.

  • Merge data from multiple data sources, such as tables and files.
  • Create complex datasets by nesting Merge transformations.

Prerequisite:

  • All the Input data sets must have same structure and data type lengths.

Note: In case we want to merge flat file source with database source table then we have to modify the structure (using Query Transform) of those data sets so they match and merge into single data set.

Example Scenario:

  • In this scenario, we are using 3 source tables 1, 2 and 3 as shown given below. And our target table structure is shown below in the figure 1
Merge Transformation in BODS
Figure 1: Sample Target Structure
  • We are getting these columns based on our business requirement.
  • STATEMENT_ACCT_TKN: This is the key column for all source tables. We can join our source tables based on this column only.
  • Remaining 6 columns (change category, lock object category, process code, lock reason, valid from date and valid to date) have values dunning lock, interest lock and posting lock.
  • Every column has three values are getting from source tables or has constant values but we want all columns data in our target table.
  • In this scenario we are using merge transformation. Merge Transformation is used to combine the multiple input data sets into a single output data set as shown in below scenario..

 

SACC01_CLIENT_TKN STATEMENT_ACCT_TKN STMACCT_PURPOSE_CD

345433

543654

SUMM

234323

67489

DAMG

 Figure 2: Sample Source Data1 

STATEMENT_ACCT_TKN SUSP_TYPE_TK START_DATE END_DATE

543654

RTFG

20120903

99991231

 Figure 3: Sample Source Data 2 

STATEMENT_ACCT_TKN SUSP_TYPE_TK LOCKR_KEY

543654

RTFG X

 Figure 4: Sample Source Data 3 

STATEMENT_ACCT_TKN CHANGECATEGORY LOCKOBJECTCATEGORY
543654 I 06
67489 I 06

 Figure 5: Sample Target Data

  •  Figures 6, 7, 8, 9 and10 shows the object hierarchy for Merge transformation job, ETL job flow and the way we define the Mapping conditions respectively. 
Merge Transformation in SAP BODS
Figure 6: Merge Transformation Object Hierarchy
Merge Transform in BODS
Figure 7: Merge Transformation job ETL Flow
  • If then else function is used to assign value ‘I’ to records which are not null as shown below figure 8.
Merge Transform in SAP BODS
Figure 8: Defining Mapping Rules in Query_DUNNING_LoCK
  •  A sysdate function returns is a date time value. Cast function used to converts the sysdate function’s date time value to a string that displays only the date.
  • Replace_substr function used to replace ‘.’ with empty string as shown below figure 9.

 

Merge Transformation in BODS 1
Figure 9: Defining Mapping Rules in Query_Posting_LoCK
  • If then else function is used to assign value ‘C’ to records which are not null as shown below figure 10.
Merge Transform in SAP BODS 1
Figure 10: Defining Mapping Rules in Query_Interest_LoCK
  • Where condition is used to join the two tables as shown below figure 11.
Merge Transform in SAP BODS 2
Figure 11: Defining where condition in Query_1

That’s it.

Enjoy 🙂

0 Flares Twitter 0 Facebook 0 Google+ 0 LinkedIn 0 Email -- Pin It Share 0 0 Flares ×