Introducing MDX INSERT

How to copy dynamically calculated values to a stored location

In my previous post we discussed how one can build a dynamic zigzag calculation in Essbase. In this post I will show how dynamically calculated values can be used in stored member calculations.

If you recall, our cube had two dynamically calculated formulas:

“Closing Inventory” = "Opening Inventory" - "Shipped" + "Received";

“Opening Inventory” = 

IF(("Inventory on hand" != #Missing) OR (@ISMBR(Jan)))

                        "Inventory on hand";

ELSE

                        @PRIOR("Closing Inventory");

ENDIF;

Let's modify the cube by adding a new dimension Year with two members 2017 and 2018.

After saving the outline all the data will go into 2017 member. Make sure that Year member is marked as "Dynamic Calculation" otherwise the data will end up in the Year member. 

Open Analyze screen for the Zigzag.demo cube and navigate to the following grid

There is no data in 2018 which is expected. Assuming that the inventory was tracked properly, the "Inventory on hand" in January of 2018 should be equal to the "Closing Inventory" in December of 2017. The "Inventory on hand" is a stored member so we need to copy values there.

Let's add a MDX script to Zigzag.demo cube.

• Open the inspector for Zigzag.demo cube and click Scripts->MDX Scripts
• Click + sign to add a new script
• Enter the MDX INSERT expression

INSERT
"([Closing Inventory])" TO "([Inventory on hand], Jan, [2018])"
INTO Zigzag.demo
FROM (
SELECT
{[Closing Inventory]} ON COLUMNS,
NON EMPTY CrossJoin(Dim1.Levels(0).members, CrossJoin(Dim2.Levels(0).members, Dim3.Levels(0).members)) ON ROWS
FROM Zigzag.demo
WHERE (Dec, [2017])
)

• Enter Inventory_on_hand_2018 as the script name
• Click Save and Close

To execute the script

• Click the Jobs ribbon
• Click "New Job"->"Run MDX"
• Select Zigzag.demo Inventory_on_hand_2018
• Click OK

Wait until the job is done and refresh the report

Now we have data in 2018. I believe the same effect could be achieved using standard BSO calc scripts, but it probably will require some tricks to generate empty blocks before you can FIX on it.

For more details about MDX INSERT see Official documentation