FDMEE connections – ODI trickery for Oracle databases

I recently wrapped up an infrastructure project at a client that needed to prove the connection between Peoplesoft and FDMEE would work. The client will then use this connection going forward as he builds out his brand new implementation.

We kept getting stuck with the FDMEE connection “initialize” step. The DBA granted us read-only access to the Peoplesoft tables through a separate schema than the owner of the tables. For security reasons, they didn’t want us logging directly into the Oracle database as the owner of the Peoplesoft tables and for that I cannot blame them. I setup the connection in ODI as I have a few times before and expected that everything would be just fine. Unfortunately, we kept getting an Oracle error in ODI: ORA-00942: table or view does not exist.

I connected to the schema using SQL Developer and when I tried to view the tables, I could see that no tables existed under my service account schema. I was able to query the Peoplesoft tables through synonyms, though, so we assumed that everything would work fine.

As we looked at the errors in the ODI logs, we could see that it was attempting to access the tables using a “Schema.Table” naming convention. The schema that it was using was our service account schema “SVC_HYPERIONSQL”. Since that schema didn’t contain any tables, it was clear that was the cause behind the errors, but how to get the integration to point to the correct schema where the tables exist?

I went back into ODI and looked at the Data Server connection. That’s when I realized that the “Schema” that it was asking for needed to be the owner of the Peoplesoft tables. We could use our other service account schema as the Work Schema, but in order for the integrations to run properly, they needed to access the tables using the correct account, which was SYSADM for this environment.

Must select the proper schema that owns the ERP tables.

Must select the proper schema that owns the ERP tables when using Oracle repositories.

After selecting the proper schema, the “Initialize” step in FDMEE worked without a hitch.

Oracle Data Integrator available

ODI is now also available for download.  New installs of ODI support Java 1.7 for server components, which I thought was interesting.

New features in this release:

ODI WebSphere Support (Hot-Pluggability)
This ODI release introduces the support of IBM WebSphere Application Server (WAS). ODI Agents and datasources can now be deployed on Oracle WebLogic Server (WLS) and IBM WebSphere Application Server (WAS).

XML Driver Enhancements
Full support for XML Schema constructs by using standard Oracle parser:

  • New JDBC driver parameter compat_mode to indicate the compatibility with mapping modes such as the XDK XSD parser.
  • New JDBC driver command WRITEMAPPINGFILE to help understand the relational structure that has been created for the XSD/DTD file. The mapping file contains the element/attribute name to table/table.column name mapping for each element/attribute.
  • The JDBC command CREATE SCHEMA includes now the compat_mode parameter.

SSL Support for Standalone Agent
The Standalone Agent now supports the SSL protocol for secure connectivity. New parameters added to specify the Standalone Agent server side transport protocol and to configure the SSL truststore and keystore:

  • New environment variables in agent.sh/agent.bat scripts
  • New configuration variables in odiparams.sh/odiparams.bat scripts
  • Java system properties in odiparam.sh/odiparams.bat scripts

Include EDQ Open Tool in ODI Core
The EDQ Open Tool introduced in ODI 11gR1 PS2 ( to invoke an Oracle Enterprise Data Quality (Datanomic) Job is now available as a standard ODI tool. The OdiEnterpriseDataQuality tool includes a new, optional parameter to connect MBean domains.


From an EPM perspective, we just scratch the surface of what ODI can do.  Most of my clients are operating under the limited use license that comes with Planning and/or HFM.   ODI is a great tool that allows some great automation to be built for EPM administrators, though.  Most of my experience with ODI has been replacing old HAL routines during Planning application upgrades, so these features don’t really change the game very much for me.

If you want it, here it is; come and get it:  Oracle Data Integrator Downloads.