Joiner, Sorter and Aggregator Transformations
In this example, we understand how to use Joiner, Sorter and Aggregator Transformation.
Source: – ORDER.txt (SrcFiles)
Target: – ODS_ORDER_AMOUNT (TDBU)
Joiner Transformation:- Joiner is Active and Connected transformation used to join towdata streams coming from Homo or Heterogeneous sources. Joins the sources with atleast one matching column. One Joiner transformation can join only two sources, among both sources we need to choose one as Master and Other as Detail.Joiner Transformation supports following types of joins: Normal Join: – Returns all the matching rows from Master and Detail Sources based on the join condition. Its equivalent to NORMAL JOIN in SQL.Master Outer Join: – Returns all the rows from detail source and matching rows from the master source. Its equivalent to RIGHT OUTER JOIN in SQL.Detail Outer Join: – Returns all the rows from master source and matching rows from master source. Its equivalent to LEFT OUTER JOIN in SQL.Full outer Join: – Returns all the rows from both master and detail. Its equivalent to FULL
OUTER JOIN in SQL.
Sorter Transformation:- Sorter transformation is Active and Connected transformation used to sort the data in ascending or descending order according to the specified port keys.Aggregator Transformation :- An aggregator is a Active and Connected Transformation which performs the aggregated calculations like
AVG,COUNT,MIN,MAX,SUM,MEDIAN,PERCENTILE,STDDEV,SUM and VARIENCE. By default aggregator transformation treats the null as NULL values.Using incremental aggregation, you can apply changes captured from the source toaggregate calculations such as Sum, Min, Max, Average etc… If the source changes incrementally and you can capture changes, you can configure the session to process those changes. This allows the Integration Service to update the target incrementally, rather than forcing it to process the entire source and recalculate the same data each time you run the session.
1. Import the Source Definition PRODUCT by the Source Analyzer from SDBU schema.
2. Import the Source Definition ORDER.txt by the Source Analyzer from SrcFiles folder provided. Edit the definition, In properties tab change the Date Format as
3. Create a Mapping ―m_ods_order_amount‖ by using the mapping designer. Click Tools > Mapping Designer to open the Mapping Designer.
4. Drag the PRODUCT and ORDER.txt Source Definitions to the mapping designer workspace.
5. Click Transformation > Create to create Joiner transformation. Select Joiner, enter name as JNR_ORDER_PRODUCT
6. Drag all the columns from PRODUCT Source Qualifier to Joiner Transformation JNR_ORDER_PRODUCT. Also, pull the columns from ORDER.txt SQ to joiner transformation.
7. Double click on Joiner Transformation in Edit mode; in ports tab check the M column checkbox for any one of the ports, which flow originally from the PRODUCT source definition.
8. In condition tab, add a new condition PRODUCT_CODE=PRODUCT.
9. Click Transformation > Create to create Sorter transformation. Select Sorter , enter name as SRT_ORDER_PRODUCT
10. Pull the PRODUCT_CODE, VENDOR_ID, PRICE, COST, ORDER_DATE and QUANTITY from Joiner to Sorter Transformation.
11. In the Ports tab, check the key checkbox for the ORDER_DATE, PRODUCT_CODE and VENDOR_ID ports. Be certain the ports are in that order.
12. Click Transformation > Create to Create an Aggregator transformation and name it AGG_ODS_ORDER_AMOUNT.
13. Copy/link all ports from SRT_ODS_ORDER_AMOUNT to AGG_ODS_ORDER_AMOUNT.
14. Edit AGG_ODS_ORDER_AMOUNT, on ports tab group the data by ORDER_DATE, PRODUCT_CODE and VENDOR_ID.
15. On the Ports tab, create three output ports
O_TOT_QUANTITY, O_AVG_PRICE and O_MIN_COST.
O_AVG_PRICE= AVG (PRICE)
16. Drag the Target, Link the columns from AGG_ODS_ORDER_AMOUNT to ODS_ORDER_AMOUNT
Click Repository > Save to save the new mapping to the repository. Validate the mapping.
18. In the Workflow Manager Navigator, create the session ―s_m_ods_order_amount‖ that uses mapping ―m_ods_order_amount‖ using the Task Developer.
MenuToolsTask Developer Task Developer MenuTasksCreate
19. Provide the connection or Path for Integration service to communicate with your source and Target system
Session–>edit–>mapping Source (LS) PRODUCTOLTP_SOURCE
Session–>edit–>mapping SourcesSQ_ORDERSource File NameORDER.txt (Copy the
file to $PMSrcFiledir= C:/Informatica/9.5.1/server/infa_shared/SrcFiles)
Session—>edit–>mapping Target (LS) ODS_ORDER_AMOUNTOLAP_TDBU
20. Choose the Target load Normal Mode and check ―truncate target table‖ option
21. Create the workflow ―w_ods_order_amount‖ using the workflow designer. Assign the appropriate INT service.
22. Click Tasks > Link Task. Drag from the Start task to the Session task. Click Repository > Save to save the workflow in the repository.
23. Execute the workflow and verify the data in Target.