Working with Joiner Transformation and Rank Transformation

  Level2 Training , Transformations

This tutorial shows the process of creating an Informatica PowerCenter mapping and workflow which pulls data from Flat File data sources and uses Joiner Transformation and Rank Transformation to build a consolidated sales report.

Lets Consider the Scenario : The Sales Department wants to use data contained in flat files to build a table summarizing the revenue on each product by Product Code, Product Name, and Product Category. The weekly orders from each store are consolidated into one orders file, and the IT organization has downloaded from the Mainframe a flat file listing of each product sold by the company.

I. Analyze the source files

1. Launch Mapping Designer and connect to the repository.
2. Highlight your folder and open the Source Analyzer.
3. To import the ORDERS.TXT flat file definition into the repository, select SOURCES | IMPORT FROM FILE from the menu.
4. Select ORDERS.TXT from the source directory.
   HINT: Be sure to set the Files of type: to All files (*.*) from the pull-down list, before clicking on OK.
5. Set the following options in the Flat File Wizard
   1. Select the Delimited radio button.
      
   2. Name the source definition ORDERS. Click on Next button.
   3. Enter the column names and specify data types and field widths as shown below.
      
   4. Click on Finish.
6. Import the PRODUCTS.TXT flat file definition into the repository and name the source definition as PRODUCTS.
7. Verify the column names and change data types as needed to match the preceding table. The source definitions should look like the ones shown below.
   

II. Design the Target Schema

1. Create the target table definition Tgt_ProductRevenue_x in the Target Designer and generate the SQL script for the same. Your target table definition should look like below image.
   

III. Drag Sources and Targets into the new Mapping

1. Create a new mapping with the name M_Product_Revenue_x.
2. Set the Mapping Designer options.
3. Automatically create Source Qualifiers when calling source definitions into the mapping.
4. Click OK.
5. Drag the ORDERS and PRODUCTS source definitions from the Navigator Window into the Mapping Designer workspace. Two Source Qualifiers will be created automatically .
6. Accept the default names.
7. Drag the Tgt_ProductRevenue_x target definition from the Navigator Window into the workspace.

IV. Create the Joiner Transformation

1. Choose TRANSFORMATION | CREATE.
2. Select the Joiner transformation from the pull down list.
3. Name it Jnr_Orders_Products_x Make sure you are in link mode by selecting LAYOUT | LINK COLUMNS.
4. Link the ITEM_NO, ITEM_NAME, and PRODUCT_CATEGORY ports from SQ_Products (Source Qualifier) into Jnr_Orders_Products_x (Joiner).
5. Link the ITEM_NO, QTY, and PRICE ports: from SQ_ORDERS (Source Qualifier) into Jnr_Orders_Products_x (Joiner).
6. Open the Joiner transformation in Edit mode.
7. Select the Ports tab.
8. Identify all the ports from PRODUCTS as Master ports.
   HINT: Check the M column checkbox for any one of the ports, which flow originally from the PRODUCTS source definition. 
   
9. Select the Condition tab.
10. Add a new condition: ITEM_NO = ITEM_NO1.
11. Exit the Edit Transformations dialog box by clicking the OK button.

V. Create the Aggregator Transformation

1. Create an aggregator transformation and name it Agg_ProductRevenue_x.
2. Link the ITEM_NO, ITEM_NAME, PRODUCT_CA TEGORY , QTY , and PRICE from JNR_ORDERS_PRODUCTS (Joiner) into Agg_Product_Revenue_x (Aggregator).
3. Switch to copy mode, LAYOUT | COPY COLUMNS.
4. Copy the TOTAL_QTY and TOTAL_REVENUE ports from Tgt_ProductRevenue_x (target definition) into the Agg_ProductRevenue_x(Aggregator).
   Remainder: You are not linking these ports. Instead you are using the column names (ports) in the target table definition as a model for the names of the ports in the new Aggregator.
5. Group by ITEM_NO, ITEM_NAME, and PRODUCT_CA TEGORY . Hint : Check the Group by columns check box under the Ports tab in the Edit Transformations box.
6. Enter aggregate expressions for the TOTAL_QUANTITY and TOTAL_REVENUE ports and make QTY as input port: TOTAL_QUANTITY: SUM(QTY) TOTAL_REVENUE : SUM(PRICE * QTY)
7. The final transformation should look like the one shown below.

VI. Create the Rank Transformation

1. Choose TRANSFORMA TION | CREA TE.
2. Select Rank from the pull-down list.
3. Name it Rnk_TopTen_x.

   Note: You can also click on the icon from the transformations toolbar to create the rank transformation.
4. Switch to link mode LAYOUT | LINK COLUMNS.
5. Link ITEM_NO, ITEM_NAME, PRODUCT_CATEGORY, PRICE, TOTAL_QUANTITY and TOTAL_REVENUE from Agg_ProductRevenue (Aggregator) into Rnk_TopTen (Rank).
6. Double-click on the Rank Transformation to enter Edit mode.
7. Select the Ports tab.
8. Identify the TOTAL_REVENUE port as the one to rank. Hint: Check the R column. 

9. Deselect the GroupBy options on the ITEM_NAME and PRODUCT_CATEGORY ports.
10. Select the Properties tab.
11. Select Top/Bottom = Top, and Number of Ranks = 10.
12. Exit the Edit Transformations dialog box by clicking the OK button.
13. Connect the Rank transformation to the target table. Hint: Select LAYOUT | AUTOLINK, OR Right click on the workspace and choose Autolink.
14. Select REPOSITORY | SAVE.
15. Given below is the final mapping.
    

VII. Load the Target

1. Create a Workflow with the name wf_ProductRevenue_x.
   1. Create a session task with the name s_ProductRevenue_x
2. Run the Workflow.
3. Monitor the Workflow.
4. Verify the results for target table Tgt_ProductRevenue_x.

Your results should look something like this

VIII. Video Tutorial

Hope you enjoyed this tutorial, Please let us know if you have any difficulties in trying out these exercise.

Share with your friends