Anaplan Use Case 3: Matrix-Style vs. Vector-Style Data Grid
Often, business users find it more convenient to review and input assumptions/drivers in a certain style. Once such way is matrix-style data grid where each of the two dimensions are on separate axis. This is an easy feat when the dimensions involved are two discreet entities/lists such as Region vs. Department, Location vs. Variety, etc.
However, what does one do when the two dimensions involved in the matrix-style data grid are two components of the same dimension? For example, Month and Year (or Week and Year) of Time dimension.
Contrast this with the vector-style data grid that Anaplan defaults to when the time dimension is used.
The existing use-case is all about handling this unique user requirement of matrix-style input. Once you understand the context and the requirement, feel free to give it a try, before peeking into the proposed solution.
NOTE: This article is a segment of the Anaplan Use Cases series. If you haven’t already, I encourage you to read through the introductory blog Why Anaplan Use Cases Series? to understand the background behind this endeavor.
Context
A hypothetical firm Saffire is an agricultural company in India that produces, exports, markets and sells Saffron in USA only.
The Supply Planning process in the firm involves inputting and tweaking drivers/assumptions such as yield (of plants) and area (of land) to calculate weight of the crop projected/forecasted for upcoming three years.
Weight = Yield x Area
The Price Planning process runs a predictive algorithm that forecasts the price ($/gm) of Saffron for each month — a price that the firm can command from the buyers of Saffron.
With both the measures at hand, revenue projections are calculated for each month of the upcoming years.
Revenue = Price x Weight
User Story 1
As Supply Manager, I need to be able to input yield and area assumptions by calendar month and year, so that I can review the projected weight/revenue of Saffron immediately. I should be able to input the assumptions in such a manner that I can easily eyeball and compare the numbers of the same month across the years.
User Story 2
As Supply Manager, I need to review the projected weight and revenue by calendar month and year, so that I can finalize the supply plan. I should be able to eyeball and compare the numbers of the same month across the years.
Weight = Yield x Area
Revenue = Price x Weight
The system should already have Price imported from Pricing Model by calendar month/year (in vector-style format), as shown below.
Analysis & Design
The time dimension in Anaplan runs sequentially across years, enforcing a vector-style data grid. It is not possible to break it down into its components (Month and Year) and use them as separate dimensions in one data grid. Hence, we would need to separately create one more dimension — Month, to handle the unique matrix-style requirement requested by the user.
Additionally, we would also need to go back and forth between the two styles (Matrix vs. Vector), because Price is in vector-style and Revenue needs to be reported back in matrix-style.
Please note that going from matrix-style to vector-style is NOT an aggregation, even though the target has one dimension while the source has two dimensions. Since the dimension of the target is not part (subset) of the dimensions of the source, we can’t use any aggregation function.
Implementation
The steps we can take to solve the problem at hand are as follows.
a) Create a new list — Month.
b) Create a system module to store the mapping of Time dimension with Month and Year dimensions. Notice the formats and formulae of the line items carefully.
c) For User Story 1, create input modules in matrix-style. We can input some numbers to be able to test the data flow/calculations as we go along.
d) For User Story 2, calculate weight in matrix-style. (STG keyword in the name denotes that the module acts as staging for the Weight measure).
e) Now, we need to convert Weight from matrix-style to vector-style, so that we can multiply it with Price to calculate Revenue. For conversion, if we simply refer to the source line item, how would the system know which source cell to get the data from, for a given target cell? There is no commonality in the dimensions of the source module and those of the target module.
We need to instruct the system to source the data from the right cell — the cell that corresponds to the given target cell. But how do we do so?
By utilizing an intermediary — the mapping of Time dimension to Month and Year dimensions in the Time System module.
Keeping this concept in mind, we use qualifiers in the formula of the target line-item while referring to the source line-item. We leverage the LOOKUP function to get the qualifiers from the Time System module.
Voila! We convert weight measure from matrix-style to vector-style.
f) Since we already have Price module (sourced from Pricing Model in vector-style), we can easily create Revenue module in vector-style.
g) Finally, for the completion of User Story 2, we convert Revenue from vector-style to matrix-style. Again, we need to provide a qualifier in the target module line-item formula while referencing the source module line-item. But where do we get this qualifier mapping from? Well, we could create one in the target module itself — a time-formatted line-time “Time” to store the Time members associated to the Month and Year dimensions.
We use the Time line-item as a qualifier in the Revenue formula.
Bingo! We convert revenue measure from vector-style to matrix-style.
User Interface
Putting a UI page together with (saved views of) the modules we just created should not be a big task. The UI page would look something like this. As the Supply Manager inputs/edits assumptions in matrix-style, he/she can see the impact on weight and revenue immediately, again in the preferred matrix-style. At the backend (in the Anaplan model), we can use the vector-style to carry the measures forward in further calculations as and when we get more user requirements.
Conclusion
Even though Anaplan does not provide an inbuilt feature/capability to use two different components of Time dimension (such Month and Year, or Week and Year) in the same module, it is not very difficult to move from vector-style to matrix-style, and vice versa. All one needs to remember is to provide qualifier(s) (through LOOKUP function) while referencing the source.
