Category: Oracle Data Integrator (ODI)
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:
- Select the object with a left click
- Move your mouse pointer outside the object’s boundary, I prefer a little bit to the right
- 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
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:
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.
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:
- Login to your ODI Studio client
- To to the Operator View
- On the top right corner of your navigation pane, expand the menu and select purge log…
- On the Purge Log screen you can select which old sessions to remove by date, agent, context, status, user and session name
- Once you have set parameters as desired click on OK and the ODI session logs will be purged accordingly
Related:
Error when importing work repository in ODI Studio (java.lang.OutOfMemoryError: Java heap space)
INTRO
I am having an issue with the work repository in one of my environments this week to the point where I had to rebuild it. After dropping and recreating the schema I am running on a java heap space error.
SOLUTION
In my case the issue went away with the following steps:
- Unpack the repository content ZIP file I was importing into an uncompressed folder
- Up the MaxPermSize parameter on my ODI\client\odi\bin\odi.conf filefrom 512M to 1024M
FULL ERROR MESSAGE
java.lang.OutOfMemoryError: Java heap space
at java.lang.Class.getDeclaredMethods0(Native Method)
at java.lang.Class.privateGetDeclaredMethods(Class.java:2427)
at java.lang.Class.getDeclaredMethod(Class.java:1935)
at com.sunopsis.tools.core.SnpsTools.getMethodFromHierarchy(SnpsTools.java:370)
at com.sunopsis.tools.core.SnpsTools.getMethodFromHierarchy(SnpsTools.java:392)
at com.sunopsis.tools.xml.SnpsXmlObjectParser.processValue(SnpsXmlObjectParser.java:611)
at com.sunopsis.tools.xml.SnpsXmlObjectParser.endElement(SnpsXmlObjectParser.java:270)
at oracle.xml.parser.v2.NonValidatingParser.parseElement(NonValidatingParser.java:1588)
at oracle.xml.parser.v2.NonValidatingParser.parseRootElement(NonValidatingParser.java:442)
at oracle.xml.parser.v2.NonValidatingParser.parseDocument(NonValidatingParser.java:388)
at oracle.xml.parser.v2.XMLParser.parse(XMLParser.java:232)
at com.sunopsis.tools.xml.SnpsXmlObjectParser.parseXmlFile(SnpsXmlObjectParser.java:390)
at com.sunopsis.tools.xml.SnpsXmlObjectParser.parseXmlFile(SnpsXmlObjectParser.java:337)
at com.sunopsis.tools.xml.SnpsXmlObjectParser.parseXmlFile(SnpsXmlObjectParser.java:347)
at com.sunopsis.dwg.DwgObject.doImport(DwgObject.java:6747)
at com.sunopsis.dwg.DwgObject.doImport(DwgObject.java:6620)
at com.sunopsis.dwg.DwgObject.doImport(DwgObject.java:6578)
at com.sunopsis.repository.manager.RepositoryManager.importObjectsUsingDoImport(RepositoryManager.java:5918)
at com.sunopsis.repository.manager.RepositoryManager.treatObjectListGeneral(RepositoryManager.java:3985)
at com.sunopsis.repository.manager.RepositoryManager.workRepositoryImport(RepositoryManager.java:4506)
at com.sunopsis.repository.manager.RepositoryManager.access$7(RepositoryManager.java:4395)
at com.sunopsis.repository.manager.RepositoryManager$2.doAction(RepositoryManager.java:4369)
at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:216)
at oracle.odi.core.persistence.dwgobject.TransactionalDwgObjectTemplate.execute(TransactionalDwgObjectTemplate.java:64)
at com.sunopsis.repository.manager.RepositoryManager.internalWorkRepositoryImportWithCommit(RepositoryManager.java:4357)
at com.sunopsis.repository.manager.RepositoryManager.workRepositoryImport(RepositoryManager.java:4661)
at com.sunopsis.repository.manager.RepositoryManager.workRepositoryImportFromZipFile(RepositoryManager.java:4814)
at com.sunopsis.repository.manager.RepositoryManager.workRepositoryImportFromZipFileWithCommit(RepositoryManager.java:4884)
at com.sunopsis.repository.manager.RepositoryManager.workRepositoryImportFromZipFileWithCommit(RepositoryManager.java:4939)
at com.sunopsis.graphical.dialog.SnpsDialogImportWork$1.run(SnpsDialogImportWork.java:155)
at oracle.ide.dialogs.ProgressBar.run(ProgressBar.java:655)
at java.lang.Thread.run(Thread.java:662)
RELATED
Other related issues I found when researching the solution are:
Error: Unable to access Oracle Data Integrator repository. You will not be able generate or execute load plans.
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.
- Not using the BIAdmin user created during the install process, or, the BIAdmin user account was not created.
- Current session was initiated with a set of credentials that haven’t been granted the BIA_ADMINISTRATOR_DUTY role on weblogic security
- 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.
How to define an index on a source or target table in ODI
The topic of indexes in Oracle Data Integrator (ODI) is an easy one once you learn your way around in ODI studio. Indexes are defined under Design > Models > [your model] > [Table] > [Constraints]. They are independent of the column definitions for the most part although they do reference valid column definitions.
- The first step to create metadata for an index is to add a new constraint and name it:
- On the Columns tab define the list of columns to be indexed
- Ensure that the index is active and marked for creation on the database on the Control tab
- Review all additional options on the Flexfields tab, for some of the options such as Index Type you do need to manually type your selection.
After you have defined an index on metadata you need to run a new load for the index to be created on the database.
My Tools:
Oracle Business Intelligence Applications (OBIA) 11.1.1.7.0
Oracle Data Integrator 11g (ODI Studio, ODI Server, no clustering)
How to Make sure an index is defined as unique in odi
Indexes are defined as constraints on the Model view in ODI Studio. To make a field unique follow one of the two alternatives listed below.
PRIMARY KEY
Define the Index as a Primary Key constraint object on the Models area within the design view
Add the unique columns on the Columns tab
In the Control tab make sure the constraint is active and marked to be defined in the database for both flow and static control checkboxes
Review that the correct settings are configured in the Flexfields tab
ALTERNATE KEY
Define the constraint object for your table as an alternate key in the ODI models area
Add the unique columns on the Columns tab
In the Control tab make sure the constraint is active and marked to be defined in the database for both flow and static control checkboxes
Review that the correct settings are configured in the Flexfields tab
How To: Download, install, configure and verify you have the latest version of Opatch
On this post we will discuss all the steps necessary to ensure you have the correct version of the OPatch patching utility for oracle software running on your system. If you find this post useful please or Share our site!
Environment Variables:
Set your ORACLE_INSTANCE path to a valid OBIEE 11g instance
Set your ORACLE_HOME path to the OBIEE 11g home (Oracle_BI1)
Validating Your Environment:
OPatch is installed by default, locate it go to the ORACLE_HOME path for your OBIEE installation:
\Oracle_BI1\OPatch
To verify your current session is correctly configured verify the installed version by running the following command:
opatch version
Minimum Required Versions:
OBIEE 11.1.1.5.0 -> OPatch version 11.1.0.8.3 or higher (do NOT use OPatch 12.x)
OBIEE 11.1.1.6.0 -> No OPatch packages available as of March 12, 2012
Downloading a Newer Version of OPatch:
To find and download the appropriate version of OPatch for yor system please go to ORacle Support and find the knoledge base article below:
Note 224346.1 – Opatch – Where Can I Find the Latest Version of Opatch?
Installing OPatch:
To install OPatch once you have downloaded the appropriate version follow the steps below:
- Rename your current OPatch directory (ORACLE_HOME\OPatch)
- Copy the zip file to your ORACLE_HOME
- Unzip the zip file
- Verify that the upgrade succeeded
cd OPatch
opatch version
File System Access:
OPatch will update the local Oracle Inventory so the user account running OPatch must have acces to the location of the OUI Inventory, to verify this you can run the following command:
opatch -lsinventory
Patch Directory Location (PATCH_TOP):
If you have a centralized location where you store code / releases it is a good idea to create a directory called PATCH_TOP to store patches as they are applied to each environment (DEV, QA, STAGE, PRD).
On this post we will discuss all the steps necessary to ensure you have the correct version of the OPatch patching utility for oracle software running on your system.