Hello Everyone!

I hope people in USA are eagerly waiting for “Thanksgiving Holidays” and door buster deals. I wish everyone in advance a Happy Holiday season.

Door Buster… Nice one to catch, in our Oracle Hyperion technology we do have one door buster step to fix it quickly. Let’s talk about the seasonal issue in this blog.

Oracle allows us using UDA and configure integration between source system and FDMEE, refer to link. Oracle is also considering this task as Custom setup in FDMEE that get reset in case of “reconfigure” as per Oracle Doc ID. 2229338.1.

Ideally we can leverage the UDA for most of the source systems, which allow JDBC Connections. As per Oracle documentation, FDMEE supports data extraction from the following data sources:

• Oracle Data Source – UDA_ORCL
  
• MSSQL Server Data Source – UDA_MSSQL
  
• MySQL Data Source – UDA_MYSQL
  
• Teradata Data Source – UDA_TD
  
• DB2 Data Source – UDA_UDB
  
• DB2 400 Data Source – UDA_DB2/400
  
• SAP Hana – UDA_HANA
  

In this blog, we take the example as connecting to PeopleSoft using UDA_ORCL.

Configuring ODI Connection with PS

Step 1: Open Oracle Data Integrator

Step 2: Click on Connect To Repository

Step 3: Select respective instance.

Step 4: Click on View > ODI Topology Navigator

Step 5: Expand Technologies

Step 6: Expand Oracle – We will find default Technologies, but no UDA related components.

Step 7: Right Click on Oracle > New Data Server

Enter Name: UDA_ORCL

Connection User: [Source System User ID]

Connection Password: [Source System Password]
Ref. JDBC Connections for Connection User to other environments, Password can be collected from PeopleSoft Team

Step 8: Click on JDBC

Select JDBC Driver as “oracle.jdbc.OracleDriver”
JDBC Url: jdbc:oracle:thin:@<host>:<port>:<sid>

Step 9: Click on Test Connection

Step 10: Select OracleDIAgent > Click Test > Save

>

Step 11: Right Click on UDA_ORCL > New Physical Schema

Step 12: Set Schema (Schema) and Schema (Work Schema) as “SYSADM”

Set Schema (Schema) and Schema (Work Schema) as SYSADM

Step 13: Set Logical Schema for Context > Save

Set Logical Schema for Global Context

Configuring FDMEE for PS

Step 1: Login to Hyperion Workspace

Step 2: Navigate > Administer > Data Management

Step 3: Click on Setup Tab

Step 4: Click on Source Adapter

Step 5: Delete Existing PeopleSoft Source Adapter (Take Snapshot prior to deleting the Adapter)
Step 6: Create New Source Adapter by clicking on

Step 7: Enter New Adapter Details (Enter Details from the Snapshot)
Note: ODI Package Name will appear ORCL Balances and ODI Project Code: AIF_ORACLE these cannot be modified

Step 8: Click on Import Table Definition

Step 9: Validate Source Column information in the Adapter

Step 10: Click on Generate Template Package

Note: ODI Package Name for the Adapter will change to Name Entered in Step 7.

Step 11: Click on Location in left panel

Step 12: Temporarily change Import Format for the number locations Source System (PeopleSoft) is connected

Step 13: Click on Import Format in left panel

Step 14: Delete the Import Format(s) assigned to Source System (PeopleSoft) – Take a Snapshot before deleting, as we need this information in next step

Step 15: Click on Add and Enter the information from Snapshot > Save

Step 16: Update Mappings for newly created Import Format

Step 17: Regenerate ODI Scenario for newly created Import Format

Step 18: Change the Location Import Format to newly created one.

Benefits from the above steps:

1. No loss of Mapping or Historical Data Loads
   
2. No loss to Data Load Rule(s)
   
3. No loss to Audit Trial
   
4. No Impact to Security