Oracle EPM 11.1.2 - Essbase Calculations on ASO Cubes - Part 1

One of the significant new features of EPM 11.1.2 release, is the ability to write custom calculation scripts for calculating & allocating data to level-0 members in an ASO Cube. Till now, in many cases/implementations, BSO option had to be used in some form or the other due to the fact that ASO did not support custom calculations. Now with EPM 11.1.2, we can do these calculations even on a ASO cube (but only for level-0 members). There are 2 ways of writing & executing calculations

Using Calculation Manager to design the calcs and executing from there
Using MaxL and executing the scripts through MaxL

The second option above is actually more flexible since it allows to write custom MDX Tuples, sets easily within the calc scripts. I somehow felt that the calc manager has undergone a significant UI change primarily due to the introduction of ADF based UI. Though it does work, there still are some UI bugs which crept up here and there especially while trying to enter the calculations manually. One significant difference between calculation manager and MaxL is the fact that Calculation Manager follows more of a BSO based member set entries. It does not support MDX, atleast as far as i have tested. MDX is supported only within the calc scripts that run through EAS.

Lets look at a very simple example using both the above options. I shall be using an ASO version of the Demo->Basic cube to demonstrate this

Essbase ASO calculation execution now follows more of a HBR/Calc Manager syntax. That is, we start with defining a calculation script and then during execution time, we can specify the Point Of View of Execution, Member Offset, Credit/Debit members etc. A single calculation script can be executed in different point of views thereby providing different results. ASO calc scripts cannot be executed in isolation directly within EAS. They will have to be executed from within the maxL script editor. Also, the calculation script editor in EAS, does not support MDX. So for now, we will have to ensure that the scripts are absolutely correct as there is no way we can check the syntax validity from within the UI. I believe there is a bug in the EAS console, wherein if you get the MDX set scripts wrong, it will bring down the EAS server (atleast on my 64 bit console).

Lets start with logging into the calculation manager. Though IE 8 is supported, the latest version of Firefox is not supported for any of the ADF based EPM UI(similar to EPM 11.1.1.3). I had to downgrade my Mac firefox to a lower version to get this working.

I will start with creating a very simple rule called Budget Variance.

You will see that now the calc manager has ASO specific icons like Allocation, Calculation, Point Of View etc for executing ASO calculations. As with any calc manager calculation, we can specify a global range and then execute calculations.

With the introduction of ADF components, we can now drag and drop the individual components into the main palette. I will start with defining the Point of View first. I want the calculation to execute for the entire level-0 member combinations of the cube.

After defining the Point of View, lets drag and drop the calculation(Formula) on to the main palette. The calculation will be defined after the Point of View(or before depending on how we want the POV to be applied).

In the formula, lets define a simple calculation where we would like the Bud Var member to be having a value of 100.

We can have more than one formula defined in a calculation. But the difference between a ASO and a BSO calc script is the fact that the formulas are executed in parallel in ASO. We can serialize them by defining a proper flow within the calc manager. Lets now deploy this rule and execute it.

After execution, the cells within the ASO cube will be updated as shown below.

So, this does work & seems to work even for some complex cases within the calc manager. Lets put this to test further by using EAS & MaxL in the next blog entry. I have not tested what happens to the load buffers, data slices etc when we run these calcs. I will hopefully cover those as well as part of the EAS/MaxL based ASO calculation execution blog entry.