Category Archives: LKM

ODI – Day 4 – Organize Projects & Models – KM’s, Interfaces, DataStores

Posted on by

Before I start defining interfaces I first need to define my target datasource which is an Oracle 10g schema.

I will re-organize my models to be able to identify the source- and target-datamodels used for my migration-path.


I create a new Model Folder to hold the different data-models which will be used for my project.

Next I drag-and-drop the Data Model I’ve already created – see previous posts – inside my Model Folder and rename the Model to ‘SRC_Model_Excel’ by double-clicking.

Now I need to define the Physical and Logical Schema for my target-datastore which is my Oracle 10g schema.

Navigate to the Topology Manager and go to Technologies and choose ‘Insert Data Server for the Oracle-technology icon.


Define the connection settings for your Oracle Connection such as username/password and the JDBC configuration settings (JDBC-driver = oracle.jdbc.driver.OracleDriver,Url = jdbc:oracle:thin:@::). Test the connection-settings are defined correcttly via the Test-button and the local agent (depending on the configuration of your ODI). Click OK.

Update the default settings for the Physical Schema in the following way:

  • Define the schema to be your personal schema, your user-defined schema and the work schema is the work repository we’ve defined earlier. I’m very glad ‘Cenisis’ on the ODI-OTN forum has helped me figure out the difference between these two schema’s. If you want more information take a look at the following thread:
    http://forums.oracle.com/forums/thread.jspa?forumID=374&threadID=491550
  • Make sure to grant select-privileges ont the work repository schema to your user-schema because otherwise the execution of the interface will fail!
  • Define the Context of your physical schema, use the ‘Global’ context and define a new Logical Schema ‘Demo_DB’ and click OK.

Next we will define our data model for the target-datastore in our Models-tab in the Designer-window of ODI.

Choose ‘Insert Model’ and define this as being the target-datastore and choose ‘Oracle’ as ‘Technology’ and the Logical Schema that was defined earlier. Define the ‘Global’ context in the ‘Reverse’-tab and choose which tables you want to reverse engineer in the ‘Selective Reverse’-tab.

The different tables you’ve chosen to reverse-engineer will be shown in the Model as datastore.

Now it’s time to define a new interface to transform our Excel data (source) to our oracle data (target), the different tasks we need to perform:
- Create a New Interface.
- Define the Target Datastore.
- Define the source datastores, filters and joins on these sources.
- Define the mapping between source and target data.
- Define the interface flow.
- Define the flow control.
- Execute the interface for testing.

Go back to the first tab of your Designer-window, the ‘Projects’-tab and right-click on the ‘Interface’-icon in the Folder and choose ‘Insert Interface’.

Give your interface a meaningfull name and drag-and-drop the source-datastore and target-datastore to your interface. In our example you need to drag the PERSONEN-datastore (from the SRC-DemoExcel) to the Sources-window in your interface and drag-and-drop the USERINFO-datastore (from the TRG-DemoExcel) to the target datastore window.

Make sure to map the fields correctly by clicking on the attribute in the target-datastore and drag-and-drop the attribute from the source datastore to the implementation-tab in the mapping-screen.

For the USERID in my case, the Primary Key, I defined a sequence to be used to fill in this key because my Excel file doesn’t provide any ID’s. To be able to use a Sequence in the interface you need to specify the sequence_name in the implementation-field and of course nextval because a number needs to be inserted inside this column.
Make sure to check the ‘Active Mapping’ Checkbox and set the “execute on” radio button to the target area.

When you try to save the interface at this point you well get errors explaining you need to define Knowledge Modules.

I need to import or create Knowledge Modules to be used in my Interface and when you check out the ‘Knowlegde Module’ guides in the ODI Documentation Library you will notice many KM’s are already made available.

We will import the needed LKM, IKM and CKM to use them in our interface where we are migrating data from Excel to Oracle. Depending on the scenario, or use case you’re working out you need to use other KM’s which is explained in the guides (ODI Documentation Library).

Go to the Knowledge Module-node inside Designer, choose the Loading (LKM) KM and right-click and choose import.

In the Import Knowledge Modules-screen you have to browse to the impexp folder which is available in the odi-installation directory.

Choose to import the ‘LKM SQL to Oracle’-Knowledge Module and do the same for the Integration KM (IKM) and choose ‘IKM SQL Incremental Update’.

The Loading Knowledge Module will be used to load the data from our Excel Files to our staging area and the Integration Lnowledge Module will be used to integrate this Excel Data with our Oracle DB.

Open up the interface you’ve defined earlier and open up the ‘Flow’-tab and click on the SSO_0 or the source-datastore. Choose the LKM you’ve defined earlier from the LKM Selection Dropdown in the screen below.

Perform the same tasks for the IKM in the Target datastore, choose the IKM you’ve defined earlier (IKM SQL Incremental Update).

The last Knowledge Module we need to define is the Check Knowledge Module (CKM), which will define how data will be checked and the constraints and rules that must be satisfied before integrating the data.

Go to the Check Knowledge Modules and choose to import ‘CKM Oracle’ from the import wizard.

The CKM Oracle will be used for our checking algorithm, make sure to open up the interface again because otherwise the dropdown-lists don’t show the KM’s.

In the Controls-tab of your interface you need to select the CKM to be used.

Save your settings and choose to ‘Execute’ your interface … in my case I got the following error ‘Flow Control not possible if no Key is declared in your Target Datastore’. And after a little bit of snooping around I’ve figured out the problem, there was no Primary Key defined for my target datastore.

If you need to define constraints for your datastores you’ve got to go to the Model and drill down into the specific DataStore. Right-click on the Constraints-node and choose to define new constraints, choose the type ‘PK’/’Unique’/’Alternate’ and in the Columns-tab choose the column.


Navigate back to your interface and choose the second tab ‘Diagram’ and choose your Target DataStore, in the screen below define the Primary Key you’ve defined earlier in the ‘Update Key’-dropdown.

Choose to Execute the interface again, choose Yes for the default settings to execute the interface.

To follow the result of your execution in the Operator, click on the operator-icon in your menu bar.
The Operator window that subsequently opens (and can be refreshed using the button if the window is already open) details the execution process tasks.

As you can notice in the Operator-window no errors are shown and the execution’s status is done. You can check out the database to verify that the Excel data has succesfully been uploaded to the Oracle Database.
Up to the next challenge !