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:
- No loss of Mapping or Historical Data Loads
- No loss to Data Load Rule(s)
- No loss to Audit Trial
- No Impact to Security