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

« Write-Back of Budget Data from PBCS to Fusion-GL through FDMEE | Main | Oracle Supply Chain Planning Cloud: Product Evaluation and Co-Existence Implementation Scenarios »

Automating file based data loads for Hyperion Cloud applications

Automation is the need of the hour! Time has come when everyone wants minimal human intervention in any process or task especially when it is a periodic activity (daily, weekly, fortnightly, monthly etc) and data analytics is no different. Clients look for maximum automation especially in the data preparation processes so that they spend more time on planning and strategizing.

Data management is part of every Hyperion cloud application viz. PBCS, EPBCS, FCCS, PCMCS, ARCS, TRCS. In the simplest terms, the role of Data Management (referred to as DM henceforth) is to bring together data from various ERP source systems, transform it and load it to the cloud applications. It is possible to extract data from any on-premise or cloud source system either through direct integration or through flat files. Oracle provides direct integration of Hyperion cloud applications with Oracle Financials Cloud, Budgetary Control, HCM cloud, NetSuite.

However, to extract data from other source systems or third party applications, a flat file or comma delimited file is needed which is used as a source for DM. This makes the file based integrations a manual process where the source system has to extract and provide data in a file and then this file will be imported into DM. However, this manual step can be eliminated by using some scripting techniques and APIs. You can automate any data load process as long as you are able to access and extract data from the source system using any scripting or programming language.

Below is an example of one such kind of automation where the source system is a third party on-premise software having Oracle DB as the backend database and target application is FCCS. The solution is implemented in three basic steps:

1.       Call a Jython script to extract data from third party source system into a file.

2.       Upload the file onto the DM inbox folder

3.       Execute the data load rule to load data to FCCS.

All this is bundled into a batch script which is executed from the command prompt or can be scheduled to execute on the desired date and time.

1.       Jython script to extract data from source system :

I have implemented this piece of code using Jython. It can be implemented using any other scripting or programming language as well. 

This is psedo code and is to be used as a guideline only

import java.sql as sql

import sys

# Period and Year passed as arguments to the script

VARPERIOD = sys.argv[1]

sys.stdout.write(VARPERIOD)

VARYEAR = sys.argv[2]

sys.stdout.write(VARYEAR)

# Connect to source system database

sourceConn = sql.DriverManager.getConnection("<<jdbc url, username, password>>")

selectStmt = "SELECT ACCOUNT, ENTITY, ICP, DEPT, AMOUNT FROM <<source table>> WHERE PERIOD = '" + VARPERIOD + "' and YEAR =" + VARYEAR

stmt = sourceConn.createStatement()

stmtRS = stmt.executeQuery(selectStmt)

myCount = 0

outfilename = "OUTPUTFILE.TXT"

outfile = open(outfilename, "w")

outfile.write("ACCOUNT;ENTITY;ICP;DEPT;AMOUNT" + chr(10))

while(stmtRS.next()):

  myCount = myCount + 1

  ACCOUNT = stmtRS.getString("ACCOUNT")

  ENTITY = stmtRS.getString("ENTITY")

  ICP = stmtRS.getString("ICP")

  DEPT = stmtRS.getString("DEPT")

  AMOUNT = stmtRS.getBigDecimal("AMOUNT")

  mystr = str(ACCOUNT) + ";" + str(ENTITY) + ";" + str(ICP) + ";" + str(DEPT) + ";" + str(AMOUNT) + chr(10)

  outfile.write(mystr)

outfile.close()

stmtRS.close()

stmt.close()

 

2.       Upload the file OUTPUTFILE.TXT onto the DM inbox folder and execute the data load rule:

These steps are achieved by EPMAutomate script which is similar to a batch script

@echo off

SET /p period=Enter Period in MMM format:

If %period% == "" goto :ERROR_P

SET /p year=Enter Year in YYYY format:

If %year% == "" goto :ERROR_P

echo Executing script to extract data from source system

cd /d c:\jythonscripts

jython -J-cp ojdbc6.jar SOURCE_EXTRACT.py %period% %year%

IF %ERRORLEVEL% NEQ 0 goto :ERROR

echo Source extract complete

echo Logon to EPM Cloud

call epmautomate login <<username>> <<password>> <<url>> <<domain>>

IF %ERRORLEVEL% NEQ 0 goto :ERROR

echo Upload file to inbox folder

call epmautomate uploadfile c:\jythonscripts\OUTPUTFILE.TXT inbox/Source_Files

IF %ERRORLEVEL% NEQ 0 goto :ERROR

echo Executing load rule

call epmautomate rundatarule DLR_LOAD_FCCS %period%-%year% %period%-%year% REPLACE MERGE Source_Files/OUTPUTFILE.TXT

IF %ERRORLEVEL% NEQ 0 goto :ERROR

:EOF

echo Scheduled Task Completed successfully

call epmautomate logout

pause

exit

:ERROR

echo Failed with error %ERRORLEVEL%

call epmautomate logout

pause

exit

:ERROR_P

echo Period and/or Year cannot be blank!

pause

exit

 

Points to note:

1.       This automation process is for EPM cloud applications where on-premise FDMEE is not available and Data Management has to be used.

2.       This is not the only way to achieve automation. It is the way I have implemented according to my project requirements. There are other ways of automating as well using a combination of APIs of the source system (if available), EPM REST API and batch scripts.

3.       This approach requires EPMAutomate, Jython and the batch files to be hosted on a local workstation or server which will be on-premise even though target Hyperion applications are on cloud.

4.       Further to this, email notifications can also be configured to send out emails to stakeholders after a particular data load completes successfully or errors out. This can be achieved using email programs like BLAT, POWERSHELL, MAPISEND, MAILSEND etc. These programs are available for free download and can be configured in a windows batch and called after the data load step is complete.

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