Issue/Error with ODI Studio right click

As I work with the Oracle Business Intelligence Applications (OBIA) repository in ODI studio I have recently noticed I am no longer able to right click on objects. I have found two solutions, the first one is a work-around:

 

Work Around:

Let’s assume you want to right click on a particular folder or scenario, you notice as you do so the context menu does not come up, go ahead and do the following:

  1. Select the object with a left click
  2. Move your mouse pointer outside the object’s boundary, I prefer a little bit to the right
  3. Right click, the context menu should come up now

This work around works if you are restricted on changing your installation’s settings or using a hosted platform such as Citrix

 

Solution:

In cases where you have access to install software your system then you should look into the compatibility matrix for ODI Studio and the version of Java you are working with. In my case I noticed the hosting provider for my environment has setup JDK 1.7  64-bit, I noticed for some versions of ODI JDK 1.6 was required so I downloaded both 32 and 64 bit versions and pointed my odi.conf file to them version. The 64 bit version did solve my issue, which is great since I can allocate more memory to the client under this bit version.

 

Related:

ODI Tip: How to make sure a “Select distinct” is issued and an ODI interface returns a unique dataset with no duplicates

PROBLEM

 

As a developer I do have a need to make sure that the subset of columns I am mapping through from source to target on my ODI interface is unique, in other words, I want ODI to include a DISTINCT clause on the SELECT statement that will be issued on the source database.

 

SOLUTION

  • Open my interface on the ODI Interface designer
  • Click on the Flow tab on the bottom
  • Click on the Target object
  • On the Property Inspector, click on the “Distinct Rows” checkbox

    image

ETL Tuning in ODI / BI Apps–The #ETL_ANALYZE_WORK_TABLE parameter

One of the first things I do when I run into performance issues with ETL loads is to look at the source and target table statistics. Have they been collected before the current select / insert statement was issued?

It turns out that in Oracle BI Apps the #ETL_ANALYZE_WORK_TABLE parameter is turned off by default when a load plan is generated. This can make doing a high level review of your load plan execution tricky since there will be steps that will seem to be gathering statistics, when in reality, the ODI code generator just puts a placeholder instead of the code for statistics. An example of this is shown below:

 

image

 

image

 

SOLUTION:

Once I realized what the issue was with statistics not being gathered for my work tables I was able to zoom into the ETL_ANALYZE_WORK_TABLE variable by looking at my generated load plan as depicted below, and change its default value to Y. The variable is defined globally so once you change the definition this new default value will apply to any newly generated load plans.

 

image  

 

image

ODI: Purging OLD Sessions

One common administrative task that I find myself doing when I realize that my ODI logs are growing fairly large is purging old sessions from the log. The steps are fairly straightforward as follows:

 

  1. Login to your ODI Studio client
  2. To to the Operator View
  3. On the top right corner of your navigation pane, expand the menu and select purge log…

    image

  4. On the Purge Log screen you can select which old sessions to remove by date, agent, context, status, user and session name

    image

  5. Once you have set parameters as desired click on OK and the ODI session logs will be purged accordingly

 

Related:

Error: Unable to access Oracle Data Integrator repository. You will not be able generate or execute load plans.

Oracle Data Integrator (ODI) Logo

Upon logging to the Oracle Business Intelligence Application’s Configuration Manager system I was greeted with an error message stating the following: “Unable to access Oracle Data Integrator repository. You will not be able generate or execute load plans.”

 

Upon some triage I was able to determine a couple of possible root causes to my issue.

 

  1. Not using the BIAdmin user created during the install process, or, the BIAdmin user account was not created.
  2. Current session was initiated with a set of credentials that haven’t been granted the BIA_ADMINISTRATOR_DUTY  role on weblogic security
  3. Additional roles are missing or not assigned to the credentials initiating the current session

 

After reviewing all possible options and confirming I am using a valid account with the proper roles and permissions I asked my system administrator to restart the server, this fixed my issue. I should have started there but at least I came out of the experience with a better understanding of the roles that control security in my installation of OBIA 11.1.1.7.0.

 

Last resort, if all else fails:

* Make sure after you have regenerated and moved the security files that the file odi.conf is updated to refer to jps-config-jse.xml.

ODI Error: ora-00001–unique constraint ( pk_pop ) violated

SYMPTOM:

 

image_thumb1

odi-10182: Uncategorized exception during repository access

ora-00001: unique constraint ( pk_pop ) violated

 

CAUSE:

 

There is another interface with the same name on the repository.  Interface names are unique and independent of their folder location. In my case I had an interface with the same on a different folder.

 

SOLUTION:

 

Rename or delete your duplicate interface.

Error: ora-00001: unique constraint ( pk_txt_header ) violated

SYMPTOM:

image

odi-10182: Uncategorized exception during repository access

ora-00001: unique constraint ( pk_txt_header) violated

 

CAUSE:

The table definition was created by duplicating metadata for another table. Indexes are defined as constraints in ODI, when duplicating the original table object the index definitions are duplicated with the original names, for some reason this does not trigger an error at the time of duplicating the tables, the primary key validation for the index name is triggered –in my case- at the time of saving a mapping where the table duplicate is a target.

 

SOLUTION:

On Design > Models look up the duplicate table. Once located review and update the names of each of the constraints / indexes defined on metadata and save them. Once you have completed the review you should be able to save the mapping throwing the original issue message.

Informatica being replaced by ODI in Oracle BI Apps 7.9.5.2

Oracle Data Integrator (ODI) Logo

In a long anticipated move among both Fusion Intelligence and Oracle BI Apps customers, Oracle has released the first versions of its packaged business intelligence solution that replaces the third party tools of previous versions (IBM DataStage or Informatica Power Center) with Oracle Data Integrator as the back end ETL tool. You can find detailed install and configuration instructions in the Oracle Business Intelligence Applications 7.9.5.2 Documentation Library.

 

It is important to note that this is a controlled availability release that mainly means the only data source supported is Oracle E-Business Suite 11.5.10, marts available in this release are:

 

  • Financial Analytics
  • HR Analytics
  • SCM and Order Managment Analytics
  • Procurement and Spend Analytics

Limitations notwithstanding, this is a key move that signals a long term strategy for BI Apps that will require customers and partners to develop additional skills and an important upgrade consideration for EBS customers. I personally hope that we will see Oracle implement Golden Gate at some point down the road in order to provide real time operational intelligence for a limited set of KPIs that lend themselves to be used in BAM applications.

Sunopsis seven years after it’s acquisition

Oracle Data Integrator (ODI) Logo

Oracle Data IntegratorOn October of 2006 Oracle acquired Sunopsis a sofware company whose flagship product was one of the up-and-coming players in the datata warehousing arena. Sunopsis for easy integration in environments with multiple database systems and differentiated itself by adopting an Extract-Load-Transform architecture that decreased the learning curve for database developers that were immediately able to create transformation using familiar SQL syntax. By utilizing the computing power in the target database Sunopsis’ ELT architecture potentially decreased the need for additional computing power in a dedicated ETL server. Fast forward five years and Sunopsis is now Oracle Data Integrator, it is a key component of the Fusion Middleware Data Integration product line and it’s looking good, a few features that large enterprise customers will find interesting are:

  • SOA enabled an can deploy and consume web services for data transformation and integration
  • Integrates well into your data cleansing (names, addresses) and Master Data Management framework
  • Out of the box CDC functionality with a Publish-and-Subscribe model implemented either via triggers or log mining (Oracle and DB2)
  • As previously stated, by utilizing the source and target database’s computing power ODI decreases it Total Cost of Ownership (TCO)

It seems as if the merging of Oracle Warehouse Builder and Sunopsis’ functionalities has been beneficial to the end product and, in general, to the enterprise customer. Now it remains to be seen how aggressively Oracle”s sales force can take market share from ODI’s top competitors IBM Information Server and Informatica PowerCenter. Stay tunned for additional product reviews and, of course, OBIEE and Business Intelligence updates…