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.

Matching on MDX Monday

According to the Essbase Tech Ref, there is no equivalent MDX function for the @MATCH function. However, there is a function called IsMatch() which can be combined with Filter() to give equivalent results. Maybe the documentation hasn't been updated in a while. Anyway, let's sort it all out here. IsMatch() uses regular expressions which are really powerful. You can read the specification that Essbase uses here.

Let's try to reproduce the three examples given in the Tech Ref for @MATCH. It's not mentioned, but these are obviously being taken from Sample.Basic.

@MATCH(Product, "???-10")

1:  SELECT  
2:  {} on AXIS(0),  
3:  Filter(Product.Members,   
4:    IsMatch(Product.CurrentMember.MEMBER_NAME, "^[0-9]{3}-10$")  
5:   )  
6:  ON AXIS(1)  
7:  FROM Sample.Basic;   

OR

1:  SELECT  
2:  {} on AXIS(0),  
3:  Filter(Product.Members,   
4:    IsMatch(Product.CurrentMember.MEMBER_NAME, "^[A-Z0-9]{3}-10$")  
5:   )  
6:  ON AXIS(1)  
7:  FROM Sample.Basic;   

In this case I'm using a few special characters to ask for 3 characters followed by "-10". The first character is the ^ which forces the regular expression to start at the beginning of a word. In the first example I have [0-9] which asks the engine to match any digit. In the second example I have [A-Z0-9] which will match any alpha character or digit. Next comes {3} which tells the engine to match the preceding statement three times. So the [0-9] or the [A-Z0-9] will need to be matched exactly three times. Next I add "-10" which will need to match that text exactly. Finally, I added the $ which matches the end of the line. In this case either example works because we only have products with numbers in the first three characters. The second example would match a product like "abc-10" as well.

@MATCH(Year, "J*")

1:  SELECT  
2:  {} on AXIS(0),  
3:  Filter(Year.Members,   
4:    IsMatch(Year.CurrentMember.MEMBER_ALIAS, "^J")  
5:   )  
6:  ON AXIS(1)  
7:  FROM Sample.Basic;   

This is a fairly straight forward regular expression. I'm using the ^ to specify that

the string needs only to start with a J. One other difference here is that I'm using

the MEMBER_ALIAS property for Year to do the matching.

@MATCH(Product,"C*") 

1:  SELECT   
2:  {} on AXIS(0),   
3:  Filter(Product.Members,    
4:   IsMatch(Product.CurrentMember.MEMBER_ALIAS, "^C")  
5:  )   
6:  ON AXIS(1)   
7:  FROM Sample.Basic;    

This is basically the same request as the last one. Grab all of the Products whose aliases start with C.

Conclusion

With the ability to use regular expressions, the IsMatch() function is far more powerful than @MATCH. If you come up with some challenges for the regular expression engine, feel free to pass them along.

Corn

Friday is finally here again and I'm ready to enjoy another fall weekend. Here in the Midwest, the corn is beginning to be harvested. Soon huge machines will be going through the fields scooping up the stalks and spitting out the dried kernels that go into so much of the food that we eat. All of this corn around here reminds me of a story James Bender told in his book How to Talk Well.

He told of a farmer who entered his corn in the state fair and won a blue ribbon each year. When a reporter asked him about how he grew it, the reporter discovered something unexpected. The farmer shared his best quality seed with his neighbors. The reporter was surprised and asked how he could afford to share his best seed with the very neighbors he was competing against each year. The farmer responded, "The wind picks up the pollen from the ripening corn and whirls it from field to field. If my neighbors grow inferior corn, cross-pollination will steadily degrade the quality of my corn. If I am to grow good corn, I must help my neighbors grow good corn."

And so it is with technology, in this case, Essbase. If nobody shared their knowledge of Essbase 15 or 20 years ago, the product might have died. But conferences, user groups, and forums encouraged people to share their good Essbase. When our neighbors succeed, we also, in a small way, succeed. The open source movement is the perfect illustration of the power that groups are capable of. This week it was discovered that Microsoft is using Linux to run their cloud services. What once was an April Fool's joke has become reality.