Oracle EPM 11.1.2 - Essbase Calculations on ASO Cubes - Part 2 - Maxl scripts and EAS

Venkatakrishnan J -

In the last blog post, i covered the new calculation manager feature to run calculations on an ASO cube. But to be honest, i somehow felt it was an overkill atleast for running ASO specific calculations alone. The advantage of calc manager can become apparent if you are using Planning or HFM and if you would like to have a common place for creating, executing & scheduling scripts. But for standalone essbase installations, i always like the MaxL approach since that provides a lot of flexibility.

In this release, as i mentioned yesterday, the calculation scripts will have to be written in a separate text editor or the BSO calc script editor. There is no syntax validation available. So, ensure that you get the syntax correct atleast for the POV part.

Lets look at the various aspects that are involved in the creation of an ASO Calculation through MaxL.

  1. Calc Script - This basically is a file (like the BSO .csc file) that will contain the actual calculation. The calculation currently does not support MDX member commands like Descendants etc. Only constant Tuples are supported. An example script is given below
([Bud Var],[Jan]) := 100;

In the above example, the calculation script basically assigns a value of 100 to all the cells with the matching Tupule.

  1. POV - This basically is used during the execution of a calculation script. This determines the context of the calculation. For example, we can determine the combination of other dimensions not specified in the Tupule above. It is expressed as an MDX Set (not a Tupule). There is a very important difference between an ASO and a BSO calculation. If the POV includes the same dimension as the Tupule members in the calc script, then all the same dimensionality members in the POV will add themselves(can be controlled through Debit & credit members) and overwrite into the Calc Script Tupule Member combination. I will cover it in the examples below.

  2. SourceRegion - A calc script can potentially have more than one calculation. For example, if you look at the calculation script below

([Bud Var],[Jan]) := ([Budget],[Jan]) - ([Actual],[Jan]);
([Bud Var],[East]) := ([Actual],[East]) - ([Budget],[East]);

This contains 2 separate calculations. Each tupule contains a different set of the dimensions. A source region basically is a MDX set which superset of all the tupules specified in the calculation script. This is needed as each line in the script is executed in parallel.

There are 3 other optional parameters like CreditMember, DebitMember & Offset. For today's blog post, i will just cover the above 3.

Lets first start with creating a simple calculation script shown below

Basically all this script does is, it tries to assign a value of 100 to the ([Bud Var],[Jan]) tupule. After creating the calc script, save the script in the {ESSBASE_HOME}/app/DemoASO/BasicASO folder where DemoASO->BasicASO is my application & database respectively. Before running this script lets look at the statistics of the ASO cube.

As you see, this cube basically has no incremental slices. All the data is in the main slice. In the case of Block Storage cubes, when we assign a constant value to a member then the constant value gets assigned to all the existing blocks or will create new blocks depending on the CREATEBLOCKONEQ setting in the calculation script. But in the case of Aggregate Storage cubes, we do not have blocks. So it will be interesting to see what happens when we run the above calculation. To find out, lets run this script using the MaxL shown below

execute calculation on database DemoASO.BasicASO with
local script_file "/u01/app/Middleware/user_projects/epmsystem1/EssbaseServer/essbaseserver1/app/DemoASO/BasicASO/BudVar.csc"
POV "Crossjoin({[Jan]},Crossjoin({[New_York]},Crossjoin({[Stereo]},Crossjoin({[Sales]},Descendants(Scenario,Scenario.Levels(0))))))"
SourceRegion "Crossjoin({[Bud Var]},{[Jan]})";

As you see, all we are doing is we are running the calculation script against a POV context containing 4 cells.

The outline of the DemoASO cube is shown below for reference.

If you notice, in the POV we have 4 members from the same dimension (Scenario). And in the calc script we are assigning a value of 100 to a member in the Scenario dimension. Also, if you look at the data(before calculation) in the cube, we have cells populated for 3 of the Scenario members and not for the 4th one (Bud Var)

Now lets look at the data after running the calculation.

Though we assigned a value of only 100 to the Bud Var member, we now have a value of 400. The reason why this is happening is in our POV we have 4 scenario members included. Since we are assigning to value to only one scenario member, all the values of the 4 members get added and will be stored in the Bud Var intersection. Lets take a look at cube statistics again after running this calculation.

This is interesting. What we have now is 1 incremental cell in the incremental slice and the remaining 3 generated cells are in the main slice. So what has happened is, Essbase looks at the POV context and determines the number of cells to be generated. Out of these cells, it looks at the source of these 4 cells. Out of these 4, 3 already exist in the main slice. But 1 cell does not exist at all. So, it generates the new cell in the incremental slice. To validate this theory, lets change the MaxL as shown below and execute it

execute calculation on database DemoASO.BasicASO with
local script_file "/u01/app/Middleware/user_projects/epmsystem1/EssbaseServer/essbaseserver1/app/DemoASO/BasicASO/BudVar.csc"
POV "Crossjoin({[Jan]},Crossjoin({[New_York],[Boston]},Crossjoin({[Stereo]},Crossjoin({[Sales]},Descendants(Scenario,Scenario.Levels(0))))))"
SourceRegion "Crossjoin({[Bud Var]},{[Jan]})";

What we have basically done is, we have added one more member called Boston to the MaxL. Again, if we look at the Cube stats, we will see that there are 2 incremental cells and each cell will be in its own slice.

These 2 incremental cells are for New_York->Bud Var and Boston->Bud Var members which do not exist in the cube at all. The data in the cube will now have spread the value of 400 across both New York and Boston as shown below

In the case of BSO calculations, the calculations are generally driven through the existing blocks & the FIX'ed members (though we can alter this in different ways). In the case of ASO cube though, the control happens through the POV (and also Offset which i have not covered here) & the calc script. ASO generates new cells depending on whether that cell exists or not in the main slice. Of course, i have not tested this when all the input data in an incremental slice, but i would assume the behavior will more or less be the same. So far, i will have to say the performance is really good. I will be putting this through further test with a larger data set. It would be interesting to see if this can replace level-0 currency conversions that are quite common in implementations. So far, the only way to do that in ASO was either by using BSO as a partition source or by using Dynamic MDX hierarchies. I have covered both of them here and here. My first impression is, this can quite easily be used for currency conversions as well. I will hopefully cover my findings in a future blog post on this.