How to loop procedures
1. Abstract
When developing business logic in board applications it sometimes becomes necessary to perform calculations individually for each Member of an Entity. In Board, this can be achieved through different means. This document is to review different methods of “looping” and their use cases.
2. Context
During the development of a board business application, different types of requirements may arise. In Board, there are different mechanisms to handle such requirements. Let’s see which of them can be used for which purpose.
3. Content
3.1 Iterating over Entity - Members of the Time Tree/Using Time Functions
When performing calculations that require period-by-period calculations such as forecasting stock inventory, a basic board function “Period by period recursion” within the board dataflow can be used.
Goal: Stock(current month) = Stock(previous month) – Planned Sales + Planned Inventory Inflow
Solution: Use Period Offset -1 on the Stock-Dataview Block and activate “Period by period recursion”
The dataflow will then calculate the month-end inventory for each month subsequently, using the previous month’s calculation result as a parameter for the next.
If “Period by period recursion” is not enabled, the Dataflow could only calculate the first month which at the point of starting the Dataflow had a populated period –1 value available.
Another way to solve this simple requirement would be to make use of cumulation functions. So be sure to consider all available tools in the box before opting for a looping function.
3.2 Iterating via Looping Procedure
Let us revisit the previous example and rephrase the Requirements to cover another use case. Now we want to focus on our inventory outflow. Our inventory per product consists of different production batches with individual expiration dates. We now want to reduce the stock of individual batches according to their expiration dates, which means that for each month we need to determine which batch to select and to deduct our planned sales from. The planned material inflow is handled by a different process, so we do not worry about it in this example. For the sake of simplicity, we also assume that each month's planned sales fit within the batch with the lowest expiration date so we do not need to spread our planned sales across multiple batches (which could be done by using more loops).
Since we cannot switch selections on batches in a single dataflow with “period by period recursion” we need to break our process up into more individual steps and create a manual loop.
Simply put, we would like to
- Create a loop which runs our calculations for each month sequentially
- For each Month
- Select the batch with the lowest expiration date
- Deduct planned Sales from Batch
Period-by-period recursion is not the right choice when you need to execute more than one calculation or, as in this case, the batch from where we deduce the sales need to be identified month by month.
Prerequisites
To create a loop within a procedure we need the following components:
- A temporary cube that puts in order all the members of a specific entity which the looping procedure is supposed to iterate over. (“Enumerated Entity Members”)
- Another temporary cube that points to the entity member used by the current iteration of the loop. (“Entity Member Pointer”)
Creating and Structuring the Procedure
A simple Looping Procedure usually has a three groups:
1. Main/Preparation
2. Loop
3. Exit
Creating the Looping Logic
The Looping Logic is created by the following mechanism:
Preparation
- Putting a sequence of all Entity Members to be looped through into the Enumerator Cube.
This is usually done using a layout extract/load through Data Reader. The Layout makes filtering /Sorting relevant Entity Members really easy and convenient.
This simple example uses the “Net Sales Planned” Cube as a filter, the “Month” Entity Block as a sorting column, and a “Counter” Ranking Function as a value field which will be loaded into our Enumerator Cube.
- Loading the extracted sequence into the Enumerator Cube so it looks something like this:
The same result could also be achieved by using a Nexel Writeback Function.
Loop
- Mark the Entity Member which is currently “1” in the Enumerator Cube in the Pointer cube.
After this step, only the first Entity Member of the created sequence has a value in the Pointer Cube.
- Check if the Exit Criteria for the Loop have already been met.
In our example, we just want to loop through all months selected for our sequence (In Programming Language this would be called a FOR-Loop). So we check if our Pointer cube has been populated with a “1” in the previous step. If yes, there was still an Entity Member to be processed. If no, all of the Entity Members in the Enumerator Cube have already been reduced below 1 which means that all of them have already been processed and the procedure loop is finished. In this case, we jump to the “Exit” Group which ends the procedure.
You could certainly apply other Exit Criteria to create UNTIL-Loops etc.
- Select the first Entity Member which was marked in the previous step using “Select Entity based on Cube”
- Perform the Selection/Calculation actions for the selected Entity Member.
This section is where your business logic comes into play. In our example this is where we would perform our Selections and Data Flows to select the batch with the lowest expiration date and deduct planned Sales from the batch inventory.
- Reset the Selection on the Looping Entity.
- Subtract 1 from all Entities Members’ values within the Enumerator Cube.
We can see that after the subtraction, Mar.24 now has the value “1” while the previous “1” which was Feb.24 has been reduced to “0”. Due to this change, Mar.24 will be the month selected when the Loop Group is executed the next time. Remember that our example Loop will run until there is no Member with value = “1” in the Enumerator Cube left.
Note that since the loop is on entity month it can be also achieved with the time functions (Previous period or applying an offset)
- Restart Loop Group
The total Procedure could look like this. Please note that step 9 is a placeholder for your business logic.
4. Conclusion
We have now seen different methods of Loops within Board Procedures. Thanks to the “Period by period recursion” Setting in the Dataflow step, many cases can be handled through this setting alone.
When considering a looping solution, one should first evaluate if “Period py period recursion” or Time Functions can be used. This is usually the case if the same one(!) dataflow needs to be applied period by period. If more than one dataflow needs to be applied to each month, a “custom” procedure as described above may be the appropriate solution.
Please also check out:
- article on multi-level allocation which makes use of looping
- article on period-by-period recursion for additional details on this feature
Comments
-
Thanks @Daniel Borghs for the insights on this article!
7