Auto Max Item Number
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.
Comments
-
Very helpful article!
One addition: If you change the max item number and, for instance, increase the number, please make sure that your already defined sparsities are still in 64-bit! This is a common mistake when increasing max item numbers leading the processes to suddenly become slow. In that case the sparsity should be redefined (ideally to 64-bit). Same applies, by the way, when decreasing a max item number and having already a 128-bit sparsity which then becomes 64-bit. In that case the sparsity should be updated.
Kind regards,
Bettina6 -
Another Use Case where it makes sense to leverage the Auto max item number is the following - also mentioned in the article here:
If you have a high-volume entity for which you cannot estimate a realistic max item number, becasue, for example, it grows in an unpredictable way, the suggestion is to leave it as Auto but with the coution that it's the only entity with the Auto max item number. In this way Board will allocate all the remaining space to its growth.
3 -
I have a question regarding manual max item numbers!
Some time ago, we received the advice to strive to set the manual max item numbers so the saturation hovers around 80% (to have some space for adding new members without maintenance). Is this still the recommendation?
Follow-up question: how about very static entities? Let's say, I have a technical entity "Yes/No" that will always only have Yes and No, these 2 members. Is it recommended that I set the Max Item number to 2, or should it be 3? (or if I have a "Row" entity that is just numbers from 1 to 1000. should max item nr be 1000, or should it be 1250?)
Thank you!
1 -
Dear @Krisztina Zappert
since Max Item Numbers are used by Board as parameter to assign a dedicated amount of space to optimize data compression and performance the golden rule is to keep it as low as possible, a generic good saturation is around 70% to 80% especially for entities with a low expected growth rate used in cube’s structure.
Said that, technical entities with no expectations of new members should have saturation 100% (Max Item Numbers = Item Numbers)
2 -
Hi Krisztina,
Yes, 80% satuartion this is still a good rule to follow when you set a manual Max Item Number (according to the guidelines in the article above) and expect moderat growth for the members of your entity.
If you know for sure there will be significant growth, quickly exceeding 20% you should size your Entity providently.To your second question. As the calculation of the sparsity is directly affected by the multiplication of the manual Max Item Numbers of Sparse Dimensions in your cube, you should think in order of magnitudes.
Wether your Entity has a Max Item Number of 2, 3 or 4, does not impact your Sparsity as strongly as your Entity having 2, 20 or 200 as Max Item Number.
If you are not sure wether to choose 4000 or 5000 as Max Item Number, I would recommend going for 5000 as this has only a moderate impact, compared to your Entity having 4000 or 40 000 as Max Item Number.That said, if you are absolutely sure your Entity will never have mor then 2 Members, you could set Max Item number to 2 without a problem.
But maybe in a few months when you decide you want to add a 'Maybe' option to your yes/no-Entity, it's helpful if you have reserved a Max Item Number of 5, for example, and the impact of this is rather moderate.
For your "Row" Entity, if you are sure you will never need more than 1000 Rows, of course you can set Max Item Number to 1000 ( or to 1001 to avoid having the Entity displayed as 100% saturated in your Entity Overview :-D )Hope this helps
Kind regards
Philipp
2 -
Thank you very much for the in dept and clear answers, @Leone and @Philipp Schumacher ! We shall commence with setting up max item numbers accordingly. :)
0
