About Zigzag

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?

Let’s look on a very common inventory example. We will consider following four measures: “Opening Inventory”, “Shipped”, “Received” and “Closing inventory”. The “Closing Inventory” would be calculated as “Opening Inventory” – “Shipped” + “Received”. The “Opening Inventory” of a month equals to the “Closing Inventory” of the previous month. The spreadsheet below illustrates this calculation.

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;

Let’s submit the numbers and check the results.

 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.

Conclusion

Our zigzag calculation is done in a completely dynamic way. New Hybrid engine was able to resolve dependencies for two dynamic formulas that were referencing each other. In additional the resulting calculation could participate in further aggregation and/or time balance calculations