LAB 6: WORKING WITH SORTER AND AGGREGATOR TRANSFORMATIONS

BUSINESS REQUIREMENT:

Suppose a bank wants to create a summary report of the overall transactions of the customers. This summary report will help to gather the facts quickly.So transaction reports are made for the customers, both month and year wise.

CHALLENGES:

  • The transaction data has to be aggregated in a single table at two levels: year and month.
  • We have to show only cleared transactions.
  • A sorter transformation is needed to sort the data year wise and month wise.
  • Aggregators are used to find the overall sum in a year and a month respectively.
  • This data in then combined and loaded into a single table.
  • Expressions are used to get the year and the month values out of transaction date.

SOLUTION :

STEP 1:

  • Go to the PowerCenter Administrator and Open as administrator.
  • Connect to the required repository and also to the folder under the repository.
  • Go to Tools->Source Analyzer.
  • Import the transaction tables TRXN,TRXN_STATUS and TRXN_TYPE from the Source Database.

1

  • Go to the Target Designer.
  • Import the table SUM_YEARMON_TRXN_T from the Target Database.

3

STEP 2:

  • Go to the Mapping Designer.
  • Create a new mapping by selecting Mappings->Create from the toolbar.
  • Name the new mapping as M_SUM_YEARMONTHTRXN.

5

  • Drag the TRXN and TRXN_TYPE tables from the Sources in the Navigator into the Mapping Designer workspace.

6

  • Delete both the Source Qualifiers.
  • Create a common Source Qualifier and name it as SQ_TRXN by selecting Transformations->Create from the toolbar.

7

  • Select the Sources for Source Qualifier Transformation as TRXN and TRXN_TYPE.

8

  • Click on OK.

9

  • Edit the Source Qualifier.
  • Go to the Properties tab.
  • Set the User Defined Join in the new Source Qualifier as TRXN.TRXN_TYPE=TRXN_TYPE.TRXN_TYPE_ID.
  • Set the Source Filter as TRXN.TRXN_STATUS=4.

10

  • Click on OK and close the Edit Transformations window.

STEP 3:

  • Create a new Expression by selecting Create from Transformations.
  • Select Expression from the drop down menu.
  • Name the Expression as EXP_DATE.

11

  • Drag  TRXN_DATE,TRXN_AMOUNT,TRXN_TYPE_NAME from the Source Qualifier into the Expression created.

12

 

  • Create three output ports with the following details.
  • Create the First output only port with name as O_YEAR and set the datatype as Integer and change the Expression to TO_INTEGER(TO_CHAR(TRXN_DATE,’YYYY’)).
  • Create the Second output port with name as O_MONTH and set the datatype as Integer and change the Expression to TO_INTEGER(TO_CHAR(TRXN_DATE,’MM’)).
  • Create the Third output port with name as O_MONTHNAME ,datatype as String,Precision as 3 and change the Expression to TO_INTEGER(TO_CHAR(TRXN_DATE,’MON’)).

13

 

  • Click on Apply and then Click on OK to save the changes.

STEP 4:

  • Create a new Sorter Transformation.
  • Name the transformation as SRT_YR_MON.

14

  • Drag O_YEAR,O_MONTH,O_MONTHNAME,TRXN_AMOUNT and TRXN_TYPE_NAME  from the Expression into the Sorter.

15

  • Double Click on the Sorter Transformation.
  • Select the Ports tab.
  • Set O_YEAR,O_MONTH and TRXN_TYPE_NAME as sort keys by checking the Key column.

16

 

STEP 5:

  • Create a new Aggregator Transformation and name it as AGGR_YR_MON.

17

  • Drag all the ports from the Sorter Transformation into the Aggregator.

18

  • Edit the Aggregator Transformation.
  • Select the Ports tab.
  • Set the O_YEAR,O_MONTH and TRXN_TYPE_NAME as Group keys by checking the Group By column.
  • Create a new output port, O_MON_AMOUNT .Set its datatype as double and its Expression as SUM(TRXN_AMOUNT).
  • Click on Apply and then click on OK to save the changes made.

19

 

STEP 6:

  • Create another Aggregator Transformation and name it as AGGR_YR.

20

  • Drag O_YEAR,TRXN_AMOUNT and TRXN_TYPE_NAME from the Sorter into the new Aggregator Transformation created.

21

  • Edit the Aggregator Transformation and set the O_YEAR and TRXN_TYPE_NAME as Group keys.
  • Create a new output only port named O_YR_AMOUNT. Set its datatype as double and its Expression as SUM(TRXN_AMOUNT).
  • Click on Apply and then click on OK to save the changes made.

22

 

STEP 7:

  • Create a new Union Transformation and name it as UN_YR_MON.

23

  • Drag the O_YEAR,O_MONTHNAME,TRXN_TYPE_NAME and  O_MONTH_AMOUNT from the AGGR_YR_MON Aggregator into the Union Transformation.

24

 

  • Edit the Union Transformation.
  • Go to the Groups tab and rename the NEWGROUP to ALL_MONTHS.
  • Also Create a new group and name it as Year.

25

 

  • Click on Apply and then click on OK to save the changes made.
  • Connect O_YEAR,TRXN_TYPE_NAME and  O_YR_AMOUNT from the AGGR_YR to the YEAR Group of the UnionTransformation.

STEP 8:

  • Create a new Expression Transformation and name it as EXP_UPDATE.

27

  • Drag all the output ports from the Union Transformation,UN_YR_MON into the Expression EXP_UPDATE.

28

 

  • Create a new output only port named as O_LAST_UPDATE having Date datatype.
  • Set its Expression as SYSDATE.

29

 

STEP 9:

  • Drag the SUM_YEARMON_TRXN_T  table from the Targets in the Navigator into the workspace.

30

 

  • Connect all the ports from the EXP_UPDATE to the Target Definition table.
  • Save the mapping by CTRL+S.
  • Perform Versioning so that no further changes could be made to the mapping.
  • Right click on the Mapping in the Navigator and Generate Workflow as explained before.
  • Go to the Workflow Manager and drag and drop the mapping from the Navigator into the workspace.
  • Edit the Session as explained before.
  • Save the Session and right click on the mapping in the Navigator and then Start Workflow.
  • The Workflow Monitor screen shows the successful running of the workflow.

31

Leave A Reply

Your email address will not be published. Required fields are marked *