Suppose we have the EMP table as our source. In the target we want to view those employees whose salary is greater than or equal to the average salary for their departments. Describe your mapping approach.

  Our Mapping will look like this:

ahref="http://png.dwbiconcepts.com/images/tutorial/info_interview/info_interview10.png"

To start with the mapping we need the following transformations:
After the Source qualifier of the EMP table place a Sorter Transformation . Sort based on DEPTNOport.

Next we place a Sorted Aggregator Transformation. Here we will find out the AVERAGE SALARY for each (GROUP BY) DEPTNO.
When we perform this aggregation, we lose the data for individual employees.
To maintain employee data, we must pass a branch of the pipeline to the Aggregator Transformation and pass a branch with the same sorted source data to the Joiner transformation to maintain the original data.
When we join both branches of the pipeline, we join the aggregated data with the original data.

So next we need Sorted Joiner Transformation to join the sorted aggregated data with the original data, based on DEPTNO. Here we will be taking the aggregated pipeline as the Master and original dataflow as Detail Pipeline.

After that we need a Filter Transformation to filter out the employees having salary less than average salary for their department.
Filter Condition: SAL>=AVG_SAL

Lastly we have the Target table instance.