Objective:In the previous article we saw Table to table integration in ODI, in this article we will learn how to create Project and Interface.

Creating Project and Interface

Let's create a project in which we have the table to table interface.

In the designer tab, Projects section, create a new project and provide a name. Project code is an important identifier which is used to identify project specific objects as compared to global objects.

Expand the project which is created, right click on knowledge modules and select import knowledge modules(IKM). we need to select three knowledge modules here for loading, integration and check constraint. Select CKM Oracle,LKM SQL to SQL and IKM SQL Incremental update. We need to select knowledge modules specific to the technology and kind of data loading we are doing. KM automates the process with predefined code for loading, integration, check constraints, reverse engineering and journalizing (change data capture).

1. LKM SQL to SQL - Automates the loading of data from source to staging. reads data from hr.employees and put it into odi_staging.c$_employees.

2. IKM SQL Incremental Update - Takes the data from staging and merges (insert or update) data to target.

3. CKM Oracle - Check for specific constraints we need to check after loading in target.

After selection, we can verify the imported KMs in knowledge modules section.

Let’s create an interface now. Under project folder right click on interfaces and select new interface.

Provide name for interface and select the schema to which we need to load.

Click on the mapping tab in interface. Drag the employees table from hr model to source area and drag employees table from data_target model to target area. drag the field from source to target to complete the mapping. select the employee id in target mapping and verify that key attribute check box is checked. if not we cannot enable flow control in IKM and so make sure that it is checked.

Go to overview tab, select staging is different from Target so that staging tables are created in odi_staging schema.

Go to flow tab. Select staging in the diagram and select LKM SQL to SQL from the drop down. This LKM has only one option which is delete temporary objects. make this true so that C$ temporary table is deleted once target loading is complete.

Select target in the flow tab and select the IKM from the drop down. IKM provides few options which we configure based on our needs.

FLOW_CONTROL - checks the constraints while loading into the target. error records are placed into E$ table.

TRUNCATE - truncates the target table before loading into it.

DELETE ALL - delete the rows instead of truncate

STATIC_CONTROL - checks the constraints after loading into target table.  But target table will still have the error records

RECYCLE_ERRORS - considers the rows from E$ table along with C$ table for integration loading into target so that if any records corrected, they will be recycled and loaded to target.

COMMIT - we can make this false if needed and we can do a separate commit when this interface is part of a package so that we can maintain transaction for the objects used in ODI package.

CREATE_TARG_TABLE - creates target table if it doesn't exist at run time.

DELETE_TEMP_OBJECTS - deletes temporary I$ tables after target loading.

Select the controls tab and select the control knowledge modules. select the constraints to be enabled.

we have few options here to modify if any. we can ask ODI to drop the error table or check table here.

Save the interface.

With this, we have completed the creation of the interface. Let's run it now. Right click on the interface and click execute. Select global context, No agent and required log level. select ok. select ok on the session started dialog.

Go to operator tab to verify the run. expand all executions. Verify that our execution is successful. We can also expand the interface results and drill down to the steps executed at LKM, IKM and CKM levels and see the SQL generated and results here.

Let’s verify the temporary tables created now. Go to the ODI_STAGING schema and verify the  E$, check tables. If we make delete temporary objects to false in LKM and IKM options, we will see C$ and I$ tables also here.

Let's check the target table for data.

With this, we have completed the table to table loading using ODI interface.

Varun Kapila

Add comment