Essbase – Doing allocation using COUNT function ( Ripping into internal mechanics of @ALLOCATE)

In this blog, I would be talking about doing allocation using the COUNT function in an Essbase calculation script. This is at a bare basic level of how Essbase does allocation using the @ALLOCATE function. Please note that this is not the only way to do allocation in Essbase. The best part of Essbase is that it gives you nuts and bolts and you can choose how to design a business logic in whatever way suits you.

So let us start with the allocation. The below snapshot shows the Entity hierarchy that I have created. I will be allocating data from Country level to the base level of entities. 

The Entity hierarchy with “Country” dimension is as shown below… For the demo, I have moved the Country member out of Rollup since I do not want the data rolling up to the Input member.   

The next snapshot shows the data that we want to allocate using the Count function i.e. I want to divide 174303 to the child entities using some business logic.  

The next snapshot shows the calculation script that I designed to do the allocation. I am basically calculating ACT as Actuals at Country/No of level zero descendants of Entity excluding Country member. 

In order to get the number of level zero descendants of the Entity dimension, I have used the COUNT function. Observe that I have used a SKIPNONE flag so that no level zero entities are skipped. (In another example later I will show how to do allocation to only a specific subset of members)

The data after running the allocation script is as shown in the next snapshot. I have used a report script to validate the data. 

The numbers pushed into an excel sheet for verification and validation is shown in the next couple of snapshots.

Now, let us see how to do a data allocation to a very specific subset of data in Essbase using the same function. This is a trial and error run so there are multiple iterations of the run. Suppose that I want to run an allocation only on the descendants of East i.e. E-A, E-B and E-C.

I created a member called PLSplit and put a Boolean value of 1 as shown in the next snapshot. Now, I want to do an allocation only of PLSplit so I put a value of 1 at E-A, E-B and E-C.

I now updated the calculation script as shown in the below snapshot.

Observe that instead of SKIPNONE, I have used the SKIPMISSING flag in the function. This flag is used when I don’t want to consider members without data in the calculation.

On running the calculation script, it gave me an output as shown in the below snapshot.

 

Although the division is working correctly, it is not what I want since I need to get the data only at E-A, E-B and E-C members. The issue is that there is no restriction on which members to choose.

There are two ways to fix this. Either alter the FIX or put an IF condition. I keep the FIX as is and add an IF condition.

To fix this issue, I deleted the PLSplit member and instead put a value of 1 for the entity where I want to run the allocation.

Since, I want to run allocation for PL1510, I put 1 at E-A, E-B and E-C members. Remaining all are #Missing.

I updated the calculation script with an IF condition as shown below.

On running the above calculation script, I find that the allocation has been done properly as expected using the COUNT function as shown in the next snapshot.   

As a side, this is a very basic implementation of how the @ALLOCATE function works internally. Will cover more on this later. But the spread methodology remains the same…