EPM Groovy – Beyond Reporting: Empowering ASO with Dynamic Calculations through Groovy

Groovy APIs and Edited Cells

Since Groovy APIs introduced access to edited cells, there has been significant adoption for using it in form-based business rules. Unsurprisingly, with all calculations written in BSO cubes, FIXes were dynamically updated using Groovy business rules. But what about ASO cubes?

ASO Cubes Step Up

ASO cubes were traditionally used primarily for reporting purposes. However, with the introduction of Groovy APIs, this trend is shifting. In the past, for simple calculations, we relied on BSO cubes and transferred the calculated data to the reporting cube. Groovy now offers more flexibility, allowing calculations to be performed directly within ASO. Notably, it provides two powerful methods specifically designed for allocation and custom calculations.

Beyond Custom Calc and Allocation Templates

Previously, both calculations and allocations were primarily performed using Calc Script templates. These templates require admins to fill in specific details, and the system handles the calculations. While this is a straightforward approach, it offers limited flexibility. To create dynamic templates, you can leverage either runtime prompts or substitution variables.

Case Study: Streamlining Local to Currency Data Copy with Groovy

I have created a calc template that copy data from Local to currency as below:

Now, let’s say you want to attach this template to your form. However, there’s a drawback: it will run for all entities, regardless of edited rows. To address this, you’ll need to convert the template to run based on edited values.

The good news is that it’s a simple process. Just switch the script mode from Designer to Editor. The EPM development team has conveniently provided a Groovy equivalent script that’s ready for your edits:

This is what the script might look like:

Note: There is a bug in the conversion, if the template has UDA references, the quotes are messing the pov format and your script wont be validated

To simplify this process,I’ll demonstrate this with a USD currency conversion example.

Let’s add two runtime prompts and configure the calculation to run only for edited entities.

/* RTPS: {Var_Dim_Scenario} {Var_Dim_Year} */

My goal is to capture edited entities from the form, excluding entity dimension members. Everything else is considered static or derived from runtime prompts (RTPs). To avoid parsing complex crossjoin strings , I’ve created a List> object to capture static POV members for each dimension.

List<List<String>> calcPov  = [
['[No Unit]'],
['[USD]'],
['[Working]'],
['[Month]'],
['[Amount]'],
['[No Account]'],
[mdxParams(rtps.Var_Dim_Scenario.member)],
[mdxParams(rtps.Var_Dim_Year.member)],
['Descendants([YearTotal], [YearTotal].dimension.Levels(0))']                                  
]

The above object will be converted to Crossjoin String using the below method:

String generateCrossJoins(List<List<String>> comb) {
	return comb.tail().inject("{${comb.first().join(',')}}") { prev , curr -> "CrossJoin(${prev},{${curr.join(',')}})" } as String
}

Capture the edited entities from the form:

List<String> editedEntities = operation.grid.dataCellIterator({DataCell cell-> cell.edited}).collect{DataCell cell -> cell.getMemberName("Entity")}.toUnique()

Lets create the custom calc parameter object and execute it:

calcUsdParams.with {
	pov = generateCrossJoins(calcPov << [mdxParams(editedEntities)])
	sourceRegion = '{[Local]}'
	script = "([USD]) :=  ([Local]);"
	roundDigits = 0
}
operation.getApplication().getCube('FINRPT').executeAsoCustomCalculation(calcUsdParams)

It’s a play script, so I haven’t handled null exceptions.

Let me add the script in the form and save the form:

USD data before edit:

Editing only Melbourne Entity in Local Currency:

USD in Melbourne is updated however other entities data is intact:

And..

In conclusion, Groovy APIs have revolutionized the way we interact with Essbase data using the allocation and custom calc APIs. By enabling access to edited cells and providing powerful calculation methods, Groovy empowers users to create more flexible and dynamic form-based business rules. As we move forward, we can expect to see even greater adoption of Groovy, not only for calculations but also for streamlining complex tasks and workflows within Essbase. I hope this post is helpful.