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.