How to utilize Partitioning, Spliting and Sorting in ODI 12c

In this post we will look into some of the features in ODI12c.

Let me first mention the use case that we will look here.

Product versions: ODI 12.2.1

Database version: Oracle 12c

Database schema: 12c

In the Emp table, the Employees are present from various departments, lets assume that this was a very huge table in a typical datawarehousing environment and the table was partitioned based on the deptno. 

We have the requirement of processing only dept30 partition data, split this data into 2 part, based on JOB i.e SALEMAN goes to one table and other go to another table. If the branch is SALESMAN, the data needs to be loaded in SORTED Order.

To create this scenario, lets create the data first.

In the scott schema, run the following script:

CREATE TABLE "SCOTT"."EMP1" 

   ( "EMPNO" NUMBER(4,0), 

"ENAME" VARCHAR2(10 BYTE), 

"JOB" VARCHAR2(9 BYTE), 

"MGR" NUMBER(4,0), 

"HIREDATE" DATE, 

"SAL" NUMBER(7,2), 

"COMM" NUMBER(7,2), 

"DEPTNO" NUMBER(2,0), 

CONSTRAINT "PK_EMP1" PRIMARY KEY ("EMPNO"), 

CONSTRAINT "FK_DEPTNO1" FOREIGN KEY ("DEPTNO")

 REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE

   )  TABLESPACE "USERS"

   PARTITION BY HASH (DEPTNO)

    PARTITIONS 3;

Now insert the data into EMP1 table:

insert into EMP1

select * from EMP;

Create 2 more tables:

CREATE TABLE EMP_SALESMAN AS select * from emp1 where (1=2);

CREATE TABLE EMP_OTHERS AS select * from emp1 where (1=2);

Now Login to ODI and Create a Model "Oracle_Scott" with logical schema pointing to Scott schema. (Details about creating Physical Schema, Logical Schema, Model etc in ODI 12c could be found in various tutorials already present in internet.)

Reverse engineer the above three tables in the "Oracle_Scott" model.

Right Click on Mappings and select New Mapping, give some name  example :"INT_LOAD_PARTITION_SPLIT_AND_SORT"

Drag EMP1 from model and drop it in the mapping logical area and click on it and Navigate to the properties of EMP1 and navigate to the General tab and select the desired partition.

The screen shot shows all the steps required till now

Drag and Drop from Model the other 2 Data Stores also, EMP_SALESMAN and EMP_OTHERS.

Navigate to the Component Palette and Drag Split Component from there and drop into the mapping logical area.

Click on the Split component and navigate to properties of the split component, after navigating to the Output connector points, enter the values as shown in the screen shot.

Now Drag and drop the sort component from the Component Palette into the logical mapping area and perform the changes as mentioned in the screen shot.

The whole mapping should look some thing like this

Navigate to the physical tab in the Mapping set the integration knowledge module as IKM Oracle Insert- Global.

Save the mapping and Run.

The desired output will be Only the Department no: 30 Data is selected from the EMP1 table and that is split across 2 tables based on job, in table 1 Salesman job data is insert while in other the rest of the data is inserted.