Tag: Informatica

Business Intelligence Platform, Data Development, Data Integration, Data Integration (ETL)

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.

Data Integration (ETL)

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.

regedit

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.

regedit_before

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.
regedit_values_before

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.
regedit_values_after

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’.

regedit_after

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.

Business Intelligence Platform, Data Integration (ETL)

DAC Execution Plan Failure – “No active subject areas for execution plan”

When your Oracle Business Intelligence Applications (OBIA) DAC Execution Plan fails with a message that looks like this …

“Retrieving execution plan informationMESSAGE:::com.siebel.analytics.etl.execution.exceptions.EmptyExecutionPlanException: No active subject areas for execution plan were found
EXCEPTION CLASS::: com.siebel.analytics.etl.execution.ExecutionPlanInitializationException

com.siebel.analytics.etl.execution.ExecutionParameters.(ExecutionParameters.java:111)
com.siebel.analytics.etl.execution.ExecutionParameterHelper.(ExecutionParameterHelpe”

It is likely that you have a missing Subject Area in your Execution Plan in DAC.  You need to attach the Subject Area to the Execution Plan and rebuild the Plan.

After doing this your job should run successfully.

 

Data Integration (ETL)

Informatica Command-line Programs

Frequently used Informatica Programs are:

  • pmcmd – used to manage workflows, such as starting, stopping and scheduling
  • pmrep – used to perform PowerCenter Repository administration tasks, such as update repository information and perform repository functions
  • infacmd – used to administer Informatica application services
  • infasetup – used to administer Informatica domain and nodes
  • pmpasswd – used to encrypt passwords for use in parameter files or environment variables

Command-line and Interactive Execution
All 5 programs (pmcmd, pmrep, infacmd, infasetup, and pmpasswd) can be executed in Command-line mode.
Three of them (pmcmd, pmrep and infacmd) can be executed in Interactive mode.

Program Locations
All programs except infasetup are located in [InformaticaInstallDirectory]/server/bin.  infasetup is located in [InformaticaInstallDirectory]/server.

Summary
Below is a table that summarizes the features/usage of each of these programs into one location:

InformaticaCommandLinePrograms

Business Intelligence (BI) Development, Business Intelligence Platform, Data Integration (ETL)

Informatica Transformations Frequently used in OBIA

These are some of the Informatica transformations that are frequently used in Oracle Business Intelligence Applications (OBIA).  The OBIA SDE and SIL mappings used to load the Oracle Business Analytics Warehouse (OBAW) are built using these and other transformations.

1. Source Qualifier
The Source Qualifier transformation is used to bring data from one or more tables from the same source into the mapping.  If being used for more than one table, then a join condition needs to be defined between the tables.  The typical naming convention for a Source Qualifier transformation is SQ_* or sq_*.

2. Joiner
The Joiner transformation is used to join tables in different data sources.  The typical naming convention for a Joiner transformation is JNR_* or jnr_*.

3. Expression
The Expression transformation is used to perform simple row-based calculations or derivations.  The typical naming convention for an Expression transformation is EXP_* or exp_*.

4. Filter
The Filter transformation is similar to a where clause in SQL – it adds a conditional filter to the data passing through the mapping.  The typical naming convention for a Filter transformation is FIL_* or fil_*.

5. Aggregator
The Aggregator transformation is used to perform aggregate calculations on the data passing through the mapping, for example, performing a sum or max.  The typical naming convention for an Aggregator transformation is AGG_* or agg_*.

6. Lookup
The Lookup transformation is used to lookup values based on another known/submitted value, and pass the looked up value into the mapping.  There are 2 types of Lookups – connected and unconnected.  The typical naming convention for a Lookup transformation is LKP_* or lkp_*.

7. Update Strategy
The Update Strategy transformation is used to determine and perform the appropriate course of action for data in the mapping.  Based on the determined state of the data, the transformation is used to insert, update, delete or reject records.  The typical naming convention for an Update Strategy transformation is UPD_* or upd_*.