Technical

Oracle EPM 11.1.2 - Allocations in Essbase ASO Cubes - Using MaxL scripts

Venkatakrishnan J

• 3 min read

In the last 2 blog entries, i showed how the calculations in ASO work. Today lets look at another new feature in ASO cubes i.e the ability to run allocations directly. In a BSO cube, Allocations and Calculations are all driven through a set of calculation commands. Some Allocation features in BSO can actually be implemented using normal calculation commands (without using ALLOCATE and MDALLOCATE functions). But in the case of ASO, calculations and allocations are treated separately. So, there are 2 important aspects that we need to remember while running calculations/allocations in a ASO cube

  1. Dynamic References using CurrentMember, CurrentTupule etc are not supported currently (necessary if we want to run multiple allocations within the same dimension)
  2. Hierarchical references like PARENT etc are not allowed in both calculations & allocations. These references are allowed for the parameters but not for the assignment scripts/allocation amounts.

The above 2 are significant drawbacks which i hope will be supported in future releases. Lets now look at a very simple example for allocation. I will be using the ASO version of the Demo->Basic cube for demonstrating this. The screenshot below shows the data that has been loaded into the cube.

Picture 1

If you notice, we have data for the Sales measure for all Regions except South. The idea is to allocate data into the South Region (both Sales & Cogs equally) based on the Total Expense values of the East Region. This is a very simple allocation example where our input data is at Level-0 but we are allocating from non Level-0. One good thing about this example is the fact that this shows that though ASO does not support input to non level-0 members, it supports allocation from them.

An Allocation in a ASO cube does not require a separate calculation script. All allocations are done through MaxLs directly. An allocation MaxL contains four main parts (in addition to other parameters which i shall not cover here).

  1. POV - POV or a Point of View provides execution context to an allocation. A POV allows only reference to level-0 members. For every combination in the POV, the allocation will be executed once. POV is specified in the form of a MDX-Set.

  2. Amount - This can contain a static member, Tupule or a constant. This does not support MDX expressions. In our example above, the amount will the (Total Expenses,East) Tupule. Ideally, Amount & POV should have all the dimensions in the cube.

  3. Target - Target is specified in the form of a MDX Tupule. Basically it represents the region that will act as a target for the allocation. In our case, this will be empty.

  4. Region - Region is a MDX set expression. This represents the target region where the data will be allocated. The dimensions specified in POV, Target and Region should be mutually exclusive as they all combine to form the target region. In the example above, Children(Total Expenses) & the Children (South) will form the Region.

  5. Basis - This is commonly used when we are doing member based allocations. In our case, since we are using dividing the values equally (Spread allocation), this is optional.

So the final formula that i shall be using for achieving this is given below

execute allocation process on database DemoASO.Basic with
pov "Crossjoin(Descendants([Year],[Year].levels(0)),
Crossjoin(Descendants([Product],[Product].levels(0)),
Descendants([Scenario],[Scenario].levels(0))))"
amount    "([East],[Total_Expenses])"
target    ""
range        "CrossJoin({[Sales],[Cost_of_Goods_Sold]},
Descendants([South],[Market].levels(0)))"
spread;

Picture 2

As you see, Essbase has generated the necessary cells to accomplish this. As i mentioned last time in the Calculation blog post, the allocations/calculations are done separately and then the updated cells are pushed through external data load buffers. So, you would start noticing cells being loaded into incremental slices.

Picture 3

And if you look at the data, the South region would now have data with the values uniformly distributed.

Picture 4

Though this does work there are some inherent drawbacks. As ASO does not accept data at non level-0 members, the way allocation maxL's are designed it makes it very tough(though possible) to do hierarchical allocations within the same dimension. Hopefully the introduction of dynamic member references and member functions should enable that in the future.

Sign up for more like this.

Enter your email
Subscribe