1. Abstract
One of the initial steps of the development phase in a project is the creation of entities. The most important property of an Entity is the Max Item Number which needs to be defined carefully, considering the effect it has on the performance of the application.
2. Context
One of the entities’ most important properties is the Max Item Number. The correct definition of it is important because is then used to size the sparsity during the Info Cubes creation. The sparse structure affects ONLY the performance, in other words, the execution time of the dataflow steps in any procedure. Therefore, the Info Cube definition is strongly recommended to avoid the product of ALL Max Item Nr exceeding that 64-bit structure (i.e., 264). Having a cube with a structure greater than 64-bit is not prohibited but, turns the sparsity to 128-bit which is LESS performing compared to 64-bit.
Please be aware that the sparse structure DOES NOT HAVE an impact on the calculation domain of the dataflows because the dataflow works only on the existing combination of the calculation domain and does not consider the existing combination in the sparsity.
Reiterating the above paragraph, changing the structure of an Info Cube DOES NOT HAVE effects on the result of the dataflow calculation. It can potentially influence the execution time of the dataflow.
There are two ways to define the Max Item Number, Automatic and Manual, let’s see what the best practices are to help you decide which choice you should have.
3. Content
3.1. Case 1: All the entities have Manual Max Item Nr.
Setting the Max Item Number manually is always recommended if the below conditions are met:
The actual number of elements of the entity includes only active elements.
The company has a clear understanding of the evolution of the entity by looking at the historical horizon (preferably, the last 3 years) to facilitate the prediction of new elements to be expected, if any.
When the Max Item Nr. is manually set for all the entities of the data model, Board applies the following actions in the following order during the set-up of the Info Cube:
Step 1: Board tries to set all the entities as sparse (except the time entities, if any).
Step 2: if the product of the max item numbers exceeds the 64-bit structure, Board defines as dense the entity with the smallest max item number.
Then it recalculates the product of the max item number of the new sparse structure.
It iterates this until the sparse fits with the 64-bit structure.
3.2. Case 2: Some of the entities have the Auto Max item Nr.
In the absence of clarity on either of the previous two conditions, it is recommended to use the Auto Max Item Nr. only for exploratory purposes at the beginning of the data quality check.
In this circumstance, where most entities have a known behavior and are already set to Manual Max Item Nr. whereas some of the entities have the Max Item Nr. set to Auto, the process performed is the following:
Step 1: Board tries to set all the entities as sparse (except the time entities if any)
Step 2: Board calculates the product of all the manual max item numbers.
Step 3: on the remaining available volume of the 64-bit structure Board tries to fit the entities with the auto max item number, assigning a temporary max item number that is PROPORTIONAL to the CURRENT members’ number of the entity.
Step 4: if the product of the max item numbers exceeds the 64-bit structure, Board defines as dense the entity with the smallest max item number.
Then it recalculates the product of the max item number of the new sparse structure.
It iterates this until the sparse fits with the 64-bit structure.
3.3. Case 3: All the entities have Auto Max Item Nr. & Manual Sparsity
When the Max Item Nr. is set to Auto for all the entities of the data model and the sparse structure is manually forced within the Info Cube, the process performed is the same as case 2, except for step 4, where Board does not switch the smallest entity in dense.
Once it identifies that the 64-bit structure is not enough it creates a 128-bit structure to address all the manually selected entities into the sparse.
4. Conclusion
Based on what we have explained, the best practice is to define the Max Item Nr. manually.
The value of the
Max Item Nr must be as small as possible either it’s manually or automatically defined.
With automatic configuration, you let Board make the decisions that you should take. The more members’ number of the entities are provided to Board, the more accurate the result will be. Otherwise, you have to make the decisions yourself.
We suggest using the Auto configuration only at the beginning when exploring the application before data loading, where the Auto setting will give an indication of how much you can set this number without impacting performance (i.e., exceeding the 64-bit structure). Then, before loading the final dataset, it is strongly suggested to set it manually.
Please note that a Board Expert (not necessarily a consultant) can do always better than Board because has a better understanding of the whole data model structure and how specifically each of the entities will be affecting it, while Board can just optimize the Max Item Nr. by assigning a temporary max item number that is proportional to the current members’ number of the entity.
