Objective: In previous article Creating Project and interface in ODI we saw how to build a Project and interface in ODI. In this article we will learn how to load all rows of source into multiple target tables.

Loading Multiple target tables in single interface (mapping) in ODI:

ODI 12c has the feature of loading multiple target tables as the part of single interface. Actually interface is termed as Mapping in ODI 12c.

We are going to use the HR.EMPLOYEES table as source and create two new tables to capture subsets 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)

 )

Let’s 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.

Let us 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.

Let’s run the mapping and see the results.

Let’s review the target tables data. we see that all rows from source are populated into both tables for different columns.

Varun Kapila

Add comment