Skip to main content

Oracle

Creating an Oracle Data Integrator (ODI) Interface

Hopefully you all had a chance to check out my recent blog posting “Getting to know Oracle’s Oracle Data Integrator (ODI).”  If you haven’t checked it out yet, I urge you to check it out.  Well to continue on the topic of Oracle Data Integrator (ODI), I wanted to put together a few posts on showing how to build a simple interface, how to create an ODI package, how to create ODI load plans and scenarios, and how to schedule ODI load plans and scenarios.  So in the next couple days, please be on the look out for these great posts.  But let me get started on how to show you how to build a simple ODI interface that takes source data and loads it to its target, so let’s get started.

Before you can get started you need to make sure all your source and targets models have been added to ODI.

To add your source and target models you will need to locate model folder.  In this example our model folder is called, “Source_and_Target_Data.”

ODI_MODELS1

Within your model folder you should have your source and target models.  Now within this example, our source and target models will be Oracle DB.  Once you have located your models you will need to import the DB table definitions.  To do this, double click on the model.

ODI_MODEL2

Once the model is open you will want to select “Selective Reverse-Engineering” as this will allow you to bring in your source or target definitions.

ODI_SOURCE

Once you’re in “Selective Reverse-Engineering” tab, you will need to select the following check boxes, New Datastores, Existing Datastores and finally Objects to Reverse Engineer.

ODI_Selective Reverse-Engineering

Once the following are checked you will see a list of tables coming from your model.  Here you can select all your tables or just the specific one you want to work with.  Once you determine which model you would want to import click on the Reverse Engineer button on the top left corner.

ODI_MODEL3

This will import your table definition.

For more detail information and introduction to ODI models visit, 5 Creating and Using Data Models and Datastores.

Now that you have imported your source and target definitions you can now move forward and create your ODI interface.

Now before you can get started to create your ODI interface you will need to make sure your project has already been created.

ODI_DESIGNER

Once you located your project expand it.

ODI_FOLDER

From there you will find the sub ODI components.  Locate “Interfaces.”  Highlight “Interfaces” and right mouse click.  There you will have two options.  Select “New Interface”

ODI_INTERFACE

When you select “New Interface” this will allow you to create your ODI interface.

ODI_INTERFACE

When creating your interface you will need to enter a name for your interface and select “Optimization Context.”  Lastly, if selected, deselect the Staging Area Different From Target check box.   Within this blog posting we will leave it undefined and have ODI decided while we add our source and target.

Now that you have added the ODI definition, you will need to click on Mapping tab on the lower left hand corner.

Mapping

Now you have a blank slate in which you can add your source and target.  First navigate to Models tab and locate your source and target datastores.

Oracle - Guide to Oracle Cloud: 5 Steps to Ensure a Successful Move to the Cloud
Guide to Oracle Cloud: 5 Steps to Ensure a Successful Move to the Cloud

Explore key considerations, integrating the cloud with legacy applications and challenges of current cloud implementations.

Get the Guide

ODI_MODELS4

 

 

Once you have located your source datastore, drag it to the middle of your design canvas.

ODI_INTERFACE2

Once you have added your source datastore you will need to add your target datastore.  You will want to drag this in the “<Temporary Target Datastore > section of the ODI designer canvas.

ODI_TARGET

Once you drag your target, ODI it will ask you if you want to perform and automatic mapping from your source to target.  If your source columns and target columns are named the same click YES as ODI will be able to map these columns automatically.  If you do not want ODI to map your source to target columns click No.

ODI_AUTOMAP

After you clicked NO, select the “Quick Edit” tab

This will allow you to map your source to target columns add any lookup targets and look up conditions and etc.  Here in this example we will simply be mapping 1 source column to the 1 target column.

To map the source column to target column expand “Mappings” within the Quick Edit tab.  Here you will map your source columns to its target column.  To do this, select “Mapping Expression” for each target column.  In this example we only have 1 target column.

ODI_PROP

Once you opened up the mapping expression, here you can select your source column in which you would want to populate your target column.  In addition, you can also apply conversion rules to your source column before loading it to the target.  Here in this example I will be only passing the source column as is.

ODI_EXP_EDITOR

Once you are done click the “Apply” and than “OK” button.  Once you have mapped all your source columns to its target column, then you will need to click on the “Flow” tab.

While clicking the flow tab you will be able to update your source and target properties.

ODI_INTERFACE5

Now while updating your source and target properties you will need to make sure you have a few knowledge models (KM) available for loading your data.  Knowledge Modules (KMs) are code templates. Each KM is dedicated to an individual task in the overall data integration process. The code in the KMs appears in nearly the form that it will be executed except that it includes Oracle Data Integrator (ODI) substitution methods enabling it to be used generically by many different integration jobs. The code that is generated and executed is derived from the declarative rules and metadata defined in the ODI Designer module[1].

ODI_KM

Now in this ODI interface example, we will be using the KM LKM file to SQL for the source data and KM IKM SQL Control append for our target.

For more information on ODI knowledge models (KM) check out the following,  Introduction to Knowledge Modules.

In addition, in this example I will be leaving in all the ODI default options for target properties.

ODI_OPTIONS

Now that our settings for our source and target have been set we can now run our ODI interface.  To run the ODI interface click the Execute button on the top left hand corner.

ODI_EXECUTE_BUTTON

Once you click on the execute button the execution settings will appear.  Here you will need to select the “Context. Logical Agent, and Log Level.”

ODI_EXEC

Once you have selected the Context, Logical Agent, and Log Level you will need to click “Ok.”  This will execute your ODI interface.

While clicking OK a pop up will appear letting you know your session has started.

ODI_SESSION_STARTED

As the interface is extracting and loading data to its target you can view the progress of the load by click on the operator tab and viewing the session list.

Screen Shot 2014-08-24 at 4.22.31 PM

Once the load is complete your interface within the session list will be highlighted green with a check mark.

Screen Shot 2014-08-24 at 4.22.53 PM

Now to check to see if your source data has been loaded to its target within ODI you will need to click on the “Mapping” tab.  Once done so locate your target and right click the mouse.  You will have a few options.  Either click on Data or Number of rows.  This will show you either the data count or a few records you have loaded within your target.

Screen Shot 2014-08-24 at 4.23.19 PM

 

Well hopefully this blog post helped you build your very own ODI interface,  Stay tuned as I will be dropping a post on how to create an ODI package.

[1] http://docs.oracle.com/cd/E21764_01/integrate.1111/e12645/intro.htm

 

 

Thoughts on “Creating an Oracle Data Integrator (ODI) Interface”

  1. Hi,

    Can you guide me how to ignore datatype/length issue records and send them as log. Load condition satisfied records into target. Means ODI job should success and send the error records as log

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Chris Evans

Chris Evans has extensive management, technical, consulting and customer service skills. He provides hands-on expertise in project leadership and management, assessments, methodologies, data modeling, database design, meta data, systems analysis, and development. He has worked with multiple platforms, and his experience spans a wide range of operational and data warehouse environments. In addition to his BI background, Chris Evans has a experience in marketing strategy for major consumer brands.

More from this Author

Categories
Follow Us