Informatica
Source Qualifier Transformation in Informatica with EXAMPLE
What is Source Qualifier Transformation? Source qualifier transformation is an active, connected...
Aggregator transformation is an active transformation is used to performs aggregate calculations like sum, average, etc.
For example, if you want to calculate the sum of salaries of all employees department wise, we can use the Aggregator Transformation.
The aggregate operations are performed over a group of rows, so a temporary placeholder is required to store all these records and perform the calculations.
For this, aggregator cache memory is used. This is a temporary main memory which is allocated to the aggregator transformation to perform such operations.
In this example, we will calculate the sum of salaries department wise. For this, we require a new column to store this sum. So, first of all, we will prepare a new column.
Step 1 – Create a new database target table, for example, say "sum_sal_deptwise", using the below script. You will see the new database target table is created under Target folder in next step.
Download the above Create_table_sal_deptwise.txt File
Step 2 – Create a New mapping "m_ sum_sal_deptwise".
In order to create new mapping, we need source table (EMP) and target table (sum_sal_deptwise) both in mapping designer for that we need to
Step 3 – In the mapping,
Step 4 - Drag and drop SAL & DEPTNO columns from source qualifier (SQ_EMP) to the aggregator transformation
Step 5 – Double click on the aggregator transformation to open its properties, and then
Step 6 – In the expression window
Step 7 – In edit transformation window, select option "GroupBy" by marking the check box against the deptno column and Click Ok ( by selecting group by against the deptno, we are instructing Informatica to group salaries by deptno)
Step 8 – Link the deptno and sum_sal columns from aggregator transformation to the target table
Now save the mapping and execute it after creating a new session for this mapping. The target table would contain the sum of salaries department wise. In this way, we can use aggregator transformation to calculate aggregate results.
What is Source Qualifier Transformation? Source qualifier transformation is an active, connected...
What is Normalizer Transformation? Normalizer is an active transformation, used to convert a...
Training Summary Beside supporting normal ETL/data warehouse process that deals with large volume...
What is Informatica? INFORMATICA is a Software development company, which offers data integration...
$20.20 $9.99 for today 4.5 (125 ratings) Key Highlights of Informatica Tutorial PDF 234+ pages...
What is Workflow? Workflow is a group of instructions/commands to the integrations service in...