Integrate Oracle Database with EPBCS using Rest API

EPBCS uses a Lighter version of FDMEE Data Management to load the external data into application. Most of the EPBCS Implementation use File method to load the data, Hyperion Admin need to request the data in a delimited format file with fixed columns. Sometimes the turn around time for getting the file may be longer than expected or the Admin may have to hold the execution of the EPBCS business rules for these source files. In this blog I am trying to use REST API + Groovy language to generate the data file and load the data into EPBCS Application. Using this method there are 2 benefits:

• No more waiting time to get the source data file.
• Changing the format of the file based on EPBCS import format  in Data Management is quicker.

Blog is written based on a scenario where GL Balances data is stored in a source system which runs on Oracle Database. GL Actual data needs to be derived from Source system in the format which is aligned with Import format defined in FDMEE and it has to be loaded into EPBCS using FDMEE data load rules.

Groovy

Groovy is a object oriented programming which helps in using the REST API Provided by Oracle for EPBCS. We also have EPM Automate which is a standalone batch commands that can be used to do many EPBCS tasks. But in this case we need to interact with External data source, so we have to depend on the scripting programs which support EPBCS REST API. Below are few high level steps:

1. Create a readonly user for Source Oracle database.
2. Write a Groovy Script which includes
   • Retrieves data from Source Database.
   • Generate Source File.
   • Uploads the file to Data management Folder
   • Run the data rule to load the data.

Readers can go thru some of my earlier blogs which gives detailed information about Groovy and its usage in invoking REST API.

REST API – Part 1

Guide to Oracle Cloud: 5 Steps to Ensure a Successful Move to the Cloud

Explore key considerations, integrating the cloud with legacy applications and challenges of current cloud implementations.

Get the Guide

REST API – Part 2

Most of the code used in my earlier blogs are reused in this blog. Lets get into the blog, Groovy script can be divided into 4 sections.

Section 1: Import Libraries

• Import all the required libraries.
• java.sql.* and groovy.sql.sql are used to connect to the oracle database.
• java.io.* is used to create a file and write the database content to the file.
• org.json.JSONObject and groovy.json.JsonSlurper are used to invoke the EPBCS REST API.

Section 2: Generate the data file

• create a data connection for the oracle server using Sql.newInstance. Groovy supports multiple databases like MySQL, Oracle, SQLServer and HSQLDB.
• CRUD operations over source database are possible using java.sql libraries.
• Make sure we use the read only db user for this groovy script.
• create a file using File java class. File1 shown below is the file object which will be used to write the data retreived from Oracle.
• Retreive the data and redirect the output to File1.

Section 3: Upload the file

• REST API Upload is used to load the file but the API has to be called multiple times.
• Data file to be loaded has to be divided into chunks and data file has to be loaded based on number of chunks.
• Chunk size should not be greater than 50MB.
• Upload API is used to upload snapshot and files to the data management folders.
• Oracle has provided some sample scripts which can be used in developing the uploadFile method.
• extDirPath is the parameter used specifically for files uploading to data management folders.

Section 4: Invoke Data Rule

• Invoking data rule is similar to invoking a business rule.
• Start Period and End Period are mandatory parameters which gives the Period range.
• Import Mode – Mandatory parameter to specify the mode to be used to send the data in the data management
  • APPEND to add to the existing POV data in Data Management.
  • REPLACE to delete the POV data and replace it with the data from the file.
  • RECALCULATE to skip importing the data, but re-process the data with updated Mappings and Logic Accounts.
  • NONE to skip data import into data management staging table.
• Export Mode – Mandatory parameter to specify the mode to be used to send the data from the data management to EPBCS.
  • STORE_DATA to merge the data in the Data Management staging table with the existing Planning data.
  • ADD_DATA to add the data in the Data Management staging table to Planning.
  • SUBTRACT_DATA to subtract the data in the Data Management staging table from existing Planning data.
  • REPLACE_DATA to clear the POV data and replace it with data in the Data Management staging table. The data is cleared for Scenario, Version, Year, Period, and Entity.
  • NONE to skip data export from Data Management to Planning
• Invoking business rule can be referred from my earlier blogs listed above.

Readers need to have a good knowledge on Groovy + EPBCS REST API documentation provided by oracle to apply the method explained in this blog.