Oracle EPM - Gettin' Groovy with ASO Custom Calculations

Warning, this is gonna be a longer and more technical one!

So, ASO cubes. They are both the most amazing and most frustrating part of working with Oracle EPM Cloud. They are amazing in their performance capabilities, their potential to hold massive amounts of data, the ability to convert BSO Smart Lists to ASO dimensions, and the flexibility that you get with member formulas. They are frustrating due to poor, poor Oracle documentation, the outdated version of MDX that they use as the scripting language, and how relatively confusing it can be to work with custom calculations in ASO business rules to handle even simple procedural calcs.

One of my latest projects involved creating an operational planning application where user wanted the ability to plan for things across multiple line items, accounts, and vendors for a given cost center. We then needed to report on the consolidated OpEx lines, blend it with workforce data, and then consolidate it all and handle corporate allocations and other consolidations. So of course, we opted for a multi-cube design that has OpEx planning in one BSO cube, workforce planning in another BSO cube (custom... I'm sorry, I don't like dealing with Oracle's OOTB modules. I'll build it myself), and a consolidated ASO cube. Data is merged between them through data maps and smart pushes.

Nothing new or crazy here so far. Though, we ran into some issues. How can we handle our allocations in the ASO cube? How can we handle direct inputs to the consolidated data with currency conversion? What about procedural calcs like clearing and copying data around?

Well, as always anymore, the answer is GROOVY!

An Introduction to ASO Custom Calculations

Writing ASO custom calculations in Planning is a little more involved than your normal BSO calc script. You either need to work through the default Calc Manager wizard to build the commands or you can directly write the Groovy yourself. Now, I personally dive right into the Groovy since I'm familiar with it, but to get used to it you might want to build some things with the wizard first and convert the rule to Groovy to see what it looks like. Here is the Oracle TechRef on this for Planning, and here is the TechRef on it for Essbase 21 (which should be coming to Planning sometime in H1 2025).

Now, writing a purely custom ASO calc involves the use of the CustomCalcParameters class. Leveraging this allows you to create the shell of the calculation command that you then populate the drivers in. Each execution of the custom calc can only have one POV (think of this as your FIX statement, more to follow), so if you are trying to recreate a BSO process with multiple FIX statements, you will need to have multiple custom calc executions. But, you can do this in a single Groovy rule, so there are limited issues!

I'm going to take the example from the Oracle docs, make it a little more advanced, and then break it down piece-by-piece.

 /*RTPS: {curYear}*/
 CustomCalcParameters params = new CustomCalcParameters()
params.pov = 'Crossjoin(Crossjoin(Crossjoin(Crossjoin(Crossjoin(
 Crossjoin(Crossjoin({[Jan]},{${mdxParams(rtps.curYear)}}),{[Current]}),
 {[BU Version_1]}),{[No Currency]}),
 {[No Entity]}),{[No Manager]}),{CHILDREN([Total Employees])})'
params.sourceRegion = 'Crossjoin({[Salary_Driver]},{[BegBalance]})'
 params.script = '([Salary]) := ([Salary_Driver],[BegBalance]);'
 params.roundDigits = 2
 operation.getApplication().getCube('ASO').executeAsoCustomCalculation(params)        

In BSO script, this would read as:

FIX("Jan", {curYear}, "Current", "BU Version_1", "No Currency", "No Entity", "No Manager", @CHILDREN("Total Employees"))
"Salary" = "Salary_Driver"->"BegBalance";
ENDFIX        

First, we start by declaring the CustomCalcParameters object that is used to handle the logic. I store this in a variable called params.

Then, we need to define the POV of the script. You can think of this like a BSO FIX() statement. Your POV is where the calculation logic will operate on, again just like a BSO calculation. The major downside here is that you need to write the POV as a set, which means you need to leverage a bunch of nested Crossjoins for calculations. I have written a Groovy helper function that makes this a lot easier, which I'll cover later. So in this case:

'Crossjoin(Crossjoin(Crossjoin(Crossjoin(Crossjoin(
 Crossjoin(Crossjoin({[Jan]},{${mdxParams(rtps.curYear)}}),{[Current]}),
 {[BU Version_1]}),{[No Currency]}),
 {[No Entity]}),{[No Manager]}),{CHILDREN([Total Employees])})'        

Translates to:

FIX("Jan", {curYear}, "Current", "BU Version_1", "No Currency", "No Entity", "No Manager", @CHILDREN("Total Employees"))        

Get used to going cross-eyed and staring very closely at the different nested bracket/parentheses types. [Square Brackets] denote a member name. (Parentheses) can denote either a tuple/cross-dim, the parameters of a function, or the order of operations precedence. {Curly braces} denote member sets, which don't have as clear a comparison to BSO calcs other than something like member ranges. MDX uses these liberally, so get used to it! Additionally, note the use of the mdxParams() function. This will basically take any string or list, flatten it, and put it in square member brackets to make it MDX ready.

Ok, so now we have our POV. Pretty straightforward conceptually, just ugly to look at. Now comes the first new topic, the sourceRegion. In ASO custom calcs, you have to explicitly denote the set from which your data is derived. Meaning, if you have any members on the right-side of the script/equation, that member set needs to be called out here. Looking slightly ahead, the script object says '([Salary]) := ([Salary_Driver],[BegBalance]);', so Salary_Driver and BegBalance need to be in the sourceRegion parameter. BSO calcs don't have a parallel to this, the calculation engine just handles this for you. One nice thing is that you don't have to be exact.... you can put more members in the source region than you will absolutely need, but putting too much in there can degrade performance, as the calc engine pulls those sets into memory to perform the calculation. This seems straightforward for a simple calc like the above, but when you start to get into conditional or more complex calcs, the source region can start to grow a bunch. I'll show some examples further down that demonstrate this.

Next, even though we kinda talked about this already, is the script itself.

params.script = '([Salary]) := ([Salary_Driver],[BegBalance]);'        

Some things to note. Both sides of the script always need to be denoted as a tuple, i.e. in parentheses, even if there is only one member. Hence why we have ([Salary]) rather than just [Salary], even though there is only one member. The equals sign is also := instead of just = for.... MDX reasons. Finally, the command must end with a semicolon.

Next is one of the most annoying things Oracle has ever done.

 params.roundDigits = 2        

For some god-forsaken reason, ASO Custom Calcs in Planning default to integer operations for certain types of calculations. That's right, integer. Round numbers only. No decimals. I have yet to decern the rhyme or reason behind when this is true, but it definitely is in certain cases. You need to explicitly tell the rule if you want it to handle decimals. I was pulling my hair our working on an allocation model (you know, dealing with percentages) and trying to figure out why it wasn't working. I went around in circles for hours only to find out that this stupid little parameter was my problem. I'm sure there's a valid technical reason for this.... maybe.... but I really, really dislike it. Anyways, the best thing to do is to remember to set this to the appropriate number of significant figures in your application. Likely 4-6 or something like that. I hope someone proves me wrong on this and shows me a better way, because I can't believe this is what solved my issue. I'll happily edit this if so!

Ahem.... rant over. Anyways... the last part is very straightforward, you execute the customCalcParamaters object against the ASO cube.

operation.getApplication().getCube('ASO').executeAsoCustomCalculation(params)        

The thing to note here is that you can define the cube at execution, so your business rule artifact does not need to be associated with the ASO cube to run the commands against it. Additionally, running this does not end the business rule execution inherently, meaning you can daisy chain multiple calculation executions together in a single business rule object.

An extremely important thing to note is that as of writing this (Jan 2025), Planning is not yet on Essbase 21. This means that we cannot leverage MDX optimization commands like NONEMPTYMEMBER and NONEMPTYTUPLE in custom calcs directly. Why does this matter? Well, in BSO, the calculation engine inherently only operates where blocks exist. This means that it doesn't process unnecessary or nonexistent cells. However, ASO doesn't have the concept of blocks. This means that inherently, ASO calcs will operate on every data point identified in your POV crossed with your sourceRegion... which can be a massive number of cells. It is the product of the resultant member sets from each dimension, which can scale into billions of cells very quickly. The NONEMPTY commands allow us to tell the engine to ignore empty cells, but since this isn't supported in the script directly, we need to find another way.... oh, whatever shall we do?

Leveraging Stored Member Formulas in ASO Custom Calculations

Fear not, just like in BSO calcs, we can leverage stored member formulas in ASO rules! Here's an example:

You want to handle your currency conversion in an ASO cube so that you don't have to worry about pushing the full dataset after updating FX rates. How can you do this? Well, a general BSO currency conversion will FIX on pretty much everything to make sure that every datapoint, if it exists, gets converted. The BSO calc engine only operates where blocks exist (or you force it to by doing @CALCMODE(BOTTOMUP) or SET FRMLBOTTOMUP ON). As discussed above though, ASO doesn't do this. Depending on your outline size, this could result in calculating 20 legal entities x 300 cost centers x 200 location codes x 200 product codes x 2000 project codes x 12 months which is 5.76 trillion data cells. The ASO engine is fast, but that is a LOT of cells.

Most applications won't have data at most of those cells. That's why BSO exists in the first place, to optimize how that data is stored. So in the ASO calc, we need to leverage a NONEMPTY command to optimize the calculation so it doesn't process 5.76 trillion cells, but only the ones we need. We can do this by creating a stored member in a dimension that doesn't have any data, but we give it a formula. Put this in a Currency, Plan Element, or your View dimension. The member formula would look something like this:

NONEMPTYTUPLE([Local],[Periodic])
([Local],[Periodic]) * (StrToMbr(Substring([Entity].CurrentMember.[Currency Attribute],1,3)),[No Entity],[No Cost Center])        

The NONEMPTYTUPLE command at the top is saying that the system should evaluate the Local->Periodic intersection to see if there is a value before pulling it into the calculation buffer. This allows the system to optimally perform the currency conversion only where there is local currency data. The StrToMbr() stuff is a trick I learned from an old blog of Cameron Lockpour, where we tag the entity that denotes the currency conversion (in this case, Entity) with an attribute that we then associate via naming convention with the member that stores the FX rate. So that function is saying "get the current member being calculated in the Entity dimension and check the value of the Currency Attribute assigned to it". Let's say this member is "EUR_Attr". Take the substring for the first 3 characters, and use that as a member in the tuple. In this case, the FX rate is stored at an account called "EUR".

Once we create this member formula, "calling" it in the calc script is very easy. All we need to do it make it the only thing on the right side of the equation and include it in the source region.

Before I show that, I want to quickly show my helper function for Crossjoins, because I HATE seeing millions of nested Crossjoin functions.

 /* custom function to generate nested crossjoins from member list */
String createCrossJoins(List<String> mbrs){
    return mbrs.tail().inject("{${mbrs[0]}}") {prev, curr -> "CrossJoin(${prev},{${curr}})"} as String
}        

This function will let us make the large POV way more readable. I'm not going to break this down here, but it does this needed.

RTPS:  {RTP_Scenario} {RTP_Year} {RTP_Periods}
println "Performing currency conversion for ${rtps.RTP_Scenario}->${rtps.RTP_Year} for periods ${RTP_Periods}"
List<String> POV
List<String>Source
String Script
Cube ASOCube = operation.getApplication().getCube('ASO')

CustomCalcParameters params = new CustomCalcParameters()
POV = [
'Descendants([TrialBalance],[Account].Levels(0))',
'Descendants([Total Cost Center],[Cost_Center].Levels(0))',
'Except({Descendants([Total_DBX],[Plan_Element].Levels(0))},{[Balance_Sheet_Cumulative],[Final_Cash]})',
'Descendants([Total Entity],[Entity].Levels(0))',
'Descendants([Total_Location],[Location].Levels(0))',
'Descendants([Total Product],[Product].Levels(0))',
'Descendants([Total Projects],[Projects].Levels(0))',
'Descendants([Total Vendor],[Vendor].Levels(0))',
'[Working]',
mdxParams(rtps.RTP_Scenario),
mdxParams(rtps.RTP_Periods),
mdxParams(rtps.RTP_Year),'[Periodic]','[USD_Reporting]'
]
Source = [
'[Currency_Conversion]'
]
CustomCalcParameters params = new CustomCalcParameters()
params.pov = createCrossJoins(POV)
params.script = "([USD_Reporting]) :=  ([Currency_Conversion]);"
params.sourceRegion = createCrossJoins(Source)
ASOCube.executeAsoCustomCalculation(params)
println("Currency conversion completed successfully.")        

As you can see above, the POV and Source now originate as a list object, where you put the common dimensions together as an entry in the list. The helper function will parse them out and merge them into Crossjoins() for you. It makes reading them just that much easier. The sourceRegion is just the Currency_Conversion stored member we created above. The script is just saying to make USD_Reporting = Currency_Conversion. This will execute and call the stored member formula, which includes the NONEMPTYTUPLE command, and store the results into the USD_Reporting member. It works and is insanely fast. One additional benefit is that you don't seem to need to worry about the roundDigits parameter when this approach is leveraged... not sure why, but that seems to be the case.

But what if you are working on only a small subset of data, or you want to use Groovy to operate on only modified cells? Well you can of course do all the needed things right in your script object!

Custom Calcs without Stored Member Formulas

First off, I've written other articles where I cover dynamically identifying your modified members, so I recommend reading that as well because I use the results of that function (generating the map of modified members, or modMbrs) in this example.

POV = [mdxParams(modMbrs.get("Account")),
'[USD_Reporting]',
mdxParams(modMbrs.get("Cost_Center")),
mdxParams(modMbrs.get("Plan_Element")),
mdxParams(modMbrs.get("Entity")),
mdxParams(modMbrs.get("Location")),
mdxParams(modMbrs.get("Product")),
mdxParams(modMbrs.get("Projects")),
mdxParams(modMbrs.get("Vendor")),
mdxParams(modMbrs.get("Scenario")),
mdxParams(modMbrs.get("Version")),
mdxParams(modMbrs.get("Years"))
'[Periodic]']
Source = ['[No_Entity]','[No_Cost_Center]','[No_Location]','[No_Project]','[Non_Vendor]','[Periodic]','[No Product]','[No_Plan_Element]','[Local_Amt]','Descendants([FX_Rates])']
CustomCalcParameters params = new CustomCalcParameters()
params.Pov = createCrossJoins(POV)
params.script = """
([USD_Reporting]) :=  
IIF(1=1,
CASE
WHEN IsAccType([Account].CurrentMember,Expense)
THEN ([Local_Amt]) * (StrToMbr(Substring([Entity].CurrentMember.[Currency Attribute],1,3)),[No_Entity],[No_Cost_Center],[No_Location],[No_Project],[Non_Vendor],[Periodic],[No Product],[No_Plan_Element])
WHEN Not IsAccType([Account].CurrentMember,Expense)
THEN ([Local_Amt])
END
,Missing);"""
params.sourceRegion = createCrossJoins(Source)
params.roundDigits = 2
PnLCube.executeAsoCustomCalculation(params)
println("Currency conversion completed successfully.")        

The one new trick here is something that Timothy Faitsch posted about recently, leveraging the IIF(1=1, calc) structure in an ASO custom calc. Basically, default MDX calc's don't allow for dynamic member references that you build, like using the Substring function and then converting it to a member. But, if you put it in an IIF() function and use 1=1 as the condition, which is always true, it seems to work. A neat little trick! So basically, what I am doing here is recreating the stored member formula logic directly in the script object, but passing in only the modified members to limit the scope of the calculation. Honestly, with how fast the stored member formula approach is, this is kinda unneeded, but a great example regardless. It shows how we can use complex conditions, i.e. the CASE statement, in our script object, and gives an example of how the sourceRegion can get more complex. Note that we need to include all the FX rates along with the other intersections they are stored in.

Handling Data Clearing in ASO Custom Calcs

A common command in BSO calcs is CLEARDATA. You use this if you are copying data around and want to make sure the target is clear, or if you are doing an allocation and want to make sure that the allocated amounts don't have anything stale leftover. In ASO, there is no CLEARDATA command, but there is a .clearPartialData method! This method allows you to pass a member set (you can use my helper function) and a true or false parameter indicating if it is a logical or physical clear. A logical clear is very quick, and the system creates a new data slice with an offsetting value to bring the aggregate to 0 for the cells in that set. A physical clear goes and removes all the data slices for that set, which can be a lot slower. The downside to this function is that, according to the techref, it puts the cube in read-only mode. This means that users can't submit anything to the application. An alternative to this is to have a basic calculation that does ([member]) := ([member]) / 0; which results to missing, but this can be long-running due to the lack of support for NONEMPTY commands in Planning at present.

Here's an example of this for a data archiving (snapshot) rule.

/*RTPS:{RTP_Year} {RTP_Source_Scenario} {RTP_Source_Version} {RTP_Target_Scenario} {RTP_Target_Version}*/
POV = [
'Filter(Descendants([Account],[Account].Levels(0)), (Not [Account].CurrentMember.Shared_Flag and [Account].CurrentMember.Member_Type <> 2))',
'Descendants([Total Cost Center],[Cost_Center].Levels(0))',
'Except({Descendants([Total_Plan_Element],[Plan_Element].Levels(0))},{[Balance_Sheet_Cumulative],[Final_Cash]})',
'Descendants([Total Entity],[Entity].Levels(0))',
'Descendants([Total_Location],[Location].Levels(0))',
'Filter(Descendants([Product],[Product].Levels(0)), (Not [Product].CurrentMember.Shared_Flag and [Product].CurrentMember.Member_Type <> 2))',
'Descendants([Total Projects],[Projects].Levels(0))',
'Descendants([Total Vendor],[Vendor].Levels(0))',
mdxParams(rtps.RTP_Target_Scenario),
mdxParams(rtps.RTP_Target_Version),
'Descendants([YearTotal],Levels([Period],0))',
mdxParams(rtps.RTP_Year),
'[Periodic]','[Comments],[Local_Amt],[USD_Reporting]']
Source = [mdxParams(rtps.RTP_Source_Scenario),mdxParams(rtps.RTP_Source_Version)]
params.pov = createCrossJoins(POV)
/* clear target */
CalcStartTime = currentTimeMillis()
ASOCube.clearPartialData(createCrossJoins(POV),false)
CalcEndTime = currentTimeMillis()
println("ASO logical data clear complete. \nCalc Script Generation and Runtime: " + (CalcEndTime - CalcStartTime)/1000 + " secs")
params.script = """
/* Copy from source */
(${mdxParams(rtps.RTP_Target_Scenario)},${mdxParams(rtps.RTP_Target_Version)}) :=  (${mdxParams(rtps.RTP_Source_Scenario)},${mdxParams(rtps.RTP_Source_Version)});
"""
params.sourceRegion = createCrossJoins(Source)
CalcStartTime = currentTimeMillis()
params.roundDigits = 2
PnLCube.executeAsoCustomCalculation(params)
CalcEndTime = currentTimeMillis()
println("ASO snap complete. \nCalc Script Generation and Runtime: " + (CalcEndTime - CalcStartTime)/1000 + " secs")        

Here you can see a few additional things you can leverage. In the POV, I use a Filter() function to return only level 0, stored, base members. I also use an Except() function to remove some base members from a set. I perform a partial clear on the ASO cube, then I copy the data from my source scenario/version to my target scenario/version (via runtime prompts). I also have some additional functionality in there for logging, which I discuss in my other posts about standard Groovy templates and logging.

Chaining Calcs Together

As alluded to before, each calc execution is limited to a single POV. So what if you need to do things across multiple POVs? Here's an example.... in a single business rule, you just need to build multiple "calc blocks" that update the parameters respectively and then run each of those executions.

/* Calc 1 - Consolidate Fringe to CC 43012 */

POV = ['[Account1]','[Local_Amt],[USD_Reporting]','[CostCenter1]','[PlanElement1]',
'Descendants([Total Entity], [Total Entity].dimension.Levels(0))',
'Descendants([Total_Location], [Total_Location].dimension.Levels(0))',
'[PD_00000]','Descendants([Total_Projects], [Total_Projects].dimension.Levels(0))',
'[Non_Vendor]','[Working]','[Periodic]',Scenario,Period,Year]
println("Consolidating values to cost center 1")
Source = ['[Total Cost Center]']
CustomCalcParameters params = new CustomCalcParameters()
params.Pov = createCrossJoins(POV)
params.script = "([Account1]) :=  ([Total Cost Center]);"
params.sourceRegion = "{([Total Cost Center])}"
 params.roundDigits = 2
ASOCube.executeAsoCustomCalculation(params)
println("Account consolidated successfully.")

/* Calc 2 - Clear out everything except cost center 1 */
POV = ['[Account1]','[Local_Amt],[USD_Reporting]','Except({Descendants([Total Cost Center], [Total Cost Center].dimension.Levels(0))},{[CostCenter1]})','[PlanElement1]',
'Descendants([Total Entity], [Total Entity].dimension.Levels(0))',
'Descendants([Total_Location], [Total_Location].dimension.Levels(0))',
'[PD_00000]','Descendants([Total_Projects], [Total_Projects].dimension.Levels(0))',
'[Non_Vendor]','[Working]','[Periodic]',Scenario,Period,Year]
println("Clearing everything besides cost center 1")
params.Pov = createCrossJoins(POV)
params.sourceRegion = createCrossJoins(Source)
Script = "([Account1]) := ([Account1]) / 0;"
params.script = Script
params.sourceRegion = "{([Account1])}"
ASOCube.executeAsoCustomCalculation(params)
CalcEndTime = currentTimeMillis()
println("Cost centers cleared successfully. \nCalc Script Generation and Runtime: " + (CalcEndTime - CalcStartTime)/1000 + " secs")
CalcEndTime = currentTimeMillis()
println("Consolidation completed successfully. \nCalc Script Generation and Runtime: " + (CalcEndTime - CalcStartTime)/1000 + " secs")        

You can see here, in the same business rule we have two different POVs and two different calcs. These run in order, so you can put a bunch of different calcs together. You can have one calc that does the logic in the script and other that references the stored member formulas. The potential is quite great, and I'm hopeful that Oracle continues to improve on the ASO calculation engine and make writing calcs for it more user friendly, as not having to deal with block creation is quite nice :)

This was a long and dense post! Hopefully folks learned something new from it. I know I had a bit of headache wrapping my head around the nuances of Groovy custom calcs in ASO cubes, so I wanted to centralize all my learnings for anyone struggling with it.