Wednesday, July 20, 2011

ASO vs. BSO Calculations

There are a lot of reasons why Essbase owners should have the majority (or all) of their cubes as ASO now. ASO loads faster, calcs faster, have a smaller footprint, allow for far more dimensions, and a few other things that others have already written a lot about. Prior to the current version of Essbase there were some concerns and it wasn’t quite as user friendly, but the Fusion edition (11.x) has addressed many of those concerns. Now with incremental loads, partial data clears, and ability to write back to level 0 members there is a lot more flexibility.

The reason why I write this today though is because I found another great reason to have ASO cubes instead of BSO. Some have written that BSO cubes allow for more complex calculations, which may be true in some regards. I have found that MDX in ASO cubes provides greater flexibility and allows writing of complex calculations in far few lines of code in a member formula.

For example, we have one BSO cube remaining that we use for planning purposes. A recent report request required a formula be added to the cube. The formula in and of itself is not complex. What is complex is that there are multiple IF scenarios that have to be embedded to ensure that the right calculation takes place for the right period of time. While writing this formula I received the following error message:
I never knew I was limited to a formula size. It makes obvious sense; I had just never run up against the limit before. I ended up having to remove some of my IF scenarios to get the formula down to the appropriate size. It’s not a huge deal as I guess no one is likely looking at the period over period growth rate from 3 years ago, but it was frustrating none the less.

So this got me thinking, we have this same formula in other cubes, ASO cubes. I was curious how they compared because I know it wasn’t as painful to write the ASO version as what I went through writing the BSO version. This is what I found:
  • A fully written BSO formula was 1242 lines and 75,493 bytes
  • A fully written ASO formula was 78 lines and 4,445 bytes
(I won’t bother showing you the 2 formulas as who wants to look at that many lines of code if they don’t have to)

And I think it is interesting to point out the BSO formula was written to calculate at the Month, Quarter, and Year level only. The ASO formula is written to calculate the Week, Quarter, Month, and Year levels. So technically, the ASO formula is doing even more than the BSO formula.

The difference is that in MDX there are some wonderful functions that allow you to select your data more succinctly and apply the desired calculation. I will be the first to admit, MDX isn’t always the easiest to read and sometimes building a complex calculation using multiple MDX functions embedded with each other can be frustrating to put together, but in the end I find that almost always my formula is shorter and therefore easier to maintain.