ODI 12c: Data loading from Database table to Table using Oracle Data Integrator 12c

In this post, i’m going to explain how to data load from oracle table to table. my source system data avaiable in the OLTP(Employee_SRC table) and my destination table available in OLAP (Employee_DEST table).

Below are the table stucture and the data of the OLTP and OLAP.

OLTP:- Employee Source Table

create table Employee_SRC(
empno    number(4,0) primary key ,
ename    varchar2(10),
job      varchar2(9),
mgr      number(4,0),
hiredate date,
sal      number(7,2),
comm     number(7,2),
deptno   number(2,0)
);

OLTP:- Employee table Data.

insert into Employee_SRC values(1, 'KING', 'PRESIDENT', null,to_date('17-11-1981','dd-mm-yyyy'),5000, null, 10);
insert into Employee_SRC values(7698, 'BLAKE', 'MANAGER', 7839,to_date('1-5-1981','dd-mm-yyyy'),2850, null, 30);
insert into Employee_SRC values(7782, 'CLARK', 'MANAGER', 7839,to_date('9-6-1981','dd-mm-yyyy'),2450, null, 10);
insert into Employee_SRC values(7566, 'JONES', 'MANAGER', 7839,to_date('2-4-1981','dd-mm-yyyy'),2975, null, 20);
insert into Employee_SRC values(7788, 'SCOTT', 'ANALYST', 7566,to_date('13-JUL-87','dd-mm-rr') - 85,3000, null, 20);
insert into Employee_SRC values(7902, 'FORD', 'ANALYST', 7566,to_date('3-12-1981','dd-mm-yyyy'),3000, null, 20);
insert into Employee_SRC values(7369, 'SMITH', 'CLERK', 7902,to_date('17-12-1980','dd-mm-yyyy'),800, null, 20);
insert into Employee_SRC values(7499, 'ALLEN', 'SALESMAN', 7698,to_date('20-2-1981','dd-mm-yyyy'),1600, 300, 30);
insert into Employee_SRC values(7521, 'WARD', 'SALESMAN', 7698,to_date('22-2-1981','dd-mm-yyyy'),1250, 500, 30);
insert into Employee_SRC values(7654, 'MARTIN', 'SALESMAN', 7698,to_date('28-9-1981','dd-mm-yyyy'),1250, 1400, 30)
insert into Employee_SRC values(7844, 'TURNER', 'SALESMAN', 7698,to_date('8-9-1981','dd-mm-yyyy'),1500, 0, 30);
insert into Employee_SRC values(7876, 'ADAMS', 'CLERK', 7788,to_date('13-JUL-87', 'dd-mm-rr') - 51,1100, null, 20);
insert into Employee_SRC values(7900, 'JAMES', 'CLERK', 7698,to_date('3-12-1981','dd-mm-yyyy'),950, null, 30);
insert into Employee_SRC values(7934, 'MILLER', 'CLERK', 7782,to_date('23-1-1982','dd-mm-yyyy'),1300, null, 10);

OLAP:- Employee Destination Table

create table Employee_DEST(
empno    number(4,0) primary key,
ename    varchar2(10),
job      varchar2(9),
mgr      number(4,0),
hiredate date,
sal      number(7,2),
comm     number(7,2),
deptno   number(2,0)
);

Go to the Topology Tab and Select the Oracle Physical Architecture and click on the New Data Server.

Then give the Data Server Name and Other Details as below in the Definition menu.
here i have schema called 'oltp' which contains some persons data in the 'Person' table.these data i'm going to load to another table call 'Dim_Person' in the OLAP schema user.
Definition Menu:-

JDBC Menu:-

Click on the Test Connection button and Check the connectivity and we have successful connection with our physical Database.

Then we can see the Created Data server in the Physical Architecture under Oracle Technology.
Then select the created Data Server and Create Physical Schema for that.

In the Physical Schema, Give the Schema details.

Like wise we have to create Physical schema for OLAP user also.

Go the Logical Architecture and Create New Logical Schema under Oracle technology.
In the Context area provide physical schema details as per the given in the Physical architecture for OLTP and OLAP schemas,
OLTP:-

OLAP:-

Go to the Designer Tab and expand the model manu and create two new model folder for OLTP and OLAP .

Right Click on the New model folders (OLTP and OLAP) and create new model for each.

In the definition tab ,Give the appropraiate name, Technology(Oracle) and created logical schema for OLTP.

Then go to the Selective Rivers_Engineering Tab and Select the Source table (EMPLOYEE_SRC).

Then Click on the Rivers Engineer Button.

IN the OLTP folder you can see the selected table (EMPLOYEE_SRC).

Right click on that table and click View data. You can see the Source data which i’m going to load my next table (EMPLOYEE_DEST).

Likewise import the EMPLOYEE_DEST table to OLAP model and it has not any record.

Then create a new project Tab .

Give the name for the project.

Created project hierachy view as below.

Grag and drop the Employee_SRC model to default area of the logical tab in the created mapping sheet. Then drang and drop the Employee_DESC model to mapping sheet as below.

Go to propertise window of teh mapping and set the knwlegde model as below.

Go to the Project menu and right click on teh mapping sheet and run the mapping sheet.

Click Yes to save the mapping sheet.

Here we are executing without Agent. If you want you can schedule thisdata loading via Agent.

Session has started.

Go to the session list and expan the status menu and you can see the executed job as below .

To check wether the loading has completed without any issue, Go to the Designer tab and open the OLAP model and right click on the EMPLOYEE_OLAP model and Select the View data.