We recently streamlined a client’s process with lights-out automation via FDM Batch Loader. The process begins with an extract of Actuals from HFM in one Hyperion environment, which is then dropped into the OpenBatch folder in a second Hyperion environment to be automatically loaded to Hyperion Planning.
A part of the requirements was to use the Essbase Adapter to perform a preprocessor custom CLEARBLOCK calc script, and a postprocessor custom AGG calc script. (Note FDM has built-in Replace logic which will clear the data by entity before the load, except it does not clear entities that are missing in the latest import file.)
The postprocessor custom AGG Calc Script was able to be automated using the technique described in a blog post by Ajay Yadav back in 2009. In this technique, Validation Entities have been set up that reference which calc scripts to run as a part of the Export step in FDM.
To perform a CLEARBLOCK calc script, on the other hand, required a different technique, since it needs to run as a preprocessor; occurring prior to the Export step in FDM. We set up an FDM event script to handle this requirement, which can be created by going through the following steps in the FDM Web:
- Ensure you have sufficient backups, including for the Essbase databases, before implementing
- Login to FDM Web at http://server:19000/HyperionFDM
- Navigate to Tools > Script Editor > Events > BefLoad
- Paste the following script and edit the items highlighted in red font as needed:
Sub BefLoad(strLoc, strCat, strPer, strTCat, strTPer, strFile)
‘Oracle Hyperion FDM EVENT Script:
‘Purpose: Run calc script to clear current month Actuals data in Essbase before load;
‘ more comprehensive than the built-in Replace functionality since it will
‘ ensure the clearing of any entities that are not in the latest load file.
‘The BefLoad Event may run multiple times. Following lines will abort subroutine if this is not the first instance of the BefLoad Event (as assessed by the filename being processed)
Select Case LCase(Right(strFile,6))
Case “-a.dat”, “-b.dat”, “-c.dat” Exit Sub
‘If/Then Line limits script to only run on pertinent FDM Locations; change location names or remove line as needed
If strLoc = “IS-Load” Or strLoc = “BC-Load” Then
Set BlOCKPROC = CreateObject(“upsWBlockProcessorDM.clsBlockProcessor”)
BlOCKPROC.Initialize API, SCRIPTENG
‘Establish connection to Essbase
Set API.IntBlockMgr.IntegrationMgr.PobjIntegrate = BlOCKPROC.ActConnect(“GetTargetInfo”)
‘Run ClrAct Calc Script; change script name as needed
Set objHWReturn = API.IntBlockMgr.IntegrationMgr.PobjIntegrate.varCon.DataManipulation.fCalculate(1,”ClrAct“)
Set API.IntBlockMgr.IntegrationMgr.PobjIntegrate = Nothing
Set BlOCKPROC = Nothing
Set objHWRerurn = Nothing
When you initiate the “Export” stage in FDM, by clicking the “fish”, the script will run. You should see activity in the Essbase Logs and Essbase Admin Console to confirm. On a side note, it is also possible to execute Windows “BAT” Batch scripts and MaxL if desired, although that is a topic for another post.
Note: We found reference online to the error “Object required: ‘API.IntBlockMgr.IntegrationMgr.PobjIntegrate'”. We set up the above script to address this error by running the ActConnect subroutine which is needed to create the PobjIntegrate Object, according to the FDM API documentation.