I was new to ERPi and had some trouble configuring it initially to work with EBS. The ERPi admin guide is very detailed but there were some documentation defects in it which made the implementation a challenge.
The implementation steps below show how to load data (Metadata and Data) from EBS. I have not configured the Write Back ability which would allow the users to Write-back their Budget data into their GL.
Drill throughs using ERPi can only be performed from a Level 0 intersection on your spreadsheet or data-forms.
I will try to provide an overview of the configuration steps required to setup ODI followed by the ERPi configuration in Part 2. This is assuming that you have ODI and ERPi installed on the server.
Pre-Requisites
You would need the following schemas or their information -
ODI schema - HYPODIM
ERPi Schema in the DB repository (HYPERPI)
EBS Schema in the EBS DB repository (HYPEBS1)
You would also need to have a DB user with DBA privileges.
Additionally, if you are configuring ERPi to work with HFM,
then there are some patches that need to be applied and also the HFM Driver dll
file that needs to be updated if you are working on a 64-bit machine.
Follow the steps below to integrate ODI with HFM (64-bit)
!! If running version 11.1.2.2, before continuing, check to
see if the following script has been run, otherwise run this script:
Download from Oracle: Patch 13967787: ERPI PATCHSET UPDATE
11.1.2.2.300
Download and unzip to
C:\Oracle\Middleware\EPMSystem11R1\OPatch\
From SQL Developer, run the following file on the ERPI
database:
C:\Oracle\Middleware\EPMSystem11R1\OPatch\13967787\files\products\FinancialDataQuality\database\Common\UpgradeTRPS2toTRPS2X.sql
Also, follow the steps below to update the HFM driver to a
64-bit compatible version
1.
Rename odiparams.bat located at <ODI_HOME>\oracledi\agent\bin
to
odiparams.bat.backup.
The default installation location is C:\app\Oracle\product\11.1.1\Oracle_ODI_1.
2.
Download Oracle Data Integrator Companion CD
11.1.1.6.2.
3.
Unzip the contents of the Companion CD to the
temp directory.
4.
Unzip oracledi-agent-standalone.zip.
5.
Copy the contents to: <ODI_HOME>.
6.
Make a backup of the HFMDriver.DLL.
7.
For the 32 bit operating system, copy HFMDriver32_11.1.2.2.dll
as HFMDriver.DLL.
8.
For the 64bit operating system, copy HFMDriver64_11.1.2.2.dll
as HFMDriver.DLL.
9.
Rename odiparams.bat.backup from Step 1 to odiparams.
Master Repository
The first step involves in importing the master repository provided by Oracle in the Hyperion Package.
This set of files and folders can be found in the odi directory found at the location below
D:\Oracle\Middleware\EPMSystem11R1\products\FinancialDataQuality\odi
Open ODI Studio
Click on File>New
Click on Master Repository Import Wizard and click on OK.
Enter the Database Connection details. Click on Test Connection to verify the details entered.
Note: Under Repository Configuration, change the Id value to ‘500’, if it's not already 500. This is important for the configuration to be successful.
In the Export Path, click on the Search Icon to navigate to D:\Oracle\Middleware\EPMSystem11R1\products\FinancialDataQuality\odi\11.1.2.2.00\masterrep and click on Ok.
Click on Ok and ODI will now import the master repository.
Connecting to the Master Repository
Connecting to the Master Repository
From the ODI Studio click on
Connect to Repository
Enter the following details under the Oracle Data Integrator
Connection section
Login Name : REPOSITORY
User : SUPERVISOR
Password : SUNOPSIS
Enter the Master Repository connection details as entered in
the previous section.
Test the connection after all details have been entered to
validate the credentials
Click on OK on the connection details screen and then Login
to the Master Repository (REPOSITORY)
Create a Work Repository in ODI
Click on View>ODI Topology Navigator
In the ODI Topology Navigator section, scroll down to
Repositories section
Right click on Work Repositories and click on New Work
Repository
On the next screen, in the user field, enter the same schema
name as the one used for the master repository user (In my example – HYPODIM)
Enter the JDBC Driver
and JDBC Url - jdbc:oracle:thin:@<host>:<port>:<sid>
(Replace host, port and SID with the correct information. Fully qualify the
server name
Click on Next
Note : ID should be changed to ‘501’
Enter a name for the Work Repository ( Ex: WORKREP)
Enter a password (SUNOPSIS)
Click Finish
ODI now starts creating the work repository
After the WORK repository creation is complete, ODI prompts
for creating a login name for the WORK repository
Click on Yes and enter the name on the next screen
Click on OK
Import the Work Repository
Disconnect the REPOSITORY connection. Go to
ODI>Disconnect “REPOSITORY”
Make sure the Work Repository radio button is selected and
the Work Repository ‘WORKREP’ is entered in the Work Repository field.
Click on OK on the screen above
Click on OK on the Login screen and this re-establishes the
connection with the WORK repository information.
Click on View>ODI Designer Navigator
In the Designer, click on the icon on the right of the
Designer Navigator task bar
Click on Import
Select Import the Work Repository and click on OK
Select the Import Mode as Synonym Mode INSERT_UPDATE
Navigate to the Work Repository folder
D:\Oracle\Middleware\EPMSystem11R1\products\FinancialDataQuality\odi\11.1.2.2.00\workrep
and click on OK.
ODI begins importing the WORK repository. During this
process ODI prompts to select Yes or No to import the files with different
Repository Ids into ‘501’. Click ‘Yes’ on all these prompts.
After the import is complete, ODI generates a report which displays
the imported projects. Click on OK.
Click on View > ODI Topology Navigator
Right click on Agents>Create New Agent ( Under Physical
Architecture )
On the New Agent screen, only enter the name ERPI_AGENT and
Save.
In Topology Navigator scroll down to the Context section.
Click on the icon in the Context task bar and click on New Context
Enter the name as ERPI (or whatever you prefer). Leave the password blank.
Update Physical Architecture
In Topology, under Physical Architecture
scroll down to Oracle and expand it.
Double click on EBS_DATA_SERVER
Enter the SID for the EBS instance
Enter the connection details:
User – This is the EBS Schema mentioned in the
Pre-Requisites section
Click on JDBC after entering the above details.
Enter the details
JDBC Driver
JDBC Url: In RAC environments the JDBC Url should be in the
format below
jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=YES)(FAILOVER=YES)(ADDRESS=(PROTOCOL=tcp)(HOST=mn4s31039-vip.nmdp.org)(PORT=1521))(ADDRESS=(PROTOCOL=tcp)(HOST="SERVER NAME")(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME="XYZ"))
Click on Test Connection to verify the connectivity
Double click on EBS_DATA_SERVER.Schema under the
EBS_DATA_SERVER and enter the HYPEBS1 schema name in both Schema and Work
Schema fields.
Click on Context under Definition and add the ERPI context
from the drop down list and map it to EBS_APPS logical schema from the drop
down list.
Double click on ERPI_DATA_SERVER in the Physical
Architecture
Enter the SID name where the HYPERPI schema has been
created. Enter the user information.
Click on JDBC under Definition and enter the JDBC Driver and
JDBC Url.
For Non- RAC environments the JDBC Url is in the following
format.
Jdbc:oracle:thin:@<host>:<port>:<SID>
After entering all the details, save it.
Under ERPI_DATA_SERVER, double click on
ERPI_DATA_SERVER.Schema
Enter HYPERPI for both Schema and Work Schema fields. Go to
Context under Definition and add the ERPI context and map it to AIF_TGT in the
drop down list under the Logical Schema column.
Update ODIPARAMS file
From the Windows file explorer, navigate to
D:\Oracle\Middleware\Oracle_ODI1\oracledi\agent\bin
Open the ODIPARAMS.BAT file in Edit mode.
Look for REM # Repository Connection Information
Enter the connection details
REM # Repository Connection
Information
REM #
set
ODI_MASTER_DRIVER=oracle.jdbc.OracleDriver
set
ODI_MASTER_URL=jdbc:oracle:thin:@mn4s34015:1554:DHYP
set ODI_MASTER_USER=HYPODIM
set
ODI_MASTER_ENCODED_PASS=bVyHjmgLGtm1Qu9O.DbJHg
The passwords have to be encoded using the utility provided
by Oracle as part of the OI package (encode.bat)
Run the encode.bat as follows from command prompt.
Encode.bat is in D:\Oracle\Middleware\Oracle_ODI1\oracledi\agent\bin
Syntax – encode <PASSWORD>
Example: encode SUNOPSIS
Result : bVyHjmgLGtm1Qu9O.DbJHg
The encoded password should be entered in the
odiparams file.
The same process is followed for the agent startup program
REM #
REM # User credentials for agent
startup program
REM #
set ODI_SUPERVISOR=SUPERVISOR
set
ODI_SUPERVISOR_ENCODED_PASS=fJyaPZR5gwgveUeh0qaE6dc
Ensure that the work repository name is WORKREP
REM #
REM # Work Repository Name
REM #
set ODI_SECU_WORK_REP=WORKREP
Create Agent START/STOP scripts
- Start Script
In D:\Oracle\Middleware\Oracle_ODI1\oracledi\agent\bin
create a "ODI_Agent_Start.bat" file. The content of this file should
be exactly as follows:
cd D:\Oracle\Middleware\Oracle_ODI1\oracledi\agent\bin
agent "-PORT=20910" "-NAME=ERPI_AGENT"
Save
this file as a batch file. This script should always be running
- Stop Script
Create a "ODI_Agent_Stop.bat" file with the
following entries
cd D:\Oracle\Middleware\Oracle_ODI1\oracledi\agent\bin
agentstop "-PORT=20910"
Save this as a batch file. This file should be run every
time the running agent needs to be stopped.
