I first started reading about ASO procedural calculations around 2011. I gave them a try in early 2012 and failed miserably. The documentation was difficult to understand and they just didn't seem to work for me. I had heard similar things from other consultants at the time so I gave up.
I started working for a large retailer who had a BSO cube with a 3,000 line calc script for running allocations. The BSO cubes (they were partitioned so there was one for each year) first needed to be aggregated, then the allocations were run, and finally the cubes were aggregated again. Total batch time was about four hours and it was killing us. This was a mission critical cube and it wasn't available until 10AM or later some days. So I started to investigate ASO procedural allocations again. I was encouraged by my manager at the time to try them out on this beast. It took a couple of weeks but I got to the point where I had a proof of concept and worked through most of the technical hurdles -- dealing with dynamic and shared members were among the most challenging. Then it was just a matter of spending a couple of weeks heads down translating the calculations from BSO to ASO. I used "Execute Allocation" and "Execute Calculation" commands. I've blogged about these in the past and have presented at a few Kscope conferences on how to use these.
After the dust settled I had taken a four hour batch process across several BSO cubes and moved it to a single ASO cube that took 10 minutes to run from start to finish. On top of that, the users could get these allocations run during the day in less than two minutes. I've been around Essbase 21 years and I have never seen such performance gains. The users were nonplussed.
I'm disappointed when I hear people who are hesitant to try using ASO. I've worked in this space a long time and I've gotten to the point where there is almost no use case where I'd choose BSO over ASO. ASO is so much more scalable. You just need to learn MDX.
The MDX Insert statement makes life so much simpler. You can use the same language against an ASO and BSO cube. You don't have to worry about dealing with dynamic and shared members the way you did in Execute Calculation and Execute Allocation scripts. You can run them in Smart View. You can write complex calculations. You can write simple calculations. In my initial performance tests, I saw a 30% improvement compared to Execute Calculation and Execute Allocation. The real test, which I intend to do, is to compare BSO and ASO procedural calc performance.
Post a Comment