Uncategorized

Times are changing and so are Essbase features. Are you ready to embrace the idea of ASO becoming a budgeting application? For many years, BSO has really dominated this front especially in Planning applications.

 

Why would I even want to use ASO if BSO can perform allocations?

The answer is simple. You can enjoy the versatility of utilizing more than 8 dimensions in an ASO application with the allocation facilities that we need to emulate a BSO application.

 

What are some of the limitations of ASO Allocations?

For now we are limited to using Maxl as the only language available. You can only write your allocation values to level 0 members. This is not very surprising considering that is how ASO operates.

 

Can ASO perform complex allocations?

Maxl allocation syntax is pretty constrictive; however, we can make use of Essbase member formulas within the outline to leverage more complex allocations.

 

Can you show me a live scenario?

We here at Accelerant Software are delighted to share our knowledge with the community.

Consider the following scenario:

 

screen1

 

We have a $20 marketing overhead all for audio products for the month of Jun in New York that needs to be allocated back to the various audio products. These products include Stereo and Compact Disk. We want to allocate them by Marketing expense ratio by each product. Of course we also don’t want any allocated amount to go back to the overhead member (Audio_Overhead).

 

In order to solve this problem in ASO, we created a Scenario member called Allocation Scenario. This member would receive all allocation; thereby, preserving the data integrity of the rest of the members.

Take a look at the outline structure:

screen2

 

Based on the business requirement, and the outline condition, this is how we would want our report to present:

screen3

screen4

When this code is executed into the Maxl editor in Essbase Administrative Services, the allocation is performed and we get our desired results within the same excel sheet when we refresh.

screen5

 

Now let’s break this code down:

 

execute allocation process on database SampASO.Basic with

This line is pretty straight-foreword. You only need to define the Application.Database in your case.

 

pov “Descendants([New_York],[Market].levels(0))”

This line determines the point of view of the allocation. You allocation will be written to this set of cells.

 

amount    “([Marketing], [Jan], [Actual], [Audio_Overhead])”

This line determines the amount that will be allocated to your target cells. Amount can be an upper level tuple as well as it just reads in a data value.

 

target    “([Marketing], [Jan], [AllocationScenario])”

This line determines a target tuple to which the amount is allocated too. It goes hand in hand with the POV and the Range and dimensions can’t be duplicated in these parameters.

 

range      “Except ({Descendants([Audio],[Product].levels(0))}, {([Audio_Overhead])})”

 

The range specifies a set of cells in conjunction with the target to write the values to. Dimensions mentioned in the range can’t clash with the target or the basis. In our MDX Except function, we are removing Audio_Overhead from our range set because we do not want to allocate to our overhead (That would be ridiculous).

 

basis  “([Actual], [Marketing], [Jan], [New_York])”

The basis is a tuple that determines how the allocation occurs. This is determined by obtaining a ratio of the specific basis cell divide by the total. In our excel example, C6/(C8-C5). We are subtracting the overhead in this excel formula because we excluded it from our range as previously described in our MDX Except clause.

share;

Finally, we specify share because we used a basis clause. ‘Share’, distributes the amount in conjunction with a basis clause. If we had used ‘Spread’ instead, the basis would not be needed and the allocation would be distributed evenly among our product families.