How to allocate production by CM1

Center of Excellence

1. Abstract

This technical article describes a way of dealing with row-by-row calculation in Board without using Nexel. Nexel can be used for small examples but does not have a good performance in mass calculations. Keep in mind that not all row-by-row calculations can be transformed into “dataflow-only” calculations. 

Even if Nexel could seem the right functionality to address similar requirements we would like to propose a solution that works only with dataflows due to Nexel limitation in terms of performance. 

2. Context

A calculation is required to allocate sales planned volumes to production sites. In the case the production site cannot fulfill the whole sales demand, volumes must be prioritized by their CM1 (Contribution Margin 1). Other volumes will be reduced or removed completely from the allocation calculation.  

The example is only an excerpt of the whole process. The CM1 allocation will be used approx. 6 times per loop. The allocation process itself runs in loops because the reduction of sales demand reduces the BoM demand which frees capacities. These capacities can be re-used. 

3. Content

3.1 Common Understanding

3.1.1 Row-By-Row Calculation 

Here is a short example of a row-by-row calculation: 

Products have to be produced but the production capacity is limited. Therefore, volumes must be cut when no capacity is left. 

In this first example no order on the products is applied. At the customer project, the CM1 is used to define the order of the rows.

3.1.2 Entity Element Sorting

The sorting of the entity is required for a Nexel solution and the front-end reporting. For a “dataflow only” solution, like the one we propose, this is not necessary. The sorting is done by the “sort by” setting in the entity. E.g. it can be used to sort by the description. The description will be updated according to the sort settings.

For the update of the description, a layout is exported with the ranking function “counter”, show all entities by rows, show codes, sort by a block and the “to-be-sorted” entity in the rows. Besides the @User in the filename, no further parallel processing precautions must be taken into consideration.

Also, other aggregation entities can be used for the calculation, e.g. containing the numeric rank.

3.1.3 Entities

Following entities are required for the example:

• Product
• Production Plant (Aggregation of Product)
• Product [R] (Replication)
• Production Plant [R] (Replication)

3.1.4 Cubes

And there are three input cubes:

• INP Volume (Year, Product)
• INP Production Capacity (Year, Production Plant)
• INP CM1 (Year, Product)

CM1 = Contribution Margin 1. Is used to sort the rows. More profitable products should be prioritized for production allocation, because more CM1 = more profit.

The results are written into following cube:

• CLC Allocated Volume (Year, Product)

There are also some temporary cubes, which are necessary and few optional reporting cubes.

• Virtual Temporary Cubes:
• vTMP UPL Rank (Year, Product): Rank is loaded back to Board
• vTMP CLC Rank [R] (Year, Product [R]): Rank is written to Product [R]
• vTMP MAP Upper Row Mapping (Product, Product [R]): Upper Row Mapping between Product and Product [R]
• vTMP UPL MAP Production Plant x Production Plant [R] (1:1) (Production Plant x Production Plant [R]): 1:1 Mapping between the production plant and its replicated entity
• vTMP Volume [R] (Year, Product [R]): Volume on Product [R]. The volume is switched to [R] first, then the upper row will be calculated. This method will increases the data amount as late as possible.
• vTMP Upper Row Volume … (Year, Product, Product [R]): The Upper Row volume (the amount of the volume above the row the allocation takes place).

3.2 Approach – Dataflow only

3.2.1 Introduction

The “dataflow only” approach is based on mapping calculations. The “upper rows” sum must be precalculated by using the replication of the product.

An Example (see upper rows volume demand):

3.2.2 Required Mapping

We need to calculate the “upper rows volume demand”, i.e. having a mapping for calculation the volume sum of the precedent products. The mapping looks as following:

E.g. before product B can be allocated, there has to be enough capacity on the plant. As product A has a higher CM1, it is allocated before product B. Therefore, the volume of A is required for the calculation on product B. For the product C calculation, the volumes of A and B are required.

So, during the calculation the number of cells increases. In this example, if there are many products in one plant then there are many additional cells calculated.

Attention: The Volume is written to [R] before and then this mapping is used.

3.2.3 Calculate Mapping

The base of the mapping calculation is an export/import of the products with their required order. The ranking is stored in cubes and is used for the calculation of the mapping.

The layout export looks as following:

The ranking is imported back on product level (vTMP UPL Rank). Additionally, a 1:1 mapping between Production Plant and Production Plant [R] is loaded (vTMP UPL MAP Production Plant x Production Plant [R] (1:1)). It is required for the mapping calculation to limit the mapping generation to one production plant.

After the ranking is loaded, it is transferred to the entity Product [R] (vTMP CLC Rank [R]). Now all three cubes are combined to calculate the upper row mapping:

The mapping is used on the volume, after it has been transferred to Product [R].

3.2.4 Allocate Volume

After the mapping calculation the volume is allocated – respecting the production capacities of the plants. At the beginning volume is transferred to Product [R]. Then the mappings is applied to calculate the Upper Row Volume:

1. Copy the Volume from the INP cube to the replicated entity. The entity is switched (Product removed, Product [R] added).
2. Apply the mapping with a Join dataflow.

The allocated volume can be calculated as following:

1. IF Upper Row Volume > Capacity THEN 0
2. IF Upper Row Volume + Volume > Capacity THEN Capacity – Upper Row Volume
3. ELSE Volume

3.2.5 Experiences in Project

Using dataflows instead of Nexel accelerated the calculation time. Within one calculation loop this has to be used many times because of different limits on different levels. It is way more complex but the time consumed is tolerable. The most time spent is for the mapping calculation itself.

3.2.6 Example Application

Center of Excellence

Thanks @Jan Kaufmann for the insights on this article!