EPM RADIANCE

EPM GROOVY- Performing MDX Calculations with Custom Groovy Scripts In ASO Cubes

·

Raja Stalvin

I’ve been wanting to check out the new MDX custom calculation capability, which allows us to write MDX calculations directly in ASO cubes. Although the Groovy documentation only provides an example of a constant value assignment, it is possible to write more complex MDX calculations. Before constructing the MDX formula, there are a few factors to keep in mind.

Writing Inventory Calculation MDX

Interdependent values are heavily used in inventory calculations. This may be accomplished in BSO by forcing the calc to run in Cell mode (@CalcMode(Cell)). Let’s look at how we can use Groovy to convert the BSO calculation below into MDX code.

IF(NOT @ISMBR (Jan))
   "Opening Inventory" = @PRIOR("Ending Inventory");
ENDIF;
"Ending Inventory" = "Opening Inventory" - Sales + Additions;

Setting up the POV

Pov, Source RegionPov, Source Region, and Script are the three mandatory fields to be specified for any MDX calculations written in ASO cubes with the CustomCalcParameters class.

  • pov supports only level0 Members
  • Support most of the member functions available in MDX

As usual, crossjoin is used to fix multiple dimension members in the POV. Since we have Groovy, I have written a simple function which converts a list to the crossjoin format which is supported in POV. I don’t really have to remember the brackets anymore.

Groovy
String getCrossJoins(List<List<String>> essIntersection) {
    String crossJoinString
    if (essIntersection.size() > 1) {
        crossJoinString = essIntersection[1..-1].inject('{' + essIntersection[0].join(',') + '}') { concat, members -> "CrossJoin(" + concat + ',{' + members.join(',') + '})' }
    }
    return crossJoinString
}

Groovy
List<List<String>> currentPov = [['[All Seasons].Children'], ['[FY20]'], ['[No Scenario]'], ['[No Version]'], ['[No Channel]'], ['[Day00]'], ['[Products].Levels(0).Members'],['Jan']]
def crossJoinPov = getCrossJoins(currentPov)
println crossJoinPov

Output from Groovy Console:

Notes about Source Region

If we are not referring to any members in the R.H.S formula, the Source Region is not required. If we refer any members to the formula, it is mandatory to specify the source region. The source region is supposed to be in the same format as POV.

Dependent Value Calculation

MDX fails to capture the interdependent numbers that are getting updated during the run time. So, with the help of Groovy, we can iterate on the month to make sure that the previous month is already calculated before the current month calculation:

Full Script:

Groovy
Cube cube = operation.getApplication().getCube('ASOCube')
List<String> months = ['Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec', 'Jan']
months.eachWithIndex { String currMonth, idx ->
    println "Processing: $currMonth"
    CustomCalcParameters calcParameters = new CustomCalcParameters()
    calcParameters.pov = getCrossJoins([['[All Seasons].Children'], ['[FY20]'], ['[No Scenario]'], ['[No Version]'], ['[No Channel]'], ['[Day00]'], ['[Products].Levels(0).Members'], [currMonth]])
    calcParameters.sourceRegion = getCrossJoins([['[Opening Inventory]', '[Closing Inventory]', '[Product Sales]', '[Addition]'], ['[FY19]', '[FY20]'], ["Jan", currMonth, months[idx - 1]]])
    String script = ""
    if (idx == 0) {
        script = """
			([Opening Inventory]) := ([Closing Inventory],[FY19],[Jan]);
			([Closing Inventory]) := ([Closing Inventory],[FY19],[Jan]) - ([Product Sales]) + ([Addition]);
		"""
    } else {
        script = """
			([Opening Inventory]) := ([Closing Inventory],[${months[idx - 1]}]);
			([Closing Inventory]) := ([Closing Inventory],[${months[idx - 1]}]) - ([Product Sales]) + ([Addition]);
		"""
    }

    println script
    calcParameters.script = script
    cube.executeAsoCustomCalculation(calcParameters)
}

String getCrossJoins(List<List<String>> essIntersection) {
    String crossJoinString
    if (essIntersection.size() > 1) {
        crossJoinString = essIntersection[1..-1].inject('{' + essIntersection[0].join(',') + '}') { concat, members -> "CrossJoin(" + concat + ',{' + members.join(',') + '})' }
    }
    return crossJoinString
}

Execution

It takes <2 seconds for the entire calculations where it processes more than 10000 cells, I am planning to extend the performance testing for more cells. Below is the validation from SmartView:

It takes <2 seconds for the entire calculations where it processes more than 10,000 cells. I am planning to extend the performance testing for more cells. Below is the validation from SmartView:

Groovy
calcParameters.script = """
([Opening Inventory]) :=
    CASE
        WHEN [Periods].CurrentMember IS [Feb] THEN
   	 		([Closing Inventory],[FY19],[Jan])
		ELSE
            ([Closing Inventory],[${Months[idx - 1].toString()}])
        END;
([Closing Inventory]) :=
    CASE
        WHEN [Periods].CurrentMember IS [Feb] THEN
   	 		([Closing Inventory],[FY19],[Jan]) - ([Product Sales]) + ([Addition])
		ELSE
            ([Closing Inventory],[${Months[idx - 1].toString()}]) - ([Product Sales]) + ([Addition])
        END;
"""

Comments

One response to “EPM GROOVY- Performing MDX Calculations with Custom Groovy Scripts In ASO Cubes”