Infosys’ blog on industry solutions, trends, business process transformation and global implementation in Oracle.

« Synopsis of EPM Cloud Updates - April 2020 | Main | Oracle Data Lake »

Hyperion Data Integration automation in a hybrid environment

This article explains the data load automation solution in a hybrid Hyperion environment. Firstly, let us understand what is actually meant by a hybrid integration environment. In the Hyperion world, data integration i.e. import, transform and load of data from the source system to the target system is achieved by either on premise FDMEE or cloud Data Management.

A module called Data Management is available along with the cloud Hyperion applications like EPBCS, FCCS, PCMCS etc to meet the data integration requirements for the cloud applications. Using Data Management, only cloud ERP applications like Oracle Financials Cloud can be directly connected using the inbuilt adapter.

The on-premise tool for data integration is FDMEE. Using FDMEE, on premise ERP applications like Oracle EBS, Peoplesoft, SAP etc can be directly connected and the target Hyperion applications are also on premise. 

A hybrid environment is where the source ERP applications are on premise applications, the target systems are cloud based applications and FDMEE is the integration tool. In this case, the data loads will be configured per the standard process but if end to end automation is required where some rules need to be triggered in the target application post data export, this cannot be achieved directly in FDMEE as cloud rules/scripts cannot be directly called from FDMEE.

As an example of this scenario, consider the source system as on premise Oracle EBS GL, target application as cloud EPBCS and FDMEE as integration tool. Such a scenario is possible only when the client organization is still using on premise ERP applications, a mix of on premise and cloud Hyperion applications and also has license for FDMEE from the past.

In such cases automation will be achieved in 3 steps -

1.       Create the regular data load rule in FDMEE to import, validate, export data from on premise EBS GL to cloud EPBCS application.
2.       Write an event script in FDMEE - AftExport - in which you call a windows batch and pass the scenario and period as arguments from FDMEE.
3.       In the windows batch, use EPM Automate commands to trigger the post load calculations or business rules of EPBCS application.

A prototype of the AftExport event script is explained below:

# Import required libraries

if fdmContext["LOCNAME"] == (" << EPBCS location name >> "):

# If period name is in the format of Jan-20, split to get the month and year. Convert year to FY20

  period_split = fdmContext["PERIODNAME"].split('-')

  period = period_split[0]

  year = "FY" + period_split[1]

  scenario = fdmContext["CATNAME"] 

  version = "Final"

# Call the windows batch placed in the FDMEE inbox directory and pass the year, version and scenario as arguments

  os.chdir(fdmContext["INBOXDIR"] + "/Batch_Files")

  command = fdmContext["INBOXDIR"] + "/Batch_Files/AftLoadBatch.bat " + year + " " + version + " " + scenario

  p = subprocess.Popen(command, shell=False)

  retcode = p.wait()

 

Similarly, you can also have BefLoad script to call a windows batch to execute any business rules before loading the data like clearing data for the period before load.

Below is the prototype for the windows batch:

REM Login command

CALL C:\Oracle\EPM_Automate\bin\epmautomate login <<username>> <<password>> https://planning-test-XXX.pbcs.us6.oraclecloud.com XXX >&1

REM Run business rule for Currency Translation

CALL C:\Oracle\EPM_Automate\bin\epmautomate runBusinessRule USD_Translate Scenario=%3 Version=%2 Years=%1 >&1

REM Run business rule for Aggregation

CALL C:\Oracle\EPM_Automate\bin\epmautomate runBusinessRule AggregateData Scenario=%3 Version=%2 Years=%1 >&1

 

Pre-requisites for this automation:

  • 1.       EPM Automate should be installed on the FDMEE server preferably at the path C:\Oracle\EPM_Automate. Ensure that the full path has no spaces in between else the EPM command throws an error in the windows batch.
  • 2.       If using a password encryption file for the EPM Automate login command (which is the recommended best practice), ensure that it is placed at the path C:\Oracle\EPM_Automate\bin.
  • 3.       Create batches in FDMEE and schedule them. The FDMEE batch will be triggered at the scheduled date and time which will trigger the associated data load rule. As soon as the data export is completed, the AftLoad script will be triggered to call the windows batch to execute the EPBCS business rules, thus achieving complete end to end automation.

Post a comment

(If you haven't left a comment here before, you may need to be approved by the site owner before your comment will appear. Until then, it won't appear on the entry. Thanks for waiting.)

Please key in the two words you see in the box to validate your identity as an authentic user and reduce spam.

Subscribe to this blog's feed

Follow us on

Blogger Profiles