Complex PBCS ASO Procedural Calcs

The notion that one cannot write complex procedural calculations in ASO was disproved about 10 years ago. I spoke on the topic for the first time at Kscope15. Now that I'm getting more involved in PBCS, I wanted to see if my old tricks worked.

I'm going to attempt to use an MDX function in an ASO Custom Calc script. According to the documentation, this shouldn't work. Probably some of you have resorted to creating formula members that handle these functions and then call those members in your calculations. Let's see if we can get around that because, frankly, it's a pain. The less outline maintenance, the better.

My test case will be very simple. I'm going to copy data from account 4120 to 4130 but the twist here is that I'll bring in the prior month of data.

This task would be trivial in a BSO Calc.

First let's start off in Calc Manager with a calculation that copies the data for the current month.

1:  CustomCalcParameters parameters0 = new CustomCalcParameters()  
2:  parameters0.Pov = "Crossjoin(Crossjoin(Crossjoin(Crossjoin(Crossjoin(Crossjoin({[FY24]},{Descendants([Channel], [Channel].dimension.Levels(0))}),{Descendants([Entity], [Entity].dimension.Levels(0))}),{[Dec]}),{Descendants([Product], [Product].dimension.Levels(0))}),{Descendants([Scenario], [Scenario].dimension.Levels(0))}),{Descendants([Version], [Version].dimension.Levels(0))})"  
3:  parameters0.target = ""  
4:  parameters0.creditMember = ""  
5:  parameters0.debitMember = ""  
6:  parameters0.script = "([4130]) := ([4120]);"  
7:  parameters0.offset = ""  
8:  parameters0.sourceRegion = "{([4120])}"  
9:  operation.getApplication().getCube('Vis1ASO').executeAsoCustomCalculation(parameters0)  

For those creating one of these scripts the first time I'll give an overview of what the lines are doing. I'm going to leave out the lines with blank arguments.

Line 2 is the POV. Think of this like your BSO fix statement.

Line 6 is the script or the formula that you want to execute. In this case we're setting Account 4130 equal to 4120.

Line 8 is the source region. If it's not in the fix statement and we need to reference the data, we need to add it in here.

Lines 1 and 9 are groovy specific for executing the calc.

This particular calculation will result in the following:

But we need to get last month's data. Let's try putting an MDX function in Line 6.

 parameters0.script = "([4130]) := ([Period].CurrentMember.PrevMember,[4120]);"  

This change passes validation but when I execute it, I get an error. This tells me that the validation (probably) only cares about the groovy syntax here.

Is there any way around this? I'm going to dig into my bag of tricks and pull out one I've been using for many years with Essbase ASO cubes. Celvin Kattookaran taught me this, I don't know how he found out that it works, I don't even want to know. Anyway, if I put an IIF function on the right side of the formula, somehow it opens up the capability to use any and all MDX functions. Let's see if we can get that to work here.

 parameters0.script = "([4130]) := IIF(1=1,([Period].CurrentMember.PrevMember,[4120]),Missing);"  

My new line includes an IIF function that is set to 1=1 which will always be true. I'm including the Period in my tuple and using the Previous Member function on it.

When I execute the calculation, I get a different error.

This error indicates that our source region doesn't have that previous month in it. This tells me that I'm on the right track and the Essbase engine has begun using the MDX function. The fix for this is relatively simple. I need to add that period into my SourceRegion on line 8. I can either add November into the SourceRegion tuple or add all the months, assuming I'm going to run this for more than just December at some point.

 parameters0.sourceRegion = "Crossjoin({[4120]},{Descendants([Period], [Period].dimension.Levels(0))})"  

Once I changed that, the script ran perfectly.

I suppose a word of caution is in order here. While I've been using this method reliably for the better part of a decade, I'm not sure it's supported. I'm nearly certain it's not officially documented anywhere. But this might just get you out of a jam and maybe you can ditch BSO/Hybrid for good. 😀

EPM Cloud ASO Plan Type Procedural Calcs

When learning EPM Cloud Planning, I went through a class that taught me you cannot perform ASO procedural calculations. I didn't like to hear I had to give up something so vital to Essbase ASO. While it's kind of true, I'll show you a work-around.

For an in-depth explanation of the problem, see this really old blog post HERE. In short, summing formula members across other dimensions is really, really difficult. The work-around is to run a procedural calc at level-0 thus storing the data. Then you let ASO do it's aggregation magic.

It's surprisingly easy in EPM Cloud Planning ASO Plan Types.

Step 1: Put the formula into a member.

If you're creating a bunch of calcs, I'd recommend putting these members in a hidden section of the hierarchy. For this example, I created a member in my Account dimension with two members under it.

On the ProcTestSource I put a very simple member formula of the number 9999. I'll get into more complex formulas in future posts.

Step 2: Create a business rule to run an allocation on the ASO cube.

For the formula, I put the Target on the left side and the Source on the right.

Now I run the business rule and retrieve my data in Smart View (or Dodeca). The column on the left is a formula that will evaluate to 9999 at every intersection. The column on the right has the stored value at level 0 and lets ASO handle the aggregations.

If you've got a better way of doing this, please reach out to me. I'm curious to see if there are other ways to accomplish this (other than doing the calculation in a BSO cube and moving the data over).

ASO Procedural Calcs -- Handling Shared Members

ASO allocations and procedural calculations do not allow you to include any dynamic members in your POV. If you include a shared member you get the following warning:

WARNING - 1300005 - The argument [POV] ignores the duplicate member [100-20].

If the prototype of the shared member is not level 0, it will fail your calculation with the following error:

ERROR - 1300033 - Upper-level members, for example [East], are not allowed in argument [POV]. Select a level-0 member. 

In order to remove any shared members from your POV, you can sometimes use the Filter() function to remove them as illustrated below. I say sometimes because this works in some versions and not in others. I currently have an open SR with Oracle that they are investigating. Hopefully it will be resolved soon. What would be even better is if there were calculation options allowing you to ignore things like dynamic members.

In this example, I'm taking all level 0 members under the Market dimension. But this might include some upper level members that are shared in alternate hierarchies. One strategy would be to only take the descendants of the original hierarchy. Another would be to filter out all shared members as shown here.

 {Filter(Descendants([Market],Levels([Market],0)), NOT Market.CurrentMember.Shared_Flag)}  

ASO Procedural Calcs Part 1

I've shown before that using ASO Allocations is generally favorable to using ASO Procedural Calcs. But when are you forced into using a Procedural Calc?

Writing a BSO calc script to subtract Forecasted Cost of Goods Sold from Actual Sales is relatively simple.

• "Margin" = "Actual"->"Sales" - "Forecast"->"COGS";

But this is where we hit a snag with ASO Allocations.

Within an ASO Allocation statement we can use the Amount and AmountContext fields. We can use those to set the source of the data I'm going to allocated. The Amount field allows us to do things like this:

• Amount "([Sales] - [COGS])"

as well as some simple arithmetic. We can use the AmountContext field to fine tune Amount field. We can add something like this:

• AmountContext "([Actual])"

or like this:

• AmountContext "([Actual],[Jan])"

What we cannot do is cross dimensions within the Amount field like this:

• Amount "([Sales],[Actual] - [COGS],[Forecast])"

>>ERROR - 1200679 - The amount expression is invalid. Only arithmetic operations and members from a single dimension are allowed.

This is the point at which we need to switch over to using a Procedural Calc. Here's an example:

1:  Execute Calculation on Database Sample.Basic With  
2:  LOCAL SCRIPT FILE "simple_calc.csc"  
3:  POV "CrossJoin({[Jan]:[Dec]},  
4:  CrossJoin({[Forecast]},  
5:  CrossJoin({Descendants([Total Markets],Levels([Market],0))},  
6:  {Descendants([Total Products],Levels([Product],0))})))"  
7:  SOURCEREGION "CrossJoin({[System Margin],[Sales],[COGS]},  
8:  CrossJoin({[Jan]:[Dec]}, {[Actual],[Forecast]}))";  
9:    
10:  /* Contents of simple.calc.csc */  
11:  ([System Margin]) := (([Actual],[Sales]) - ([Forecast],[COGS]));  
12:    

If you're translating many BSO calc scripts to ASO calcs then chances are good that you'll run into one that forces you to use a Procedural Calc. Why the developers chose to implement Procedural Calcs and Allocations in such a different manner doesn't make a lot of sense to me but my hope is that they will extend the functionality in them.