When integrating an on-premise system like an E-Business Suite application with an Oracle Enterprise Performance Management Cloud application, SQL queries can be used as means to extract the data from the source database. You can extract the data and then load the data directly to the EPM Cloud applications using the EPM Integration Agent. The EPM Integration Agent executes a query against an on-premise relational database and then loads the data to the EPM Cloud application.
For an on-premise EBS system, a SQL query connecting the various database tables can be written to output data in a format ready to be loaded into the EPM applications. For this purpose, a corresponding ‘Target application’ must be created in Oracle Data Management to host the data from the SQL query, and load the data to the desired target EPM application.
The Target Application in Data Management for an on-premise EBS system can be created by choosing the source system as ‘EBS GL Balance’. But when using a SQL Query to extract data from the source, using the Query output as the ‘Data Source’ to build the Target Application yields the best results. This way, the Target Application dimensions are created exactly as the corresponding columns exist in the SQL Query and there is no scope for a mismatch between the Query and the columns in Data Management.
Using this SQL based integration, the system executes the required Query on the source data in the on-premise relational database, offloads processing, extracts and transforms the data at the source level, and then loads the data directly to EPM Cloud. This way, the EPM Cloud database is bypassed for staging and processing, eliminating any performance bottlenecks and improving the performance and scalability of the load process.
In this article, the detailed steps required to integrate SQL based EBS data with an EPM Application are discussed.
This article assumes that your EPM Application cubes and dimensions are already configured and set up, you have a working SQL query that extracts all the dimensions and corresponding data from the Oracle database, an EPM Integrations Agent is installed, and that you have some familiarity with Oracle Data Management integration tool.
Below is a step-by-step process to integrate an on-premise EBS system with an EPM Cloud application.
Create Queries in EPM Data Exchange
The first step for this integration is to create a Query for the data pull from Oracle database. Navigate to Data Exchange in the Application tab in the EPM Cloud application.
Click on Actions and select Query.
Add a new Query.
Provide a Query Name and add the SQL Query used to pull the required data in the Query String. Save the Query. The query name is also used on the Application Filter page in Data Management to identify the data extract query when registering the SQL data source in the target application.
Query is saved.
Export the Query output into a CSV file
Open a Database Management toolset like a SQL developer and run the Query that was saved in Data Exchange.
Right click on the Query Result and click Export.
Select the format to be CSV.
Select Left and Right Enclosure to be none.
Click on Browse and choose a destination for the Export.
Click Next to see the Export Summary.
Explore key considerations, integrating the cloud with legacy applications and challenges of current cloud implementations.
Query output is saved.
Create an EBS Target Application with Query output
Open Oracle Data Management in the Application tab in the EPM Cloud application.
On the Setup tab, click on Target Application.
Click Add to create a new Target Application for the EBS Query.
Select Data Source.
Choose Source System as On Premise Database.
In the Data Management directories, select a folder, browse and upload the CSV extract of the SQL query previously created.
Click Ok. Provide an optional Prefix to the Target Application name.
Click Ok again to import the SQL Query output columns as Target Application Dimensions.
Click Save to create the Target Application.
Update Target Application Filters
In the Target Application Details section, click on Application Filters tab.
Enter the following for the filters:
- Data Extract Query: Name of the Query created in Data Exchange
- Delimiter: ‘,’ (Comma)
- Credential Store: Cloud
- JDBC Driver: Oracle
- JDBC URL: Oracle EBS Database JDBC connection
- Username: Database user with access to tables and schemas used in the Query
- Password: Password for the Database User
Save the updates to the Target Application.
Finish the Integration Setup and Import data
Create an ‘Import format’ using the Target Application created for the EBS Query as the Source, and the EPM Application as the Target.
Create the ‘Location’ for the Import format.
Create Global and Application ‘Period Mapping’ for the Target EPM Application.
Create ‘Data Load Rules’ and ‘Data Load Mappings’ for the ‘Location’.
Run the Data Load Rule to extract data from EBS and Import into the required EPM Application.
Review the ‘Data Load Workbench’ to validate that the SQL Query output matches the data in the Workbench.
You are all set to import data from EBS into the EPM application!