I want to load unique record one table and duplicates one table using ODI 12c Mapping

ODI 12C SCENARIO

Agenda:

Mapping Source Unique and Duplicate record into different different target using ODI 12c Mapping

Source Table:  EMP_INFO

Source Table Data: 

Target Table: EMP_INFO_TAR1 (For Unique Record)

Target Table Expected Data: EMP_INFO_TAR1 (For Unique Record)

Target Table: EMP_INFO_TAR2 (For Duplicate Record)

Target Table Expected Data: EMP_INFO_TAR2 (For Duplicate Record)

Used ODI components:

• JOIN
• AGGREGATE
• FILTER
• SET
• SPLIT

Step By Step Implementation Process:

Step 1:

•         Drag source table twice in the mapping area.
•      Connect both table with aggregate component as like above diagram
•          Add 1 column into the both aggregate attribute.
•          Name as status and in Expression column place value 1, 2.

Step 2:

•        Connect each Aggregate component output into a Filter component where we can filter out value with 1 and 2 records.
•        In Filter condition put the condition “AGGREGATE1.EMP_COUNT=1” and “AGGREGATE1.EMP_COUNT=2”.

Step 3:

•         Now the output of both Filter component pass as an input for the Set component same like the above.
•         Select Set operator from the Operator tab- Input Connector Points.

Step 4:

•        Output of the Step-3 means set component pass input as a Split component.
•        Navigate into Split conditions tab- Output Connector Points and write the Spit condition same like “SET_.STATUS=1” & “SET_.STATUS=2”.

Step 5:

• We are at the last stage where we connect Split Component output into a join component and did join with source table again to get the full table data.

JOIN:

JOIN1:

•    Now connect both the join component output into target table “EMP_INFO_TAR1”,” EMP_INFO_TAR2”.

Final Mapping Diagram:

Output:

Conclusion : In the conclusion, I can say there is a 11 rows of record in the source where only 3 rows is unique which has transferred into target 1 and others remaining 8 rows was transferred into target 2. 

This is required can be done through other different way, even using procedure also. Here I am trying to show how to achieve this requirement with the mapping also.

Thanks.