Oracle EPM - Gettin' Groovy with Complex, Dynamic ASO Allocations

Allocations.... everyone hates them, but they are a part of corporate finance for better or worse. Since allocations are normally driver based on dynamic subtotals, they can be difficult for newcomers to the Oracle EPM space to handle in the traditionally calc-heavy BSO cubes due to block creation issues. My task was to rewrite a complex BSO allocation driven by Attributes into an ASO cube. Here's the BSO logic I started with:

The general idea is that I have an attribute dimension that represent different groupings for the allocation to process. Each cost center is tagged with this attribute, and I reference a similarly named input member that has the value to allocate in it. For example, cost center CC_xxxxx would be tagged with attribute member "ATTR_Group1_Input" and would need to reference member "Group1_Input" for the calculation. It looks something like this:

Now, those of you familiar with Oracle's implementation of MDX might notice an initial problem... the use of the @SUBSTRING function to dynamically generate a new member name. We'll get to that in a bit... my first issue was just getting part of the logic to run.

Generally, my approach to doing anything calc-related in an ASO cube is to start using Oracle's Graphic Designer and then convert to a script object, as I'm not nearly as comfortable with MDX as I am with BSO Calc Scripting. I started out by just trying to get a percentage of total headcount to calculate correctly, but I ran into some weird issues between the Graphic Designer and the converted script object. You can see more about it on my Customer Connect post here, but the tl;dr is that when I built it out in the graphic designer, it worked fine, but once I converted into a script object and ran (without changing anything else), it didn't generate any results. This blew my mind, and I ended up calling a former colleague to see if I was being crazy (spoiler, while we may both be crazy, we weren't about this).

Anyways, I took a break from fighting the graphic designer and turned back to my core substring problem. How can I handle that dynamic reference? The simple answer ended up being to create members in the outline that were dynamic calcs that referenced the attribute directly. So for example, if my attribute was "ATTR_Group1_Input", I made a member called "Group1_Input_RPT" and made it's formula "([Total Cost Center],[ATTR_Group1_Input])".

The nomenclature is very important here... I made the names of the attribute members, input members, and reference members follow a very similar pattern so that I could perform string operations on them to dynamically make the references. "Now Patrick," you might say, "you can't do string operations in MDX formulas!" Fear not, worried reader, for this is were GROOVY comes into the equation! (EDIT: some folks have reached out and said they have gotten MDX string formulas to work in member formulas, so your mileage may vary)

The first thing to note is that EPM Cloud ASO calculations are all generated via Groovy calls to the API. Even the most basic script in the graphic designer ends up being converted to Groovy behind the scenes, leveraging the executeAsoCustomCalculation method against the cube.

Let's discuss what this basic approach is doing before we dive into my monstrosity. We start by create a CustomCalcParameters object which we will use to store the parameters of the calculation. The first parameter is the POV, which is very similar to a BSO FIX statement. Basically, you put the intersections you want to write to here. ASO notation requires the use of many nested Crossjoin() functions, which looks ugly and is hard to maintain, but I have a solution for that too ;)

The next three parameters are generally optional. To be honest, I'm not sure what the Target parameter is for... I think its a leftover from the on-prem MaxL commands. I don't even see an explanation for it in the Groovy techref. The creditMember and debitMember are used to book offsetting entries if required for accounting purposes, which I don't use in this usecase.

The script parameter is where the magic happens. This is basically the formula from your BSO script. Now, unfortunately by default the MDX calc engine won't allow MDX member operators in this script parameter... however, Timothy Faitsch found a solution to this on his blog by using IIF(1=1, script, missing) to handle things that require those MDX functions. I don't need to use that here but wanted to call it out, since it's a great find.

The offset parameter is again optional.... but now we have the big one. The sourceRegion parameter is where you need to reference any intersections that are leveraged in the script, but are not in the POV. Let's dive into this a little more since this took me a while to wrap my head around, coming from BSO.

In my BSO logic above, I FIX'ed on my target intersections and just put my references in the member formula, and that's that. ASO requires you to be more implicit and tell it exactly what slices of the database to pull into memory to handle the calcs. So in my BSO script, I'm writing to entity CO_101 and location LC_DBUS001, but my amount to allocate is stored at No_Entity and No_Location. Similarly, I need to reference subtotals stored at "Total Location", etc. in my logic. All of these additional intersections must be crossjoined together and put into the sourceRegions parameter. Tedious, but it works.

Once you have that, you can reference your cube and tell it to run the calc command!

Now, back to my dynamic generation issue. As you can tell from my screenshots above, I have 15 attributes I need to reference for my allocation. I don't want to write 15 discreet blocks of MDX calcs for maintenance purposes... so, I can use my naming relationships and the power of Groovy to do things all at once!

Here's my friend in all it's glory:

In a nutshell, what this script is doing is looping through the attribute dimension, taking the name of the attribute member, performing string operations on it to convert it into the appropriate reference, and generating a calc per member of the attribute dim.

Let's do the needful and break down each section:

Cube PnLcube = operation.application.getCube('<cube>')        

This is the basic starter to get the cube reference in memory. We need this to execute the calcs.

Dimension costCenterDim = operation.application.getDimension('Cost_Center')
AttributeDimension attr = costCenterDim.getAttributeDimensions().find{it.name == "MgmtHierForAttrition"}
def attrDimMembers = attr.getEvaluatedMembers(/ILvl0Descendants("MgmtHierForAttrition")/,PnLcube)        

This handles generating the list of members in the attribute dimension.

String createCrossJoins(List<String> mbrs){
return mbrs.tail().inject("{${mbrs[0]}}") {prev, curr -> "CrossJoin(${prev},{${curr}})"} as String}        

This little blurb takes a list of members and nests them into crossjoins. So ['[account1]','[costcenter1]','[entity1]'] will generate 'Crossjoin(Crossjoin({[account1]},{[costcenter1]}),{[entity1]})'. This is really optional, if you prefer writing crazy nested Crossjoins, you can ignore this part.

The first initial pass just 0s out the values to make sure the calc handles things fresh. Yes, this will create a slice in the ASO cube with 0s, but you can merge slices and clear 0s to get rid of that.

The fun part happens here:

/* objects for dynamic calcs */
List<String> POV = []
List<String> Source
String Script
/* for each member of the attribute dimension, loop through and generate a calc */
attrDimMembers.each{ it ->
println("Calculating vacancy savings allocation for " + it.toString() + " attributed members.")
POV = ['[Manual Bonus],[Manual Salaries & Wages]',"ATTRIBUTE([${it.toString()}])".toString(),'[CO_101]','[LC_DBUS001]','Descendants([YearTotal], [YearTotal].dimension.Levels(0))','[PD_00000]','[PJ_00000]','[Forecast]','[Non_Vendor]','[Working]','[Periodic]','[&FcstYr]']
params.Pov = createCrossJoins(POV)
Source = ['[Total Regular Headcount]',"[${it.toString().substring(5)}],[${it.toString().substring(5) + '_RPT'}]".toString(),'[Local_Amt]','[CO_999],[No_Entity]','[Total_Location],[No_Location]','[Total_Projects]']
params.sourceRegion = createCrossJoins(Source)
Script = "([Local_Amt]) :=  ([Total Regular Headcount],[CO_999],[Total_Location],[Total_Projects],[Local_Amt]) / ([Total Regular Headcount],[CO_999],[${it.toString().substring(5) + "_RPT"}],[Total_Location],[Total_Projects],[Local_Amt]) * ([No_Entity],[No_Location],[${it.toString().substring(5)}]);"
params.script = Script
 PnLcube.executeAsoCustomCalculation(params)
println("COMPLETE: Vacancy savings allocation for " + it.toString() + " attributed members.")
}        

We start out by creating initial variables to store the dynamically generated POVs, Sources, and Scripts.

We take the list of attribute members and loop through them leveraging the .each{it->} approach. Notice nested functions that handle converting the attribute member object to string and performing required operations to them. Additionally, another thing to note is the difference between single quotes and double quotes. Double-quoted objects allow for the inclusion of nested functions like this: "[${it.toString().substring(5)}],[${it.toString().substring(5) + '_RPT'}]". However, this returns a GString object (don't laugh, that's what its called...). To pass into my createCrossJoins method, I convert it to a static string object. Just a technical nuance to notice.

But yeah, that's really it! We loop through each member of the attribute dimension, convert them accordingly, and generate POVs, Sources, and Scripts, and run the calcs all in one. We can pass the attribute into the ATTRIBUTE() MDX function to return the base cost centers for the POV, then use string functions to convert them into different references. The ${it.toString().substring(5)} approach trims off the ATTR_ header so we can reference the input member for the allocation, and the ${it.toString().substring(5) + '_RPT'} approach adds the _RPT to the end to reference the attribute reference members I added to the outline.

This runs in a second, substantially faster than its BSO equivalent in our current production process.

That was a long one! But felt proud about figuring this out so wanted to share it case it helps anyone else :)