1. Administering Data Integration for Oracle Enterprise Performance Management Cloud
  2. Integrating Data
  3. Integrating Oracle ERP Cloud Data
  4. Registering a BI Publisher Report as an Oracle Enterprise Scheduler (ESS) Job
  5. Defining the Data Extract Process

Defining the Data Extract Process

The source for the Oracle Business Intelligence Publisher report is a data model, which defines the source query and the parameter used in the query. Before an extract may be defined, the source extract SQL must be defined.

The following example shows a sample query that extracts the net activity for a given period and ledger from the GL_BALANCES table. Any table in the source may be specified, but for clarity and ease of use, an example for GL balances is provided here.

To define the data extract process:

  1. Use a SQL tool to build the query.

    In the example, a SQL tool is used to build the query to make it easy to debug the results before using it in BI Publisher. If a tool to perform this step is unavailable, you can just enter the query in BI Publisher. This is the base query, and additional bind variables are included so that period and ledger name may be passed from the Oracle Enterprise Performance Management Cloud as part of the extract process.

    Image shows SQL query and results

  2. Add the bind parameter values to pass along with the query when it is executed,

    The integration to Oracle ERP Cloud will fail unless the selected extract on the Oracle ERP Cloud side has one or more bind parameters passed from the EPM Cloud.

    Here two bind parameters have been added to the query:

    Image shows bind parameters.

    Here is a sample of the output from the query for the ledger named "Vision Services (USA)" and period "Jul-20":

    Image shows results of SQL Query.