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.
