MDX Insert Part Two

In Part One of this series I explained why I think MDX Insert is one of the most important things to happen to Essbase in a while. Now I'm going to show you how to use it. I hope to build from very simple, to very complex over the coming weeks. If you have interesting use cases, send them my way. The more input I get, the more interesting my examples will be.

Keep in mind that you need to be using Essbase in OAC for this to work. It will not work in Essbase 11.1.2.4 or prior.

Documentation

As far as I can tell this is the sum total of documentation on MDX Insert from Oracle:

Tech Reference Spec
MDXINSERTREQUESTTIMEOUT - sets the timeout for an MDX Insert statement
MDXINSERTBUFFERAGGMETHOD - sets whether to add same intersection values or load the last one calculated. Note that it doesn't say if this is an ASO only command so I will assume it isn't until I test it out myself.
Blog One
Blog Two

I'd like to point out that I haven't tried playing around the two essbase.cfg settings above. In OAC it seems you set config settings at an application level and these two settings are not available in the interface (as of 7/14/2019). You can telnet into the OAC server and update the essbase.cfg file. I plan to do that at some point.

Starting Simply

The syntax for MDX Insert is as follows:

INSERT

    source tuple TO target tuple

INTO

    App.Db

FROM

    (

       MDX Select statement goes here

    )

From a high level point of view

1. You are writing an MDX select statement.
2. You're using that to write data to another cell.

Without using an MDX With section, the MDX Insert is only good for copying data from one place to another in a database.

First Example

This example is from the ASOSamp.Basic cube which is available for loading from the Application Gallery in OAC. (If you want to load the sample data you will need to copy that from the gallery into the ASOSamp.Basic file folder then perform a load job -- no load rule is necessary.)

In the MDX script editor in the OAC interface I created the following script.

Here I'm selecting a single cell specified by the following tuple on the rows:
([13118],[056098],[Digital Cameras],[Under 20,000],[20 to 25 Years],[No Promotion],[Cash],[Sale],[Jan],[Original Price])

On the columns I'm selecting [Curr Year]. The column tuple seems to be the source or left side of the equation. I map this into [Prev Year] in the INSERT section.

I can run this query a number of different ways.
1. As a job in the OAC web interface.
2. From Smart View under Manage & Execute Queries. Note that it does not show you the scripts available from ASO cubes. You'd need to copy and paste the MDX Insert statement into the query editor. What's interesting is that if you had created this MDX Insert statement in a BSO cube, Smart View would recognize it as a calc script. So if you were to click the Calculate button, it would show you the classic Essbase calc scripts AND any MDX Insert scripts you had created on the server. I think we need this functionality for ASO.
3. MaxL
4. I assume Rest API (though I haven't tested it)
I don't see a way of using the CLI to run this.

Here's the before:

And the after:

What Does Not Work

From One Into Another

The way the syntax is written, it looks like you should be able to specify different databases for the INTO and SELECT statements. I tried. Sadly it didn't work. I did mention this to someone at Oracle who told me there weren't any immediate plans to add this functionality. It would be pretty cool though. Imagine if you couldn't do that in SQL?

INSERT #MISSING
If I switch the MDX Insert statement above so that my SELECT has [Prev Year] and the INSERT section has "([Prev Year])" TO "([Curr Year])" when Previous Year values, the Curr Year value will not be overwritten. This is the same behavior you would see when using Execute Allocation or Execute Calculation commands with an ASO cube. A load of #MISSING into an ASO cube will not overwrite an existing value. In the past I've used logical clear statements ahead of ASO procedural calcs when I though there might be an issue with overwriting values. For example, if Actuals data needs to overwrite Forecast data.

Under the Covers

I assume what is happening here is that the SELECT statement is generating a dataset. That dataset is then being loaded into the cube as a new slice. When I ran this calc an incremental slice was created. This is what the documentation says on the matter:

During execution of the MDX Insert query, an output buffer is created in memory which accumulates with values until the query is completed.

Appendix

MDX Script for easy copy and paste:

 INSERT "([Curr Year])" TO "([Prev Year])"  
 INTO [ASOSamp].[Basic]  
 FROM (  
   SELECT  
   {[Curr Year]} on COLUMNS,  
   {([13118],[056098],[Digital Cameras],[Under 20,000],[20 to 25 Years],  
    [No Promotion],[Cash],[Sale],[Jan],[Original Price])} on ROWS  
   FROM ASOSamp.Basic  
 )  

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.

Kscope19 Reflections

I wanted to take a few minutes to reflect back on what was an exciting, invigorating, and exhausting Kscope19. It started for me on Saturday morning with a trip to the coolest museum on the planet where I programmed an Altair and ended with an Applied OLAP team lunch on Thursday afternoon. I barely had a chance to sleep in between. I'm not going to get into the details on any one thing but want to cover a few important topics that make Kscope such a great conference.

People
This is by far the best aspect of the Kscope conferences. It was great to be a part of the Applied OLAP team this year. We had 10 attendees! Putting faces to names, seeing people I hadn't seen in years (in one case 15 years!) was a real joy for me. The nightly activities including Applied OLAP's night at the Seattle Aquarium and the Wednesday night MoPOP event and after party were a total blast.

Technical Content
Being able to interact and listen to the brightest minds in our field can be done nowhere else. I got a chance to speak with Oracle product management about new features in Essbase. I was able to present on BSO Solve Order and the new MDX Insert statement. These are the kinds of things that get me really fired up. I love helping to push things forward. I was also able to present a demonstration of Essbase parallel queries in our Dodeca product at our Sunday Symposium. This could be a game changing technology allowing multiple Essbase queries to be run by a single user simultaneously. I'll be posting more about this in the future.

Personal Growth
The fact is that presenting at a conference can be difficult. I tend to over prepare which hopefully benefits the people sitting in my presentations as well as me. Preparing a presentation takes a lot of time and hard work. It forces me to be better. I encourage others to try it if they haven't done it before. It is a very valuable skill to have. I'm already starting the process of thinking about abstract submissions for Kscope20. I will see everyone in Boston next year!