Friday Jan 06, 2017

Oracle Data Integrator Best Practices from the A-Team: Using Reverse-Engineering on the Cloud and on Premise

Thanks to Benjamin Perez-Goytia of the A-Team – find out more about using the reverse engineering features of Oracle Data Integrator (ODI) through this ARTICLE.

Do you need step by step instructions? This might just do the trick!

There are three sections to this article. The first section covers the various options available in ODI to reverse-engineer metadata from a data server. The second section discusses performance considerations when running and executing reverse-engineering tasks.  The last section of the article discusses the ODI reverse-engineering best practices overall.

Happy reading!

Tuesday Jun 21, 2016

Oracle Data Integrator (ODI) Now Available

Oracle Data Integrator is now available! Please visit the ODI OTN page for downloads, documentation, related collateral and other useful links.

Take a look here for the latest details of this release – and read on for some highlights:

Hyperion Essbase and Hyperion Planning Knowledge Modules

Hyperion Essbase and Hyperion Planning Knowledge Modules have been made available out of the box with Oracle Data integrator and support the latest version ( of these Hyperion Applications.

Integrated Capture/Delivery support in GoldenGate Knowledge Modules

The GoldenGate Journalization Knowledge Modules (JKMs) for Oracle databases have been updated and now support Integrated Capture and Delivery. This updated functionality can improve performance and provides better scalability and load balancing.

Support for Cubes and Dimensions
Core ETL – ELT enhancements have been made; where ODI now provides support for two types of dimensional objects: Cubes and Dimensions. Users can create and use Cubes and Dimensions objects directly in Mappings to improve developer productivity with out of the box patterns that automate the loading of dimensional objects. This also allows for improved Type 2 Slowly Changing Dimensions and brand new Type 3 Slowly Changing Dimensions support with ODI.

Big Data Configuration Wizard
A brand new Big Data Configuration wizard is now available in the ODI Studio Gallery and provides a single entry point to configure the Topology objects for Hadoop technologies such as Hadoop, Hive, Spark, Pig, Oozie, etc.

Let us know how you are using ODI – and try out this new version to keep up with what’s current!

Friday May 20, 2016

Using Oracle Data Integrator (ODI) for Loading to Oracle Cloud’s Human Capital Management (HCM)

The A-Team is on a roll…

To load data into Oracle Cloud’s Human Capital Management (HCM) with Oracle Data Integrator (ODI) – check out the following blog:

Oracle Data Integrator (ODI) for HCM-Cloud: a Knowledge Module to Generate HCM Import Files

HCM is unique in that it uses a dedicated file format that contains both metadata and data. As far as the data is concerned, the complete hierarchy of parent and children records must be respected for the file content to be valid. This article explores a new Integration Knowledge Module (KM). This KM allows us to leverage ODI to prepare the data and generate the import file. Then, traditional Web Services connections can be leveraged to load into HCM.

Friday Nov 20, 2015

Using ODI Exchange to Share ODI Objects

In the previous blog post in this series we had a look at the new ODI Exchange functionality available in ODI 12.2.1 and how to use it. In this blog post we will be focusing on how to share your global ODI objects with the rest of the ODI community.

Step 1: Prepare Global ODI Objects

  1. Connect to the ODI Repository containing the desired Global Knowledge Modules or User Functions (Note: Only Global objects are supported)
  2. In the Designer Navigator expand the Global Objects accordion to select the desired object

  3. Export the Global KM or User Function via Context Menu “Export…”

  4. Specify the Export directory and Export name (Note: Export key is NOT supported)

  5. Press OK (and answer Yes to Export Key warning if shown)
  6. Press OK on Export finished dialog
  7. Obtain the GlobalId by examining the export file content.  The first <Object> should contain the exported object definition (class=”com.sunopsis.dwg.dbobj.SnpUfunc” or class=” com.sunopsis.dwg.dbobj.SnpTrt”).  A few more lines down should be one called <Field name=”GlobalId”…>…[CDATA[##-##-##-##]…  Make note of the value of the GlobalId you will need it again!  (Hint: you should probably copy it to the clipboard) 

    Here is an example:
    <?xml version="1.0" encoding="ISO-8859-1"?>
    <Admin …
    <Encryption …
    <Object class="com.sunopsis.dwg.dbobj.SnpUfunc">
      <Field name="ExtVersion" …
      <Field name="FirstDate" …
      <Field name="FirstUser" …
      <Field name="GlobalId" … [CDATA[99f49a0e-096d-407f-b423-a7f0fbb258da]]>
      <Field name="GroupName" …
      <Field name="IndChange" …

Step 2: Prepare Update Bundle

  1. Place the exported Global ODI Objects into an empty directory
  2. Create under that directory a “META-INF” sub-directory

  3. Create a file called “bundle.xml” in the “META-INF” directory as follows:
    • [mandatory] globalId: MUST exactly match the one in the export xml
    • [mandatory] name: the ‘name’ of the object which will be displayed by Check For Updates
    • [mandatory] version: the version number (in the form #.#[.#.#])
    • author: the value to show as “Author”, this information will appear within ODI Studio so make sure to fill it in!
    • authorUrl: the value to show as the “Author Url” (http://... ) , this information will appear within ODI Studio so make sure to fill it in!
    • description: the description to show in the description field, this information will appear within ODI Studio so make sure to fill it in!
    You can also download a sample bundle.xml file from here: link

  4. Utilize a ‘zip’ tool (zip, winzip, 7zip, etc…) to create a single .zip file containing the update contents in the ‘root’ directory and META-INF/bundle.xml
    Ex: in Linux:    zip -9 –r {bundleZipFile}.zip *

Step 3: Upload the bundle zip file to the Oracle Data Integration project on

  1. Go to and create an account if you don't have one yet

  2. Click on Join This Project
  3. Click on Downloads in the Project Features menu on the left hand-side

  4. Click on ODI

  5. Click on Knowledge Modules

  6. Click on 12c

  7. Finally click on Add File / Link for Download, specify the file to upload and put a title/description. If you don’t see 'Add File / Link for Download' you need to log into

  8. Click on Upload to complete the upload of the object on When you’re done click on Done.

When a new object is added to we will be automatically notified and will add it to the ODI Exchange. Thanks for contributing!!

If you want to know more about all the new features included in Oracle Data Integrator 12.2.1 have a look at the New Features document on OTN.

Wednesday Nov 18, 2015

Introducing Oracle Data Integrator (ODI) Exchange

Oracle Data Integrator 12.2.1 was recently released and introduces many exciting new features including the ODI Exchange. In a nutshell, ODI Exchange allows Oracle and our users to easily share global ODI objects (Knowledge Modules and User Functions) with the rest of the community through Update Centers. There are 2 Update Centers: one on which hosts unsupported but very valuable ODI objects shared by our community and one on the Oracle Technology Network (OTN) that will be used in the future to ship supported ODI objects.

Did you know? Our Oracle Data Integration project on already hosts dozens of Knowledge Modules, User Functions and more (SDK samples, Open Tools, Technologies etc.). Now with ODI Exchange most of this content is readily available from ODI Studio!

To get started simply open up ODI Studio 12.2.1, go to Help and click on Check for Updates

Then select the Update Centers you want to connect to:

  • Official Oracle Update Center: Hosted on OTN, this Update Center will be used to ship officially supported ODI objects from the ODI Development team. This Update Center does not contain any objects at the moment
  • Customers and Partners Update Center: Hosted on, this Update Center provides non certified but yet very valuable ODI objects created by customers, partners and Oracle employees

Click Next and then select one or more objects you would like to download.

It is also possible to filter the results using the Search box at the top. We can type ‘big data’ for example to download a Loading Knowledge Module that leverages Oracle Big Data SQL.

Once the objects are selected click Next and ODI will download the files locally. Finally click on Finish to complete the process.

The objects will be imported into your ODI repository and will be available in Designer in the Global Objects accordion.

In the next blog post of this series we will look at how you can share your global ODI objects with the rest of the ODI community!

If you want to know more about all the new features included in Oracle Data Integrator 12.2.1 have a look at the New Features document on OTN.

Tuesday Jun 09, 2015

Oracle Data Integrator Journalizing Knowledge Module for GoldenGate Integrated Replicat Blog from the A-Team

As always, useful content from the A-Team…

Check out the most recent blog about how to modify the out-of-the-box Journalizing Knowledge Module for GoldenGate to support the Integrated Replicat apply mode.

An Oracle Data Integrator Journalizing Knowledge Module for GoldenGate Integrated Replicat


Tuesday May 12, 2015

ODI 12c - Improving Usability of KM recipes

This post is all about reducing user errors and improving usability surrounding definition of Knowledge Modules and their usage. Knowledge Modules are all about encapsulating recipes - every great cookbook has lots of recipes and some are based on common techniques and ingredients. ODI Knowledge Modules are data integration recipes - they define how to access, transform and store information based on the directions in the KM. There are a few usability improvements in the recent release around both the KM definition and usage of the KM that make for an improved user experience. I've seen many KMs over the years where its many things to many people and there are a bundle of options that expose all facets for every path possible in the KM - the user has to read the description and follow the instructions.

The first improvement I'll mention is the KM (and procedure) option type of 'Choice'. Not exactly rocket science here I know, but an addition that greatly helps usage of a KM that may do more than one thing. Let's take the example of a KM that can make different .....pizzas. In the past you would have an option field which was a string based value where the user would type either margerita or pepperoni to drive a path within the KM implementation, users of the KM would have to know that those were the accepted option values and they'd have to type it in properly (otherwise things would go wrong). So now the options can be specified as the 'Choice' type, see below where in the IKM we capture the recipe type as a choice.

The choices can be defined in the default value field, below the recipe is going to either create margherita pizza or pepperoni- these are the only two choices and the default is margherita;

Then I can define all the rest of the options, let's say the pizza needs flour, oil, salt, yeast and pepperoni needs... pepperoni of course and margherita needs tomatoes and basil - so some of the options are applicable to both types and some are only applicable to the specific one. Prior to this release when the KM is used you would see all of these option values and you'd be reading the description 'only set basil if you are making margherita' and so on. Another feature has been added to improve this area. Below you can see all of the options....

One column was snipped out of the image - the condition expression. This is a groovy expression to determine whether the option is shown. So now we can say only display basil when margherita pizza is the recipe type or only display pepperoni when pepperoni is the recipe type. We see below the options only applicable to the recipe type are displayed - anything common has no condition expression.

The groovy snippet must return a string. The string must be of the format show=true|false

When you see the KM assigned in the mapping it becomes a little clearer. Below you can see the choice box, the user is constrained to pick one of those types;

 When margherita is selected above remember some options were for margherita and some were for pepperoni, we see a subset of options;

Above you can see tomatoes and basic, if you change the type to pepperoni the above options are hidden and pepperoni is displayed as below;

This helps guide the user into configuration options that are more applicable to a path within the KM. One of the other visual enhancements is the ability to group options together. We can add all of the options above into a group named 'Ingredients' that helps visually group related options together;

 Then when this is used you see the options related to ingredients from where the KM is assigned.

You can see how these help improve the usability of KMs in ODI and help reduce errors by further specializing how data is entered and related in the configuration options of the KM. The tasks within the KM can retrieve the option values and perform condition code based on those values. There are some other areas around this but that's all for now. The functionality described here is available in the

Wednesday Jan 29, 2014

ODI 12.1.2 Demo on the Oracle BigDataLite Virtual Machine

Update 4/14/2015: This blog is outdated, instructions for the current Big Data Lite 4.1 are located here

Oracle's big data team has just announced the Oracle BigDataLite Virtual Machine, a pre-built environment to get you started on an environment reflecting the core software of Oracle's Big Data Appliance 2.4. BigDataLite is a VirtualBox VM that contains a fully configured Cloudera Hadoop distribution CDH 4.5, an Oracle DB 12c, Oracle's Big Data Connectors, Oracle Data Integrator 12.1.2, and other software.

You can use this environment to see ODI 12c in action integrating big data with Oracle DB using ODI's declarative graphical design, efficient EL-T loads, and Knowledge Modules designed to optimize big data integration. 

The sample data contained in BigDataLite represents the fictional Oracle MoviePlex on-line movie streaming company. The ODI sample performs the following two steps:

  • Pre-process application logs within Hadoop: All user activity on the MoviePlex web site is gathered on HDFS in Avro format. ODI is reading these logs through Hive and processes activities by aggregating, filtering, joining and unioning the records in an ODI flow-based mapping. All processing is performed inside Hive map-reduce jobs controlled by ODI, and the resulting data is stored in a staging table within Hive.
  • Loading user activity data from Hadoop into Oracle: The previously pre-processed data is loaded from Hadoop into an Oracle 12c database, where this data can be used as basis for Business Intelligence reports. ODI is using the Oracle Loader for Hadoop (OLH) connector, which executes distributed Map-Reduce processes to load data in parallel from Hadoop into Oracle. ODI is transparently configuring and invoking this connector through the Hive-to-Oracle Knowledge Module.

Both steps are orchestrated and executed through an ODI Package workflow. 

Demo Instructions

Please follow these steps to execute the ODI demo in BigDataLite:

  1. Download and install BigDataLite. Please follow the instructions in the Deployment Guide at the download page
  2. Start the VM and log in as user oracle, password welcome1.
  3. Start the Oracle Database 12c by double-clicking the icon on the desktop.

  4. Start ODI 12.1.2 by clicking the icon on the toolbar.

  5. Press Connect To Repository... on the ODI Studio window. 

  6. Press OK in the ODI Login dialog.

  7. Switch to the Designer tab, open the Projects accordion and expand the projects tree to Movie > First Folder > Mappings. Double-click on the mapping Transform Hive Avro to Hive Staging.

  8. Review the mapping that transforms source Avro data by aggregating, joining, and unioning data within Hive. You can also review the mapping Load Hive Staging to Oracle the same way. 

    (Click image for full size)

  9. In the Projects accordion expand the projects tree to Movie > First Folder > Packages. Double-click on the package Process Movie Data.

  10. The Package workflow for Process Movie Data opens. You can review the package.

  11. Press the Run icon on the toolbar. Press OK for the Run and Information: Session started dialogs. 

  12. You can follow the progress of the load by switching to the Operator tab and expanding All Executions and the upmost Process Movie Data entry. You can refresh the display by pressing the refresh button or setting Auto-Refresh. 

  13. Depending on the environment, the load can take 5-15 minutes. When the load is complete, the execution will show all green checkboxes. You can traverse the operator log and double-click entries to explore statistics and executed commands. 

This demo shows only some of the ODI big data capabilities. You can find more information about ODI's big data capabilities at:

Wednesday Jan 22, 2014

Deep Dive Into Oracle Data Integrator Changed Data Capture Leveraging Oracle GoldenGate

Check out this blog post below from Christophe – first the details related to Oracle Data Integrator’s (ODI) Journalizing Knowledge Modules (JKMs) as well as a deeper dive into the particulars around the seamless out-of-the-box integration between Oracle Data Integrator (ODI) and Oracle GoldenGate.

Happy reading!

Thursday Jan 09, 2014

ODI - High performance data movement using Datapump

Great blog post below by Ben from the A-Team on improved Datapump support in ODI. As well as a great write up the KM is posted on the site too.

Feedback appreciated on this.

Friday Jan 03, 2014

ODI 12c - Components and LKMs/IKMs

Here I'd like to illustrate some nice capabilities of ODI 12c's knowledge module framework in combination with the new component based mapper. Some of this was prompted from recent questions from Holger Friedrich (on migration from OWB..'where is delete'), Stewart Bryson and Mark Rittman (on components and KMs here) and a general nagging thought I had that people were generally unaware of the capabilities. There's a lot more we can illustrate and talk about in this area, so this is a little taster...

Customized loading, integration and error management can be defined using knowledge modules. There is a text based substitution library that makes building such knowledge modules very easy - especially with the abundance of examples - plus the substitution reference documentation is a good reference guide (intro here, reference here). This can be used in harmony with ODI 12c components. In 12c we have modularized a lot and introduced components (components are logical and describe the WHAT of the transformation, component KMs describe the HOW, just like regular KMs), also we have formalized some parts of the framework but it is still as open as ever.

In supporting the odiRef substitution library we have chosen a similar approach to how the sub-select worked in 11g. If you want to build your own integration you can use the odiRef methods to get the target shape, details, connection etc. The source for the target, just like in 11g is provided via the odiRef methods also and may be rolled into the odiRef.getFrom method.

To illustrate, if you want to perform an integration to do a delete operation, then the following snippet is sufficient - in 11g or 12c (the code in red is boiler plate). In ODI 12c you can now have an arbitrary mapping graph leading up to the target;

  1. DELETE FROM <%=odiRef.getTable("L","TARG_NAME","A")%> T
  2. WHERE (<%=odiRef.getTargetColList("", "[COL_NAME]", ", ", "\n", "UK")%> )
  3. IN
  4. (
  5. select * from (
  6.  <%for (int i=odiRef.getDataSetMin(); i <= odiRef.getDataSetMax(); i++){%>
  7.  <%=odiRef.getDataSet(i, "Operator")%>
  8.    SELECT  <%=snpRef.getColList(i,"", "\t[COL_NAME]", ",\n", "", "UK")%>
  9.    FROM <%=odiRef.getFrom(i)%>
  10.    WHERE (1=1) <%=snpRef.getJoin(i)%> <%=snpRef.getFilter(i)%> <%=snpRef.getGrpBy(i)%> <%=snpRef.getHaving(i)%>
  11.  <%}%>
  12. ) S
  13. )

For example in the mapping below, I am using the subquery filter component to identify some rows that I then want to DELETE from the CONTACT_LIST target. The integration type property on target datastores was introduced in 12c and supports a set number of integrations to help filter the IKMs, if you select None, you can pick any IKM applicable for the technology.

If you look at the physical design for the above mapping you can now pick the IKM which performs the delete, the subquery filter also has a component KM which produces code, this code is rolled into the odiRef methods in the template. This then lets arbitrary map designs to be created and still build customized KMs for loading, integrating and error management. The odiRef substitution methods are simple text based APIs for producing text based on simple primitives.

This then produces the following SQL statement to perform the DELETE DML, our IKM has no knowledge of how to construct subquery filter, pivot or any other complex transformation - the code has been modularized. The code produced is below;
  2. IN (
  6.                = SOME  (  
  7. SELECT 
  9. FROM
  12.     )
  13.    )  
  14. )

Another useful illustration of things working in harmony is related to the improved code generation for Oracle connectivity - the basics of database links have been greatly improved (no more requirements for view on source, but still supported). The framework change in order to support this will have more use cases down the line too. Now you can use the 12c database link LKMs to pull data from tables over a database link and leverage custom IKMs on the target. One of the 11g issues was related to the requirements the 11g Oracle to Oracle database link LKM placed on users, this has been greatly improved, you can now take advantage of this, plus use existing IKMs, build new ones etc.

The mapping above uses new 12c LKMs to access remote Oracle datastores and a customized IKM to illustrate the mix and match capabilities. 

Hopefully this gives you some background and insight into the ODI 12c mapping and knowledge module capabilities that you weren't aware of. Looking forward to any questions and additional ideas, insights that you have.

Wednesday Mar 28, 2012

New Feature in ODI ODI for Big Data

By Ananth Tirupattur

Starting with Oracle Data Integrator, ODI is offering a solution to process Big Data. This post provides an overview of this feature.

Before getting into the details of ODI for Big Data and with all the buzz around Big Data, I will provide a brief introduction to Big Data and Oracle Solution for Big Data.

[Read More]

Thursday Jan 12, 2012

ODI SDK: Reporting on KM Use in Your Projects

Leverage the ODI SDK to report on KMs usage across interfaces in a given project.[Read More]

Monday Nov 09, 2009

The Benefits of ODI Knowledge Modules: a Template Approach for Better Data Integration

This post assumes that you have some level of familiarity with ODI. The concepts of Knowledge Module are used here assuming that you understand them in the context of ODI. If you need more details on these elements, please refer to the ODI Tutorial for a quick introduction, or to the complete ODI documentation for detailed information..

At the core, ODI knowledge modules are templates of code that will be leveraged for data integration tasks: they pre-define data integration steps that are required to extract, load, stage - if needed - and integrate data.

Several types of Knowledge Modules are available, and are grouped in families for Loading operations (LKMs), Integration operations (IKMs), Data Control operations (CKMs), and more.

For a more detailed description of what a knowledge module is, simply picture the multiple steps required to load data from a flat file into a database. You can connect to the file using JDBC, or leverage the database native loading utility (sqlldr for Oracle, bcp for SQL Server or Sybase, load for db2, etc.). External tables are another alternative for databases that support this feature.
As you use one or the other technique, you may first want to stage the data before loading your actual target table; in other cases, staging will only slow down your data load.

As far as the integration in the target system is concerned, again multiple strategies are available: simple inserts, inserts and updates, upserts, slowly changing dimension... these techniques may be as simple as one step, or be a complex series of commands that must be issued to your database for proper execution.

The Knowledge Modules will basically list these steps so that a developer who needs to repeat the same integration pattern only has to select the appropriate templates, versus re-developing the same logic over and over again.

The immediate benefits of this approach are well known and well documented:
- All developers use the same approach, and code development is consistent across the company, hence guarantying the quality of the code
- Productivity is greatly improved, as proven path are re-used versus being re-developed
- Code improvement and modification can be centralized and has a much broader impact: optimization and regulatory changes are done once and inherited by all processes
- Maintenance is greatly simplified

To fully appreciate all the benefits of using knowledge Modules, there is a lot more that needs to be exposed and understood about the technology. This post is a modest attempt at addressing this need.


Most tools today will offer the ability to generate SQL code (or some other type of code, such as scripts) on your source or target system. As most products come with a transformation engine, they will also generate proprietary code for this engine where data is staged (I'll skip the debate here as to whether a transformation engine is a staging area or not - the point being that code can be generated on either source, "middle-tier" or target).

However, real life requirements are rarely either/or. Often times, it makes sense to leverage all systems to optimize the processing: spread out the load for the transformations, reduce the amount of data to be transferred over the network, process the data where it is versus moving the data around solely for the purpose of transformations.

To achieve this, Data Integration tools must be able to distribute the transformation logic across the different systems.


Only ODI will effectively generate code and transformations on all systems. This feature is only possible thanks to the KM technology.

Beyond the ability to generate code, you have to make sure that the generated code is the best possible code for the selected technology. Too often, tools first generate code that is then translated for the appropriate database. With the KMs technology, no translation is required: the generated code was initially conceived explicitly for a given technology, hence taking advantage of all the specifics of this technology.

And since the KMs are technology specific, there is no limit to what can be leveraged on the databases, including user defined functions or stored procedures.



Whenever a tool generates code, the most common complaint is that there is very little (if any) control over the generated result. What if a simple modification of the code could provide dramatic performance improvements? Basic examples would include index management, statistics generation, joins management, and a lot more.

The KM technology is open and expansible so that developers have complete control over the code generation process. Beyond the ability to optimize the code, they can extend their solution to define and enforce in house best practices, and comply with corporate, industry or regulatory requirements. KMs Modifications are done directly from the developers graphical interface.

One point that can easily be adapted is whether data have to be materialized throughout the integration process. Some out-of-the-box KMs will explicitly land data in a physical file or tables. Others will avoid I/Os by leveraging pipes instead of files, views and synonyms instead of tables. Again, developers can adapt the behavior to their actual requirements.


How much time does it take to adapt your code to a new release of your database? How much time does it take to add a new technology altogether? In both cases, KMs will provide a quick and easy answer.

Let us start with the case of a new version of the database. While our engineering teams will release new KMs as quickly as possible to take advantage of the latest releases of any new database, you do not have to wait for them. A new release typically means new parameters for your DDL and DML, as well as new functions for your existing transformations. Adapt the existing KMs with the features you need, and in minutes your code is ready to leverage the latest and greatest of your database.

Likewise, if you ever need to define a new technology that would not be listed by ODI (in spite of the already extensive list we provide), simply define the behavior of this technology in the Topology interface, and design technology specific KMs to take advantage of the specific features of this database. I can guaranty you that 80% of the code you need (at least!) is already available in an existing KM... Thus dramatically reducing the amount of effort required to generate code for your own technology.


I am a strong advocate of the customization of KMs: I like to get the best I can out of what I am given. But often times, good enough is more than enough. I will always remember trying to optimize performance for a customer: we did not know initially what our processing window would be - other than "give us your best possible performance". The first out-of-the-box KM we tried processed the required 30,000,000 records in 20 minutes. Due to IT limitations, we could only leverage lesser systems for faster KMs... but still reduced performance to 6 minutes for the same volume of data. We started modifying KMs to get even better results, when the customer admitted that we actually had 3 hour for the process to complete... At this point, spending time in KM modifications was clearly not needed anymore.

KMs are meant to give the best possible performance out of the box. But every environment is unique, and assuming that we can have the best possible code for you before knowing your own specific challenges would be an illusion - hence the ability to push the product and the code to the limit

Another common question is: do you have to leverage both source and target systems as part of your transformations? Clearly, the answer is no. But in most cases, it is crucial to have the flexibility to leverage all systems, versus being cornered in using only one of them. Over time, you will want to reduce the volume of data transferred over the network; you will want to distribute some of your processing... all more reasons to leverage all available engines in your environment.

Do not hesitate and share with us how you extend your KMs!

Screenshots were taken using version of ODI. Actual icons and graphical representations may vary with other versions of ODI.


Friday Oct 09, 2009

Did You Know that ODI Automatically Summarizes Data Errors?

Looking for Data Integration at OpenWorld 2009? Click here!

This post assumes that you have some level of familiarity with ODI. The concepts of Interface, Flow and Static Control, as well as Knowledge Module are used here assuming that you understand them in the context of ODI. If you need more details on these elements, please refer to the ODI Tutorial for a quick introduction, or to the complete ODI documentation for detailed information..


If you take advantage of either Flow Control or Static Control in your interfaces, you know that ODI will automatically trap errors for you as you run your interfaces.

When you select the Controls tab of your interface, where you will decide which Knowledge Module will be used to identify the errors, you have an option to drop the Error table and another one to drop the Check table. Have you ever wondered what these are?

Interface Controls Tab

The Error table is the table that will be created by ODI to store all errors trapped by the FLOW_CONTROL and STATIC_CONTROL of your interface. You have probably already used the error table. This table is structured after your target table, along with administrative information needed to re-cycle or re-process the invalid records. It is loaded by ODI with all records that fail to pass the validation of the rules defined on your Target table. This feature is often referred to as a Data Quality Firewall as only the "good" data will make it to the target table.

Once all errors have been identified for a given interface, ODI will summarize them into another table: the Check table. There will be only one such table per data server: all target tables in the server (irrespectively of their schema) will share the same summary table. The name of this table is defined by default by the CKMs as SNP_CHECK_TAB.


You will find the check table in the default work schema of your server. To locate this schema, you have to go back to topology, in the Physical Architecture tab. Expand your data server to list the different physical schemas. One of the schemas is your default schema and will be identified by a checkmark on the schema icon (see SALES_DWH in the example below).

Default Schema

When you edit the schema, it has an associated work schema. The work schema associated to your default schema is your default work schema: ODI_TMP is the following example.

Default Work Schema


Note that you can change your default schema by selecting/unselecting the default option in the schema definition. But remember that you will always need exactly one default schema for each server.


Now that we know where to find this table, let's look at its structure:

  • CATALOG_NAME, SCHEMA_NAME: location of the table that was being loaded (i.e. the target table)
  • RESOURCE_NAME, FULL_RES_NAME: name of the table that was being loaded
  • ERR_TYPE: type of control that was performed (Flow Control or Static Control)
  • ERR_MESS: plain English error message associated with the error
  • CHECK_DATE: date and time of the control
  • ORIGIN: name of the ODI process that identified the errors
  • CONS_NAME: name of the constraint (as defined in the ODI Models) that defines the rule that the record violated
  • CONS_TYPE: type of error (duplicate primary key, invalid reference, conditional check failed, Null Value)
  • ERR_COUNT: number of records identified by the process that failed to pass that specific control rule.



A sample of the data available in that summary table is show below (we split the content in 2 screenshots to make this more readable - this is one and only one table):

Errors Summary Data

Errors Summary Data2

There are many possible uses for this table: decision making in your ODI processes based on the number of errors identified or the type of errors identified, basic reporting on errors trapped by ODI, trend analysis or the evolution of errors over time...

Do not hesitate and share with us how you leverage this table!

Screenshots were taken using version of ODI. Actual icons and graphical representations may vary with other versions of ODI.



Learn the latest trends, use cases, product updates, and customer success examples for Oracle's data integration products-- including Oracle Data Integrator, Oracle GoldenGate and Oracle Enterprise Data Quality


« February 2017