How to perform allocation with MDX INSERT

What if I told you MDX INSERT can be used for allocations

Unfortunately, no one can be told what the MDX INSERT is. You have to try it for yourself. You take the blue pill, the story ends, you wake up in your bed and do allocations with calc scripts. You take the red pill, you stay in MDX Wonderland and I show you how deep the rabbit hole goes.



Let's build Sample.Basic cube. Click on "Import", then "Catalog". Navigate to gallery > Cubes > Genral and select Sample_Basic.xlsx. Click Select then OK and wait until the cube is ready.

To illustrate allocation we should make some simple modification the outline. Add a new stored member under "Scenario" dimension; let's call it "Scenario 1".

  Calculate your cube by starting "Run Calculation" job for Sample.Basic

Check that you can navigate to the following grid in Smart View

Let's enter 10,000 into D27 which corresponds to (Sales, Jan, Scenario 1, East, Colas). Our goal would be to allocate these 10,000 to all the level 0 descendants of East and Colas. We want to allocate it based on the proportional share of the actual values.

Before we will write the allocation script, let's try to build a simple MDX SELECT statement that returns allocated numbers.

First we need to calculate the ratio of  each level 0 combination to the corresponding aggregated value. This should be simple -- ([Sales] / ([Sales], [East], [Colas]). Now we should multiply our allocated value by the ratio -- ([Scenario 1], [Sales], [East], [Colas]) * ([Sales] / ([Sales], [East], [Colas]). Finlay we round the allocated value. The final formula for the allocated value would be:

Round(([Scenario 1], [Sales], [East], [Colas]) * ([Sales] / ([Sales], [East], [Colas])), 2)

Next we would like to perform such a calculation across level 0 descendants of East and Colas

CrossJoin(Descendants([East], Market.levels(0), SELF), Descendants([Colas], Product.levels(0), SELF))

Now combining these two parts into a single MDX query we will get

WITH
  SET [_MARKET]      AS 'Descendants([East], Market.levels(0), SELF)'
  SET [_PRODUCT]     AS 'Descendants([Colas], Product.levels(0), SELF)'
  SET [_ALLOCATE_ME] AS 'CrossJoin([_MARKET], [_PRODUCT])'
  MEMBER Measures.[_AllocatedVal] AS
          'Round(([Scenario 1], [Sales], [East], [Colas]) *
                  ([Sales] / ([Sales], [East], [Colas])), 2)'
SELECT
  {[Sales], Measures.[_AllocatedVal]} ON COLUMNS,
  [_ALLOCATE_ME] ON ROWS
FROM Sample.Basic
WHERE ([Actual], [Jan])

Let's execute the query using Analyze UI

OK, so we managed to query the allocated values, now we need to store it. This is where MDX INSERT comes into the picture. The allocated values are coming as (_AllocatedVal, Actual) and we need to insert it into ([Scenario 1], [Sales]).

WITH
  SET [_MARKET]      AS 'Descendants([East], Market.levels(0), SELF)'
  SET [_PRODUCT]     AS 'Descendants([Colas], Product.levels(0), SELF)'
  SET [_ALLOCATE_ME] AS 'CrossJoin([_MARKET], [_PRODUCT])'
  MEMBER Measures.[_AllocatedVal] AS
          'Round(([Scenario 1], [Sales], [East], [Colas]) *
                 ([Sales] / ([Sales], [East], [Colas])), 2)'
INSERT
  "(Measures.[_AllocatedVal])" TO "([Scenario 1], [Sales])"
INTO Sample.Basic
FROM (SELECT
  {[Sales], Measures.[_AllocatedVal]} ON COLUMNS,
  [_ALLOCATE_ME] ON ROWS
FROM Sample.Basic
WHERE ([Actual], [Jan]))

Create the MDX script, execute "Run MDX" job and query the cube again

As you can see the values are  allocated and stored. It is easy to modify the script to work on a completely different data slice and I will leave this exercise to the readers, but what if I want to run my script from Smart View and execute it only for a slice of data that I'm currently working on. Apparently it is easy. Essbase supports Run Time Substitution Variables with MDX scripts. Add RTSVs to your scripts and replace specific Market, Product and Year values with the variables.

SET RUNTIMESUBVARS
{
  Market = POV <RTSV_HINT><svLaunch>
                       <description>Market</description>
                       <type>member</type>
                       <allowMissing>false</allowMissing>
                       <dimension>Market</dimension>
                       <choice>Single</choice>
                       </svLaunch></RTSV_HINT>;
  Product = POV <RTSV_HINT><svLaunch>
                   <description>Product</description>
                   <type>member</type>
                   <allowMissing>false</allowMissing>
                   <dimension>Product</dimension>
                   <choice>single</choice>
                   </svLaunch></RTSV_HINT>;
  Year = POV <RTSV_HINT><svLaunch>
                   <description>Period</description>
                   <type>member</type>
                   <allowMissing>false</allowMissing>
                   <dimension>Year</dimension>
                   <choice>single</choice>
                   </svLaunch></RTSV_HINT>;          
};

WITH
  SET [_MARKET] AS 'Descendants(&Market, Market.levels(0), SELF)'
  SET [_PRODUCT] AS 'Descendants(&Product, Product.levels(0), SELF)'
  SET [_ALLOCATE_ME] AS 'CrossJoin([_MARKET], [_PRODUCT])'
  MEMBER Measures.[_AllocatedVal] AS 'Round(([Scenario 1], [Sales], &Market, &Product) * ([Sales] / ([Sales], &Market, &Product)), 2)'
INSERT
  "(Measures.[_AllocatedVal])" TO "([Scenario 1], [Sales])"
INTO Sample.Basic
FROM (SELECT
  {[Sales], Measures.[_AllocatedVal]} ON COLUMNS,
  [_ALLOCATE_ME] ON ROWS
FROM Sample.Basic
WHERE ([Actual], &Year))

Now open Smart View, submit 50,000 into (Feb, East, Colas), select the cell where you just submitted the number and click the Calculate button. You should see Allocate_Sales MDX script. Smart View automatically picks up the parameters from the grid.

Run it and refresh the grid

Isn't this cool?