MDX, Maxl, and Business Rules

In this post, I'm going to write about tweaking MDX and Maxl in business rules, I found this to be quite useful in numerous times mainly involving clearing dynamic sets of data in separate cubes, times where I needed to push data from one cube to another cube on demand, and obviously, I needed to clear the data in the target cube before loading.

As usual, I'm going to simplify the example and talk about the relevant stuff only, once you get the idea you can unleash your inner demon and find a useful application for it, Beleive it or not, I used it to make me a chicken caesar wrap and it was yummy albeit a bit salty!(just don't take my word for it)

I have two cubes, one BSO cube for data entry and another ASO cube for reporting, and I needed to push the data from one to another, it is a normal case every one of us is bound to face at some point in any implementation, but in my case, there was an additional twist as follows; I only need to push the Budget data for specific combination (Entity member(s)) without touching the Base scenarios (Budget, Forecast, Actual..) and the first thing that will come to your mind is to use Entity/Scenario as a prompt/variable and build the logic accordingly, but this not work in my case.

To better understand the dilemma, I'll show below an example of my data intersections:

Clearly, from the sample data shown above, you can see that Entity & Scenario dimensions are sharing the same members for two different use cases, so I need a 3rd dimension to determine the unique intersections for each of the data sets, in this example I have simplified it by showing the Period dimension, but in a real life this can vary.

So in my case the difference is the Period member used, so to clear data for my Budget scenario (Manual) I'll have to delete the Month period members only, having said that let me break down the solution and see where we can have that extra tweak:

   

1. Business rule (exporting the specific data region and calling the Maxl script)

2. Essbase load rules 

3. Maxl script to clear data and load exported file

I'm going to have two business rules (one for manual Budget scenario and another for base scenarios), as for the load rule you can have one generic rule, and finally the Maxl script, you can also have one script with a tweak and I'll show how in a bit.

Below are the business rules I'm using:

1. Base scenarios with BegBalance

2. Budget manual scenario with months

You can see a lot of substitution variables, single and double quotes... which I'm not going to talk about, you can read more about it in my previous post. Now in a nutshell, what I want to do is that based on the scenario, I want to pass to the Maxl script the region I want to clear in my ASO cube, this way I can have one generic Maxl script with flexibility to clear data in non-unique data intersections.

So to get on with it here is my Maxl:

Closer look at my alter statement (important to have at least one space before the semicolon):

Here is the tweak, I'm having a dynamic alter statement in my Maxl script, taking the 6th, 7th, and 8th parameters from either of the business rules calling the script, producing one of the following statements:

First case (base scenarios and BegBalance):

     - 6th parameter: '{([BegBalance],[

     - 7th parameter: E100100200 (Entity run-time prompt)

     - 8th parameter: ])}'

Second case (Budget scenario with months):

     - 6th parameter: '{CrossJoin([YearTotal].Children,{[

     - 7th parameter: E100100200 (Entity run-time prompt)

     - 8th parameter: ]})}'

It works perfectly, here is the log:

Voila!

May the Cosmos be with you!