Thoughts, ideas and solutions from a few EPM consultants.

Dynamic Calcs from FDMEE Event Scripts

In a recent project I came across a process the client wanted to keep intact that seemed easy enough in FDMEE. They had multiple calc scripts saved in text files with custom ##VARIABLES##. These needed to be parsed, replaced and executed before or after certain data loads. In the old FDM VB API (11.1.2.1) there was an option available to pass in a calc script as a string for execution.

You can setup calc scripts and pass run-time substitution variables using FDMEE currently. However, it has a limited number of options when it comes to passing variables (like from event scripts on the fly). After looking over the 11.1.2.4 FDMEE admin docs I wasn't able to find this method available. So I looked over the Essbase JAPI docs and I could see there was a method that allowed execution of dynamic calc scripts. So I decided to write a simple wrapper around the JAPI that allows for this functionality.

Download:

Jython Essbase Wrapper

Save here:

D:\FDMEE\data\scripts\custom\jython\essbase-api.py

Open the file in your favorite text editor and edit the section that is labeled #Consts to suite your environment.

The following few methods handle the reading and parsing of the calc scripts from the file system. Save the following script to the same folder you saved the previous one.


'''
    File name: calc-lib.py
'''

#Handles reading and returning as a string
def ReadFile(filename, path="D:\\FDMEE\\scripts\\calc\\"):
	import os
	script = open(path + filename, "r").read()
	return script
	
'''
Sample usage:
replaces = { "##YEAR##": "FY15" }
ParseCalcScript(ReadFile("CalcScript.csc"), replaces)
Returns: Parsed script with replacements
'''
#Handles parsing script and replacing variables
def ParseCalcScript(script, rep):
	import re
	#Replaces variables in script with replacement dictionary using regex
	rep = dict((re.escape(k), v) for k, v in rep.iteritems())
	pattern = re.compile("|".join(rep.keys()))
	text = pattern.sub(lambda m: rep[re.escape(m.group(0))], script)
	return text

#Handles running clear sample
def ClearScript(period, year, entity, api):
	
	#Replacement vars in script
	replacements = {
		"##PERIOD##": period,
		"##YEAR##": year,
		"##ENTITY##": entity
	}
	
	#Read the script file
	scriptContent = ReadFile("SomeClearScript.csc")
	
	#Parse script with replacements
	finalScript = ParseCalcScript(scriptContent, replacements)
	
	#Logging
	api.logInfo(finalScript)
	
	return finalScript

Now lets include both of these these in an event script. For our example we will use the BefLoad event for our planning application.


'''
    File name: BefLoad.py
'''
import sys

custLib = r'D:\FDMEE\data\scripts\custom\jython'

if custLib not in sys.path:
	sys.path.append(custLib)

#Import custom code	
import essbase-api
reload(Essbase)
from essbase-api import Essbase
import calc-lib as calcLib
reload(calcLib)

try:
	ess = Essbase()
	ess.connect()

	dynCalc = calcLib.ClearScript("JUN", "FY16", "I234537", fdmAPI)
	
	ess.calc(dynCalc)

finally:
	ess.disconnect()

So now when our BefLoad event runs the clear calc script will get executed. This little snippet of code and the Jython wrapper allows you to call scripts on a more granular level. This does run inline so if it's a long running calc the other jobs in process details will wait behind it.