Loading multiple target tables in single interface (mapping) in Oracle Data Integrator

Loading multiple target tables in single interface (mapping) in Oracle Data Integrator

ODI 12c is released and it has the feature of loading mutiple target tables as part of a single interface. Actually interface is termed as mapping in ODI 12c. So i will refer as mapping going forward in this post.

In this post, I will show how to load all rows of source into multiple target tables. And also based on some condition, how to load few rows of source to one target and other rows into other target. It is only the split condition which decides which rows goes to which target table. 

Lets get started.

1. Load Multilple target tables in a mapping

I am going to use the HR.EMPLOYEES table as source and create two new tables to capture sub sets of columns from the employees table. I call emp_basic and emp_advanced. Here are the DDLs for both source and target tables.

Source:

CREATE TABLE "HR"."EMPLOYEES"

  (

    "EMPLOYEE_ID"  NUMBER(6,0),

    "FIRST_NAME"   VARCHAR2(20 BYTE),

    "LAST_NAME"    VARCHAR2(25 BYTE) CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ENABLE,

    "EMAIL"        VARCHAR2(25 BYTE) CONSTRAINT "EMP_EMAIL_NN" NOT NULL ENABLE,

    "PHONE_NUMBER" VARCHAR2(20 BYTE),

    "HIRE_DATE" DATE CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ENABLE,

    "JOB_ID"         VARCHAR2(10 BYTE) CONSTRAINT "EMP_JOB_NN" NOT NULL ENABLE,

    "SALARY"         NUMBER(8,2),

    "COMMISSION_PCT" NUMBER(2,2),

    "MANAGER_ID"     NUMBER(6,0),

    "DEPARTMENT_ID"  NUMBER(4,0),

    CONSTRAINT "EMP_SALARY_MIN" CHECK (salary > 0) ENABLE,

    CONSTRAINT "EMP_EMAIL_UK" UNIQUE ("EMAIL"),

    CONSTRAINT "EMP_EMP_ID_PK" PRIMARY KEY ("EMPLOYEE_ID"),

    CONSTRAINT "EMP_DEPT_FK" FOREIGN KEY ("DEPARTMENT_ID") REFERENCES "HR"."DEPARTMENTS" ("DEPARTMENT_ID") ENABLE,

    CONSTRAINT "EMP_JOB_FK" FOREIGN KEY ("JOB_ID") REFERENCES "HR"."JOBS" ("JOB_ID") ENABLE,

    CONSTRAINT "EMP_MANAGER_FK" FOREIGN KEY ("MANAGER_ID") REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") ENABLE

  )

Targets:

CREATE TABLE "DATA_TARGET"."EMP_BASIC"

  (

    "EMP_ID"     NUMBER(16,0) NOT NULL ENABLE,

    "FIRST_NAME" VARCHAR2(50 BYTE),

    "LAST_NAME"  VARCHAR2(50 BYTE)

  )

CREATE TABLE "DATA_TARGET"."EMP_ADV"

  (

    "EMP_ID"     NUMBER(16,0) NOT NULL ENABLE,

    "JOB_ID"     VARCHAR2(20 BYTE),

    "SALARY"     NUMBER(16,0),

    "COMMISSION" NUMBER(16,0)

  )

Lets create the mapping in ODI 12c client.

Go to logical tab and drop the source table hr.employees. You can see two employees table instances with name employees and employees1 dropped there. It is because of the manager_id foreign constraint referring to the self table. ODI 12c client represents this constraint as self join here automatically.

Lets drop the two targets data_target.emp_basic and data_target.emp_adv.

Drag and drop the column names from source to target tables to do the mapping.

Lets review the physical tab for KM details. Select each target table in the physical tab to review the IKM and CKM used. As we can see, ODI uses oracle parallel hint to load the target tables which improves performance for appending the data.

You can also see that IKM and CKM are from Global Knowledge modules. By default ODI 12c client has few KM in global KMs.

Lets run the mapping and see the results.

Lets review the target tables data. we see that all rows from source is populated into both tables for different columns.

2. Splitting records into multiple target tables based on condition

Lets try to split rows from source into multiple targets based on some condition. rows which are matching the condition are going into the respective targets. 

Go to the logical tab and delete the lines from source to target and add split components from components.

connect source to split and split to multiple targets. while deleting the existing lines from source to target, odi will still keep the existing mappings.

Click on split component and in the split component properties, define condition in expression section for each target output1 an output2 so that only rows matching this condition from source are loaded to respective target.

The condition I have defined here are 

OUTPUT1 - Default.EMPLOYEES.EMPLOYEE_ID <= 109

OUTPUT2 - Default.EMPLOYEES.EMPLOYEE_ID >   109

so employee_id less than 109 goes to emp_basic table.

employee_id greater than 109 goes to emp_adv table.

Lets run the interface and check the results. Run is successful, lets verify the results in target tables.

emp_basic table has records till employee id 109.

emp_adv table has records from employee_id 110 as expected.