InfatoODI – Informatica to ODI conversion tool

We are currently in the process of upgrading Oracle Business Intelligence Applications (OBIA) from version 7.9.6 to OBIA 11g.  Oracle has replaced Informatica as the data integration tool in the platform with it’s own tool, Oracle Data Integrator (ODI). This was a selfish, profit-driven move on Oracle’s part with no consideration for the impact on customers, but it is what it is.

Because of this, as a part of the upgrade to the new OBIA release, we need to convert all our hundreds of Informatica mappings to ODI.  As you can imagine, this is a lot of work.  We are getting help from a company that has developed a specialized conversion tool called InfatoODI, which converts Informatica mappings to ODI interfaces.

We are performing the conversions specifically for an OBIA application, but the tool can be used as a straight conversion tool for Informatica-to-ODI for any type of application.

We are in the beginning stages of the project, but early indications are that the tool will save us time, but I am not sure how significant as yet. I will post updates as we progress through the conversions with my experience and opinion of the tool.


Oracle Data Integrator (ODI) Knowledge Modules (KMs)

I am currently working on a project to upgrade (Oracle Business Intelligence Applications) OBIA 7 to OBIA 11g.  OBIA 11g and all future releases of OBIA (per Oracle) will use Oracle Data Integrator (ODI) as the ETL platform, replacing Informatica.

Due to this, I need to become very familiar with ODI to be able to manage and support the new release, and will be writing about ODI from time to time.

One key component in ODI is Knowledge Modules (KM’s).  In this post, I will describe what Knowledge Modules are and the various types that are in ODI.

Knowledge Modules (KMs) are generic code templates or modules that can be configured/coded to meet specific data integration needs and each type is dedicated to a specialized function in the overall data integration process.

Each of the 6 out-of-the-box (OOB) Knowledge Modules contain the “knowledge” to perform a specific set of actions on a specific combination of technologies, including connecting, extracting, transforming, loading, and checking data.  While the 6 OOB KMs meet most data integration needs, there will surely be cases when more custom features are needed. ODI KMs are extensible, and new totally custom KMs can be built.

The 6 OOB KMs are:

Reverse Knowledge Module (RKM)
This KM is used to retrieve metadata from data sources and targets to the Oracle Data Integrator work repository. You can use it in models to perform customized reverse-engineering.

Loading Knowledge Module (LKM)
This KM is used to load heterogeneous data to a staging area. It is used in interfaces with heterogeneous sources. The LKM and the IKM are the two most frequently used KM’s in our environment.

Journalizing Knowledge Module (JKM)
This KM is used in models, sub models and databases to create, start and stop journals and to register subscribers. It creates the Change Data Capture framework objects in the source staging area.

Integration Knowledge Module (IKM)
This KM is used in Interfaces to integrate data from the staging area to a target. The LKM and the IKM are probably the two most frequently used KM’s in our environment.

Check Knowledge Module (CKM)
This KM is used to perform consistency checks of data against defined constraints. It is used in models, sub models and databases for data integrity audit, and used in interfaces for flow control or static control.

Service Knowledge Module (SKM)
This KM is used in models and databases. It is used to generate data manipulation web services.

These KM’s are central to ODI and I will need to master the usage of these KM’s and if you are planning on using ODI, you will need to also.

Some workflows not showing in Informatica Workflow Monitor workflow run

If you run a lot of workflows and have noticed that you are not seeing some of the workflows in the Workflow Run window, this post may help.

Your workflows might be running, but you are not seeing some of them because you have exceeded the maximum number of workflow runs that will be stored in each of the folders.

In Informatica Workflow Monitor, change the “Maximum workflow runs per folder” property.  From Informatica Workflow Monitor, select the Tools -> Options menu item.


Then, change the “Maximum workflow runs per folder” to a number that is higher than the highest number of workflows that are run in your cycle for a given folder.


Save changes.  You will now see more workflows in the output after your next job cycle.


Maximum number of characters allowed in the Informatica SQL Qualifier & Lookup transformations’ SQL Query

The Informatica SQL Qualifier and Lookup transformations’ SQL Query parameter has a limitation on the number of characters that your SQL can contain. There is no limitation on the number of lines, the limitation is on the number of characters.

Your SQL is allowed a maximum of of 32,767 characters. If you copied and pasted SQL into the field, after 32767 characters the query will be truncated.  If you are typing into the field, after 32767 characters, no more characters can be entered.

To reduce your SQL character count, removing unnecessary spaces used for formatting will help. You may also examine your aliases to see if they can be shortened.

Unable to see the SQL Override SQL in Informatica SQL Qualifier Transformation

I had an issue in Informatica where I was not able to see the SQL Override SQL code in the SQL Qualifier.

It turns out this was caused by switching the application interface between double monitors or disconnecting from double monitors while one of the Informatica application windows was on the secondary monitor screen, which led to changed settings in the registry.

I found a resolution on the Oracle site and this post explains how to resolve in detail.

To correct this, you need to update the appropriate registry settings. As a precautionary measure, you should backup your registry before making changes to it.

Follow these steps to resolve.
1. Close out all Informatica applications.
2. Click your Windows start icon, and type in regedit.   Then double-click “regedit.exe” to run it.


3. From the Registry Editor window, navigate as follows …

HKEY_CURRENT_USER -> Software -> Informatica -> PowerMart Client Tools -> 9.5.1 -> Designer -> Options -> Global -> Editor -> SQL
Note: The path will vary based on your installed version. So, instead of “9.5.1”, you may have another version number.


4. Locate these 4 values, which you will update, on the right-hand side of the Registry Editor window (highlighted in the image above):

  • Expression Editor Position
  • Expression Editor Splitter Position
  • SQL Editor Position
  • SQL Editor Splitter Position

5. Edit each of the 4 values one at a time, by double-clicking on them.

regedit_update_valuesAnd you will be changing the 16 values highlighted in the image above.

This is an example of what the values will look like before you make any changes.

In the Edit Binary Value window, update the 16 double character values to all be ‘00’

For each key value, the Edit Binary Value window should look like this after the update.

6. Click ok. Then repeat for each of the other values.

When you have updated all 4 keys, your Registry Editor window should look like this. Note that the values are now all ’00’.


7. Close the Registry Editor and open your Informatica application and check if it is resolved.  Hopefully, it will be.

I haven’t done any exhaustive testing on this to determine all scenarios under which this happens, but I think the way to prevent this is, if you are using multiple monitors, use only your primary monitor for Informatica applications, or bring your Informatica application windows back to your primary monitor before closing them and before disconnecting your computer from the secondary monitor.

OBIA Financial Analytics – SIL_APTransactionFact_DiffManLoad performance issues

We are on Oracle Business Intelligence Applications (OBIA) and had been experiencing performance issues with the SIL_APTransactionFact_DiffManLoad workflow/mapping. We tried a number of things but only had minimal improvements.   Eventually, I found a solution for the poor performance on Oracle Support.  This change resulted in a drastic improvement of this workflow.

The solution can be found on Oracle Support ( – Oracle Doc ID: 1446397.1), but for your convenience I have included the content below.  There are other mappings that have a similar problem.


OBIA 7963: SIL_APTransactionFact_Diffmanload Mapping And Performance Issue (Doc ID 1446397.1)

In this Document

 Applies to:
Informatica OEM PowerCenter ETL Server – Version [AN 1900] and later Information in this document applies to any platform.

The OBIEE application ( ETL task “SIL_APTransactionFact_DiffManLoad” has run over 68 hours during full load execution.


  1. The size of these columns (DOC_HEADER_TEXT and LINE_ITEM_TEXT )  in DAC is 255 (except AP where its 1020 in DAC and Infa). But in Informatica the size for these two columns is 1020. Ideally it should be 255. This is a known performance issue.
  2. The cause of the problem has been identified in unpublished  Bug 12412793- PSR: B16 INCREMENTAL: SIL_GLREVENUEFACT,

Below are the steps you will follow to modify the size of the fields in the lookup.

  1. Take a backup of existing Lookups ( LKP_W_AP_XACT_F and LKP_W_AR_XACT_F ).
  2. Login to Informatica Designer >Transformations
  3. Open the lookup and modify the size of the fields. The port lengths for the DOC_HEADER_TEXT and LINE_ITEM_TEXT were changed to 255 .
  4. Save the changes
  5. Rerun the test and confirm the performance issue is resolved  and migrate the changes in PROD.


Informatica Unconnected Transformations

There are 3 Informatica transformations (External Procedure, Lookup, and Stored Procedure) that can be unconnected in a valid mapping.  An unconnected transformation is one that is not connected to the pipeline – that is, it is not connected to any other transformation via a link.  Unconnected transformations, especially unconnected lookup transformations, are widely in OBIA mappings.

Unconnected transformations are defined in a mapping and are called / invoked from another transformation in the mapping.  They can be called from any transformation that supports the transformation expression language.

Below is a list of the 3 Informatica Unconnected Transformations and how they are called or invoked from another transformation:

 Transformation  How its called / invoked
 External Procedure  :EXT.external_procedure_transformation(argument1, argument2, …)
 Lookup  :LKP.lookup_transformation(argument1, argument2, …)
 Stored Procedure  :SP.stored_procedure_transformation( argument1, argument2, [, PROC_RESULT])

Any mapping with an unconnected transformation must also include another transformation that calls the unconnected transformation.

All of the 3 Unconnected Transformations can also be used in connected mode.  However, when you want to execute the transformation logic for a subset of the rows passing through the mapping (especially when it’s a small percentage of the rows), you will want to consider using the transformations in unconnected mode and with conditional logic.  The conditional logic will cause the unconnected transformation to execute only when necessary, and therefore, may improve perfiormance.

The example below shows a call to an unconnected lookup transformation (lkp_item_id) that is invoked only when the desired item_id value is NULL.  In a case where only a small percentage of the rows have an item_id that is NULL, then the lookup would only be invoked for a small number of rows, as opposed to all rows if the transformation was connected.

                       IIF  ( ISNULL(item_id),   :LKP.lkp_item_id ( item_name ),   item_id )

As a side note, the Sequence Generator transformation can also be invoked from a function call, but it is a connected transformation.

 Transformation  How its called / invoked
 Sequence Generator  :SEQ.sequence_generator_transformation.CURRVAL