Leveraging the Oracle Integration Agent

Posted by: PARC’s Oracle EPM Team

Ever since Oracle’s EPM (formerly Hyperion) products have existed, an important consideration has been integrating data and metadata from (and back to) these external data sources. For many years organizations either used Oracle-based data sources or relied upon flat files exports and imports to accomplish this, even as the EPM products entered the cloud realm.

Oracle’s EPM Integration Agent, released in Q4 2019, adds an excellent new tool to the EPM Cloud products’ capabilities, and enable the use of virtually any data source. In this post we will explore the “old state” of data and metadata integration and introduce the technologies and capabilities of this new tool.

The Old Method

Prior to the advent of Integration Agent, data and metadata import relied upon the use of Data Management for Oracle Enterprise Performance Management Cloud (DM), either through a connection to a small number of Oracle Cloud data sources, or by using flat file extracts from the data source, which has been more common in PARC’s experience. The typical flat file-based approach looks something like this:

1. 1. A file is extracted from a source system, such as SAP, into a location that a server-based scheduling tool can access.
   2. A scheduled task runs on a server and runs one or more EPMAUTOMATE commands that call DM routines which load the flat files’ data or metadata.
   3. As applicable, execute calculations and other tasks.
      These can be run on an automated basis or manually by an admin or authorized user.

The above processes are often complicated by many issues:

• Flat file extraction processes must either be scheduled tasks than run regularly or are executed locally for on-demand functionality (such as during month-end close periods).
• Data can easily be loaded only one period at a time, and (when performed manually) can require synchronization of system substitution variables with those of the DM Point of View.
• There is no inbuilt ability to write data (such as budget data) back to the source systems for reporting and analysis other than sending data back to the source systems using flat files.

Introducing the Oracle EPM Integration Agent

Introduced in the EPM Cloud 19.10 (October 2019) release, Integration Agent adds significant new functionality to your ETL arsenal. It has the following capabilities and benefits:

• The ability to extract, transform and load data and metadata from nearly any on-premise or cloud-based source, and to load to supported Oracle EPM Cloud products, such as Planning, Account Reconciliation, and Financial Consolidation and Close:
  • Using Direct access, to on-premises data sources using SQL queries.
  • Using Scripting, to connect to sources not accessible via SQL.
  • For other cloud integrations, REST or other web services can be leveraged using Jython scripting.

Technologies

Depending on your needs and the complexity of your integrations, both familiar tools (EPM Automate) and newer ones such as Jython can be used.

The EPM Integration Agent is a lightweight tool that does not require major hardware resources. It is also very easy to implement and does not require any major security or firewall changes. If the on-premises server where the agent runs has internet access to the *.oraclecloud.com domain, it will work. All data exchange and communication between the on-premises agent and the Oracle cloud is encrypted.

Key Benefits

• Direct data exchange eliminates the need for file operations: The Integration Agent can execute queries against your on-premises DB sources. The result of these queries is then automatically uploaded to DM for transformation and load into the target EPM application.
• Built-in “before” and “after” extract/load scripting capability allows flexibility and enhanced functionality: The Integration Agent provides the flexibility to perform certain actions at different stages of the load process using Jython scripting. This opens a world of possibilities since actions not supported directly inside DM can be performed, such as, truncating an on-premises table before pushing data to it, running a stored procedure before running the data extract query, and modifying the query dynamically.
• Drill-thru to data from non-Oracle sources: Drill-thru can be enabled which will allows the user to see all the records that comprise the data value in the EPM application. Drill-back to source can also be enabled depending on the source system; this will require a bit of additional configuration.
• Write-back capability to any source: The integration agent is capable of writing data from the cloud back to an on-prem database. If the target isn’t on-premises or isn’t a database, the data exported out of DM can be loaded to any other source via a web service using Jython scripting.
• Ability to add filter conditions on database sources: The integration agent allows you to customize the data extract query. This provides the flexibility to extract the data as needed.
• Ability to add metadata from source systems: Data kickouts can be a thing of the past with this setup. Metadata from your source systems can be loaded before the data loads. This ensures that if there’s a new account or any new dimensional member in the source, it gets loaded to the EPM application first so that all data loads are successful.

Summary

The Oracle EPM Integration Agent can provide a superior ETL experience as compared to a flat file approach and is rich with capabilities that can bring you next-level integrations. PARC Consulting can help you leverage this new tool to simplify and enhance your EPM implementation.

In our next blog post we will provide a case study of a PARC implementation using Integration Agent.