Joiner, Sorter and Aggregator Transformations


In this example, we understand how to use Joiner, Sorter and Aggregator Transformation.
Source: – ORDER.txt (SrcFiles)
PRODUCT (SDBU)
Target: – ODS_ORDER_AMOUNT (TDBU)
Joiner Transformation:- Joiner is Active and Connected transformation used to join tow
data 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 to
aggregate 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

imgg


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_TOT_QUANTITY=SUM(QUANTITY)
O_AVG_PRICE= AVG (PRICE)
O_MIN_COST=MIN (COST)
16. Drag the Target, Link the columns from AGG_ODS_ORDER_AMOUNT to
ODS_ORDER_AMOUNT

imgg (1)


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.
MenuToolsTask Developer
Task Developer MenuTasksCreate
19. Provide the connection or Path for Integration service to communicate with your
source and Target system
Session–>edit–>mapping Source (LS)  PRODUCTOLTP_SOURCE
Session–>edit–>mapping SourcesSQ_ORDERSource File NameORDER.txt (Copy the
file to $PMSrcFiledir= C:/Informatica/9.5.1/server/infa_shared/SrcFiles)
Session—>edit–>mapping  Target (LS) ODS_ORDER_AMOUNTOLAP_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.
Click Transformation > Create to create Lookup transformation. Select Lookup, enter name as LKP_CUSTOMER

2 Comments to “Joiner, Sorter and Aggregator Transformations

  1. Hi my friend! I want to say that this post is amazing, great written and include almost all significant infos. I’d like to see more posts like this. Rombach

    1. Thanks william..

Leave a Reply

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