Friday, July 24, 2015

Split component in ODI 12c


Case Study:
We need to dump all the data which satisfying conditions into one table and unsatisfying data into another table (i.e., Simply we can say as rejected records in one table and accepted records in one table)
How to achieve it?
This can be achieved by using Split component in ODI 12c very easily.
For Demo Purpose – Scenario
We need to insert all records from employees table into employee to whom manager assigned and insert all records from employees table into emp_nomanager to whom manager is not assigned.

Pre-requisites:
1.     Employees, Employee and emp_nomanager Data Store of same structure should be available in Model
2.     Project already created

Steps
1.     Right click on Mappings and click on New Mapping
2.     Enter name of Mapping as Map.Manager and Non-Manager
3.     Disable create empty data set and click on ok
4.     Now drag and drop Employees , Employee and emp_nomanager datastores on to the Mapping Editor
5.     Drag and drop split component from component palette on to mapping editor
6.     Join Out port of Employees to Input port of Split component
7.     Join Out port of Split component to Input ports of Employee and emp_nomanager Datastores
8.     Popup will appear select
a.     Match Options
By Name
Ignore Case
b.     Create Actions
Auto map enable
9.     Click on ok
10.                         Now select Split component and go to Property inspector and then go to Split conditions à Select Output Connector Points
11.                        For Output1 à Splitter conditioner as “EMPLOYEES.MANAGER_ID is not null”
Output2 à Is Remainder is enabled

Completed Successfully!!!!!

Output
Employee Table
To view Data right click on Target datastores and click on data




Emp_nomanager Table

3 comments:

  1. What is use of "Bound Object" where we can use fr this?

    ReplyDelete
  2. In emp table deptno is null for few records. i want to load deptno = 10 into one table reaming data into another table (including deptno null, 20 etc...) how we can handle this scenario?

    ReplyDelete
  3. Hi Ram,

    Instead of enabling "is Remainder" checkbox. You need write split condition as "EMPLOYEES.DEPARTMENT_ID <> 10 OR EMPLOYEES.DEPARTMENT_ID IS NULL"
    Please let me know if you have any doubt still.

    Thanks,

    ReplyDelete