SPLIT component will allow us to split the data into multiple downstream flows so that we can load data into multiple tables. It is similar to Informatica's ROUTER transformation. In this blog I will be taking a simple example to showcase the use of this component.
We will see how to load Employees details into department specific tables.
Examples: ACCOUNTING employees data will be loaded to EMP_ACCT table and RESEARCH employees data into EMP_RESR table. This can be done in specifying the split condition on Department name and rout the data flow into specific tables in the mapping.
Steps to follow in ODI:
1.Add source tables EMP and DEPT into logical layer
2.Add JOIN component and provide join condition
EMP.DEPTNO = DEPT.DEPTNO
3.Add SPLIT component . By default 2 output connector points will be added
4.Add 2 target tables EMP_ACCT and EMP_RESR
5.Provide Split condition
Open SPLIT properties ,
provide below split condition for OUTPUT1
DEPT.DNAME='ACCOUNTING'
and DEPT.DNAME='RESEARCH' to OUTPUT2
6.Connect to Target tables
Connect OUTPUT 1 to EMP_ACCT table
and OUTPUT2 to EMP_RESR table.
7. Set proper KMs for SPLIT outputs (LKM) and Target tables(IKM) in the Physical layer of mapping
Overall mapping looks like this
Note: Here specific queries will be generated for each SPLIT output connectors and Split condition will be the unique part in each query.
We will see how to load Employees details into department specific tables.
Examples: ACCOUNTING employees data will be loaded to EMP_ACCT table and RESEARCH employees data into EMP_RESR table. This can be done in specifying the split condition on Department name and rout the data flow into specific tables in the mapping.
Steps to follow in ODI:
1.Add source tables EMP and DEPT into logical layer
2.Add JOIN component and provide join condition
EMP.DEPTNO = DEPT.DEPTNO
3.Add SPLIT component . By default 2 output connector points will be added
4.Add 2 target tables EMP_ACCT and EMP_RESR
5.Provide Split condition
Open SPLIT properties ,
provide below split condition for OUTPUT1
DEPT.DNAME='ACCOUNTING'
and DEPT.DNAME='RESEARCH' to OUTPUT2
6.Connect to Target tables
Connect OUTPUT 1 to EMP_ACCT table
and OUTPUT2 to EMP_RESR table.
7. Set proper KMs for SPLIT outputs (LKM) and Target tables(IKM) in the Physical layer of mapping
Overall mapping looks like this
Note: Here specific queries will be generated for each SPLIT output connectors and Split condition will be the unique part in each query.
Question:-
ReplyDeleteI have source table column with joiningdate as a column with data
I want to use odi split component
In targets tables
At first table I want data only less than 15 dates data
And
In second target greater than 15 dates dataa
How to do this mapping?