Transformation Summary in SAP data service - BODS - Part 2 - Merge and Query Transformation

[pallab_haldar]

In this blog I am going to discuss the  Merge Transformation and Query Transformation in SAP Data Services :

Merge Transformation :

Merge Transformation is used to merge multiple data sources i.e. tables or files into a single data source. But the field all the sources need to have a similar number of fields, the same data type, and sequences.

Basically, merge transformation acts as a UNION.

For example, if we want to merge  below source tables of Indian and Pak employees for an organization that exists in two countries:

Table 1: IND_EMP :

Table 2: PAK_EMP :

Design of a Merge Scenario :

The target table EMPLOYEE should contain all the data due to the union.

 

Query Transformation :

Query transform is the most used transformation in BODS dataflow. It projects the data source fields to a subset or the same set.  it is used in single or with other transformations based on the business scenario.

It is used to -

1. Narrow down the column subset by projection.


2. Narrow down the data using the filter.


3. Merge data from multiple sources using join or union.


4. Perform other operations also.

For example, if there are two data sources like the below -

Data Source Table 1 : 

Data Source Table 2 : 

 

The business wants to know the highest salary of an employee outside India region. We can use query transformations for this -

Now double click on the Query transform and it will let you go to the query editor.

 

Now in the FROM tab put the joining condition.-

In the Where Tab editor add the below filter.

PAK_EMP.SAL >=10000.

Save the Job and execute it. The Data preview of the target table should look like the below -

 

# Which scenarios Merge and Query Transformation is preferable?

Merge transform only can merge data from data sources having  the same number of fields, the same data type, and the same sequences. It acts like an UNION operation.

But Query transformation can combine any set of datasets, project a subset of data, filter data, and add parameter to data.  The usage of query transformation is a very large set of utilities. It is commonly used with other transformation also.