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  
 )