Summing Calculated Members in ASO

One challenge I see on a regular basis on Essbase technical forums is the summing of rate driven calculations. Usually the poster wants to carry out a rate driven calculation at the lowest level of the cube and have it summed up. In BSO and HSO this isn't an issue. You simply create a member formula and calculate it or write a calc script. Let's look at this problem from an ASO perspective.

Assume we are planning a mission to Mars and we are tasked with building a robot to explore the planet. We want to test our various robots on a challenging obstacle course. We can measure the time the robot motors are moving and the rate at which they move but the robot extract file doesn't compute the distance it traveled. We'll handle this in our robot Essbase cube. Let's start with two dimensions: Robots and Measures.

First Try

If you remember back to high school algebra, the equation for distance is Rate * Time. We can put this into our Distance member and Essbase will take care of everything for us. We'll just send some data to the cube and let ASO take care of the rest.

So far so good. Now we'll let ASO handle the calculation for us.

Perfect. Now we have our distances computed. Let's just tally those up for all the robots.

Hold on there a second. 12 + 2.5 + 6 + 48 does not equal 211.5. How could Essbase be wrong? Well, it's not. Essbase is multiplying 4.5 by 47. Okay, we'll just change around our solve orders to fix it. Um, actually that won't do anything. We can change solve orders until we're blue in the face and it won't give us the answer we're looking for (which is 68.5)

Second Try

I'm going to try writing a fancy MDX member formula now. I'll just check to see when the Robot member is at the level zero and perform the computation there. At the other levels, I'll sum the robots up.

1:  IIF(IsLevel([Robots].CurrentMember,0),  
2:    [Rate] * [Time],  
3:    Sum(CrossJoin({[Distance]},{[Robots].CurrentMember.Levels(0).Members}))  
4:  )  

Let's see what I get.

Now I have the correct distance number. I don't like the Rates being added up but at least I have my Distance number.

Third Try

I personally don't ever remember seeing a two dimension cube in a production environment so my guess is that most people tackling this problem are designing databases with several dimensions. Let's add a Dates dimension and see how our solution works.

1:  IIF(IsLevel([Robots].CurrentMember,0) AND IsLevel([Dates].CurrentMember,0),  
2:    [Rate] * [Time],  
3:    Sum(  
4:        CrossJoin({[Dates].CurrentMember.Levels(0).Members},  
5:          CrossJoin({[Distance]},{[Robots].CurrentMember.Levels(0).Members})  
6:        )  
7:     )  
8:  )  

Hmm. Things are getting more complicated. Let's see what Essbase comes back with.

That's not right. I'm not getting the right distance when I add up all of the dates. I'll have to go back and change the formula. At this point I think I'm ready to throw this idea out as it is far too complex. Maintenance would be difficult and if you think about how this works in Essbase, everything is getting calculated dynamically as the user requests it. Potentially a lot of complex calculations will have to take place and performance will almost certainly be poor.

Final Try

I can try using an ASO Procedural Calc or Allocation. This is an example of a simple allocation script that will perform the calculation. Once this calculation is complete, the retrievals will be super fast as the Distance data will be stored.

MAXL> execute allocation process on database Mr.Robot with
   2> pov "CrossJoin([Robots].Levels(0).Members,
   3> [Dates].Levels(0).Members)"
   4> amount "([Rate]) * ([Time])"
   5> target ""
   6> range "{[Distance]}"
   7> spread;

 OK/INFO - 1300006 - Essbase generated [12] cells.
 OK/INFO - 1013374 - The elapsed time of the allocation is [0.01] seconds.
 OK/INFO - 1241188 - ASO Allocation Completed on Database ['Mr'.'Robot'].

And here are the results. The distance data now totals correctly across Robots and Dates.

Another Idea

If you're loading from SQL or another source system -- get it to do the calculation and add a column with the correct value before you load.

ASO Allocations -- Performing a Simple Allocation

If you need to translate BSO calc scripts over to ASO procedural calcs/allocations, the Oracle documentation isn't a lot of help. There is no one for one translation table and the Execute Allocation command has so many options that it's difficult to know where to begin. It does take a while to read through and understand. If you've struggled through it before, hopefully this post will help. I'm going to take a simple BSO allocation and translate it for you, explaining things along the way.

Let's assume we have an outline that looks like the one below. This is based off of Sample.Basic, with a few minor adjustments.

Assume we had an allocation from our old Sample Basic cube which was taking last year's Marketing spend and spreading it to this year's Marking budget based upon last year's sales by market. You can see how the data looks below. In this case the $843 gets spread to the various markets in direct proportion to what the market's sales were.

The Old Way

The BSO calc script to do this could use the @ALLOCATE function as below.

1:  FIX("Marketing")  
2:     "Budget" = @ALLOCATE("PY Actual, @LEVMBRS("Market",0),"PY Actual"->"Sales",,share);  
3:  ENDFIX  

Translating to ASO

The most basic difference between a BSO Calc Script and an ASO Allocation is that the former is a file that can be executed in a number of ways: via MaxL, through Smart View, through EAS, etc. and the latter is a MaxL statement. This is the process for creating my MaxL statement.

The first thing I need to do is to start my execute statement specifying the database name.

1:  Execute Allocation Process on Database ASample.Basic with  

Next I need to define my POV. I use the CrossJoin function to define the Range the allocation will be run on. In this case all Level 0 products and all 12 months.

2:  POV "CrossJoin([Jan]:[Dec],  
3:  {Descendants([Total Products],Levels([Product],0))})"  

Now I need to set the AMOUNT I am using in the allocation. In this case it is the Marketing dollars from last year for all Markets. That can be represented using a tuple.

4:  AMOUNT "([Market],[PY Actual],[Marketing])"  

Then I will set the BASIS by which the data will be spread. In this case the PY Actuals, Sales tuple.

5:  BASIS "([PY Actual],[Sales])"  

I will define my TARGET with a tuple pulling data from the intersection of the Budget Scenario and Marketing Measure.

6:  TARGET "([Budget],[Marketing])"  

Next I will define my RANGE as a set made up of the Level 0 members under Total Markets. This is the group of members to which the data will be allocated.

7:  RANGE "{Descendants([Total Markets],Levels([Market],0))}"

Finally I will specify the Allocation method. In this case I will use SHARE. You can use SPREAD if you want to allocate the data values evenly. If you do use SPREAD then make sure to drop the BASIS (simply remove line 5 from the statement) as it doesn't make sense to use a BASIS when allocating evenly.

8:  SHARE;  

Hopefully when you execute, you'll see the following messages telling you the elapsed time and how many cells were created.

 OK/INFO - 1300006 - Essbase generated [5] cells.
 OK/INFO - 1013374 - The elapsed time of the allocation is [0.13] seconds.
 OK/INFO - 1241188 - ASO Allocation Completed on Database ['ASample'.'Basic'].

Just a Start

There are many other options available for use in more complex allocations. This example will get you started. I'll work on some posts involving more complicated allocations in the future.