MDX Insert Part One

This is important. Really, really, really important. If you’re using Essbase in the cloud you now have the capability of writing MDX Insert statements. (You'll get this capability on-prem with the 19c release) You can write these against both ASO and BSO cubes. MDX inserts are essentially calc scripts. I’m going to spend some time blogging about them in the future weeks and months. I’ll start out with a brief introduction today.

Let’s start with a history of procedural calculations in ASO.

Initially

When ASO was introduced it wasn't good for much. You were really limited in what you could use it for. Basically loading a large amount of data and adding it up. You could add some formulas but they were slow and couldn't be added outside of certain dimensions. If you wanted a procedural calculation done, you needed to make sure that was done before the data was loaded.

And then

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.

And then

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.

And finally

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.

  

This is the next big step for Essbase. It makes it a more mature and "database-like" technology. Essbase now resides within the database group at Oracle. This is starting to make sense. Moving toward a more standard, less proprietary language is definitely a move in the right direction.

And next

I'm going to cover several examples using MDX in the coming weeks. I'll start off simply and build on that. If you have any specific uses cases you're curious about, send them my way and I'll do my best to tackle them.