How to manage BoM explosion

Center of Excellence · September 2023

1. Abstract

For the production of an item, different materials/ingredients are used. For the production of these materials/ingredients, other materials are required. In the BoM Explosion, all materials of the different levels are written to one level. This article describes an approach within Board – based on a project where the recipes could be changed during the planning process within Board.

2. Content

2.1 Approaches

In the ideal world, the BoM should be delivered already exploded to Board. In the project, the customer required to change the recipes of materials in the planning process. The materials also contained information about where those materials are coming from/are produced. Also, materials may be replaced by other materials of a differing quantity.

If it is possible to use an SQL database, this might be the preferred solution. With the help of CTE (Common Table Expression/With-Statement), it is easy to generate a flattened list of all materials.

If this is not an option, it might be required to use Board.

2.2 Common Understanding

2.2.1 Entities

There are three entities:

Head Material
BoM Source
BoM Target

Both BoM entities could be replicated. Head Material and BoM Source use the same ID, otherwise, a mapping between these two entities would be required.

2.2.2 Cubes

And there are three cubes:

INP Recipe (Dim: BoM Source, BoM Target): Containing the recipe in %
INP Volume (Dim: Head Material): Containing the volume for a year.
CLC Exploded BoM: Containing the shares of all BoMs per head material. This cube has to be calculated from the other cubes.
CLC Exploded BoM Volume: Containing the volume of all BoMs per head material. This cube has to be calculated from the other cubes.

2.2.3 Procedure – Looping

The looping in Board can be achieved by using the following option in the Go to group action:

E.g. this value can be set to 100. In each loop, the condition should be analyzed whether another loop is required or whether the procedure should be stopped, e.g. using the action Exit procedure.

2.2.4 Parallel Execution

If it is possible that several users could run the calculation at the same time and it is required to use export/import mechanisms: Please secure the process with @User for the export and import/data reader. A Board User entity is not required starting with 12.5.1 because virtual cubes can be used in most of the procedure actions and in the data reader for import/export steps.

It is recommended to use the virtual cubes for the data reader. Many cubes are just temporary cubes and therefore, they should not create any new persistent sparsity entries.

2.3 Challenges

2.3.1 Circle References

If it is possible to create circle references, they have to be detected and the iteration has to be stopped for this iteration path. Otherwise, the looping would take forever.

2.3.2 Cutoff

At a certain iteration, the numbers get very small and consume more resources than their relevance. Therefore, a cutoff point has to be defined, e.g. if the outcome is below 10 kg. Also, the % could be used or a price or a combination of it all. In the project, it was very important to cut it because the runtime increased a lot with more detailed BoM explosions.

2.4 Approach 1: Fixed Iteration/Small Explosion

In the fixed iteration process, n entities are created – each representing a level. So for 5 levels, 5 BoM Target entities are required. This works well only for a few levels. With the increasing amount of data and maximum possible combinations, this process gets too slow. In the worst case, entities have to be set as dense.

An example approach:

Level 1: Mapping (Source/Target 1)
Level 2: Mapping (S/T1) * Mapping (T1/T2)
Level 3: Mapping (S/T1) * Mapping (T1/T2) * Mapping (T2/T3) …

At the end, the sum of all levels is the result of BoM Explosion (Level 1+ Level 2 + Level 3 + …). Before the cubes can be added up, the last BoM Target entity has to be transformed to a unified level.

Disadvantage:

Not flexible, too fixed.
Problems with many levels
Many cubes

2.5 Approach 2: Simple Dynamic Iteration - Ignoring levels

If we can be sure, that there are no circle references and we do not want to track down the exact explosion, we can use a simplified method for a dynamic iteration.

An example approach:

Level 1: Mapping (S/T) > result is added to a Result cube and written to the Previous Level mapping cube which is used in the next iteration.
Level 2: Mapping (S/T) * Previous Level Mapping > result is added to a Result cube and the Previous Level mapping cube is overwritten with the result of this level.
Level 3: Mapping (S/T) * Previous Level Mapping > result is added to a Result cube and the Previous Level mapping cube is overwritten with the result of this level.
…

The Previous Level mapping must be prepared before it can be used in the next iteration.

Remove Source Entity
Add Source Entity by using Target Entity
Remove Target Entity

Advantage

Easy to implement
No need for export/import in the calculation

Disadvantage

No BoM Explosion path
No Circle Reference detection

2.6 Approach 3: Dynamic Iteration - Include full levels

In the project, the simple dynamic approach was not sufficient. It is necessary to identify circle references (primary reason) and to visualize the calculation path to verify the results (secondary relevance).

In the case there are n dimensions (n=number of loops), the detection cannot be done in a dataflow. Instead, a data reader with the FIND function (ETL) is used. The export must contain a concatenation of all levels in one entity and the new target element code. In the ETL the circle reference can be detected and the result is written to a cube. An additional entity is used: Level. The level contains the number of iterations. Circle references are written to -1 and will not be used in the n+1 iteration.

The cutoff is responsible for the duration of the process. At the customer, it can vary between 1 Minute and more than 30 minutes. With an increased amount of sparsity entries, the process gets slower.

In addition, for this approach following entities are required:

Level
BoM Concatenation Path

The first loop must be treated differently from the other loops. At the customer project, the cutoff was not applied for the first loop. In addition, the concatenation entity must be added/created differently to the result cubes. In the first loop, the concatenation entity is equal to the source entity. In the case, a target entity is equal to the source entity, the circle reference is recognized.

2.6.1 Process

The process looks as follows:

2.6.2 ETL Formula

The ETL in the data reader to identify the circle references looks as follows:

Be aware, that depending on the code format, the FIND Function could find false circle references, e.g. if different code lengths are used and one code is part of another.

If a circle reference is detected, it is written to BoM Level -1.

2.6.3 Results

Center of Excellence · September 2023

Thanks @Jan Kaufmann for the insights on this article!

Carlos Eduardo Meneses Maran · January 12

Excellent and extremely difficult topic 👍️. I would suggest that it be reviewed and expanded with more step-by-step instructions to make it more straightforward to replicate and implement in Board 📝.