Objective: In the previous article Creating ODI Repository, we saw how to create Master and Work Repository in ODI. In this article, we will show the step by step process of implementing table to table integration in ODI.

Topology Configuration

Creating table to table integration involves only creating a interface in ODI. But there are some perquisites before we get in there.

Step 1. Create a physical server and logical servers.Check if they already exist.

Physical Servers are the configuration which point to the physical connection security details for database (oracle, sql servers, sybase etc), File, JMS, XML, Complex files etc. We can create physical connection for each environment (Dev, Test, Production) . Logical server is what used to run the interface. Based on the environment currently the interface is run, logical server points to the respective physical server.

Step 2. Check model is already existing for the database tables we are going to use. Model is nothing but reverse engineering the database tables, view, AQs etc for use in ODI objects. Model is applicable for File, XML, Complex files and JMS also. Even for File,XML, Complex Files and JMS the model is created in a relational way (as table datastores) in ODI.

Once both the steps are complete, we can create the interface. In this example, I have three schemas in XE database.

1. Oracle seeded HR schema - Has all HR related tables for demo purposes.

2. ODI_STAGE schema - I have created a new schema. This i will use for holding the temporary objects created by ODI

3. DATA_TARGET - I have created a new schema. I will use this schema to create target tables.

I have employees table in hr and data_target. hr schema employees table has some data but data_target employees table is empty. Using the interface I create in ODI, i will load the data from hr.employees to data_target.employees table. Also create a primary key constraint on the  employee_id in target.

CREATE TABLE "EMPLOYEES"

  ("EMPLOYEE_ID" NUMBER(6,0),

"FIRST_NAME" VARCHAR2(20 BYTE),

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

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

"PHONE_NUMBER" VARCHAR2(20 BYTE),

"HIRE_DATE" DATE NOT NULL ENABLE,

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

"SALARY" NUMBER(8,2),

"COMMISSION_PCT" NUMBER(2,2),

"MANAGER_ID" NUMBER(6,0),

"DEPARTMENT_ID" NUMBER(4,0)

  );

Physical Server and Logical Server definition

1. After connecting to the work repository, go to topology tab.

2. Under physical architecture section, right click on Technologies->Oracle->New data server

3. Create a data server connection odi_stage schema. This is the schema which is entry point for ODI. we can use any schema here for data server but it should have access to all other schemas and objects which we are dealing with for creating models, insert, select, update data.

4. Here I am using ODI_STAGE schema as data server and HR and DATA_TARGET as physical schemas4. In the definition tab, provide name and connection details for odi_stage schema.

In the jdbc section, select the jdbc driver and connection details for oracle database instance.

Test the connection by selecting test connection. select Local (No Agent) to simulate odi client as agent. Select ok, if you get pop up to create physical schema, select ok.

Right click on the data server we just created and select new physical schema.

Right click on the data server and select new physical schema. Let's create a physical schema for HR and DATA_TARGET schemas. In the create physical schema window, select the main schema and the work schema (schema where odi creates work tables like loading, error, integration tables). we can also provide prefix convention for the error, integration, loading tables which ODI creates in the work schema ODI_STAGE. we can also configure the masks which are specific to the technology we use.

Work Tables:-

Error - This table by default created with E$ prefix and it stores the primary key of the record which errored out and its corresponding error.

Loading - This table is the exact copy of the source data before any joins are done. This by default has C$ prefix.

Integration - This table is used to check the integration constraints check for data errors

Let's create a physical schema for DATA_TARGET also with schemas as data_target and work schema as ODI_STAGE.

Let's create a logical schema for hr and data_target here which maps to physical schema based on environment In logical architecture section right click on Oracle and select create logical schema. Here we are mapping it to Global context. We can also create DEV, TEST and PRODUCTION context and point the respective physical schema for context (which is nothing but environment reference).

Varun Kapila

Add comment