*Something that Essbase Hybrid engine can do*

# What is zigzag?

According to Wikipedia a zigzag is a pattern made
up of small corners at variable angles, though constant within the zigzag,
tracing a path between two parallel
lines.

# Great! How exactly it is related to Essbase?

If I would need to model this in Excel I would have the
following formulas

See how this calculation forms a zigzag?

# Let’s see how to implement such a calculation in Essbase.

First we will define two dimensions Measures and Time. In
the Measures dimension we will have four members from our Excel file. “Closing
Inventory” and “Opening Inventory” will be marked as “Dynamic Calculation” and
will have the following formulas:

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

“Opening
Inventory” = @PRIOR("Closing Inventory")";

These two formulas implement the calculation logic that we
described before, however in this model “Opening Inventory” is a dynamic member
which makes it impossible to assign an initial “Opening Inventory” in January.
To resolve this we will add a new member called “Inventory on hand” where user
can input the initial number; plus we will modify the “Opening Inventory” formula
to be

“Opening Inventory” =

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

"Inventory on hand";

ELSE

@PRIOR("Closing
Inventory");

ENDIF;

It looks like months values are calculated correctly, but
the quarters for "Opening Inventory" and "Closing Inventory" are not. The reason for this is the default
solve order. Remember by default the solve order of a dynamic sparse member is
10 and dynamic dense members is 30. The system first aggregates Time dimension
and only then it calculates the “Opening Inventory” and “Closing
Inventory”. In our particular case we
first calculate “On Hand Inventory”->Q1 and “On Hand Inventory”->Time and
then we calculate “Opening Inventory” and “Closing Inventory” for the same two
time periods.

To fix it we should change calculation order of “Opening
Inventory” and “Closing Inventory” to be less than 10. Let’s say 5.

Time is now aggregated after the formulas are calculated and
we add monthly inventory numbers to calculate quarter, which is not what one
would expect. The solution for it is to set Time Balance property of “Opening Inventory” to First and “Closing Inventory” to Last. Let’s check the numbers now.

It looks good. Here is a DBX workbook that will allow you to create the same cube on your OAC instance.

I do not think the quarterly rollups are working as you intended. If you set Opening Inventory as TB_Last, it's picking the value from March when it should pick up the value from January. This results in numbers that do not foot down the rows. Opening Inventory (94) - Shipped (81) + Received (39) = 52. The correct Closing Inventory is 58.

ReplyDeleteOh, thanks for pointing this out. Opening Inventory were supposed to be set to TB_First. I'll fix.

Delete