2.02-05 Create "joins" in smallest hierarchy

rob_marshall
rob_marshall
in Planual

If a text string join is needed, create the joins in the smaller lists first to minimize the size of the text strings

 

Related to Rule:
2.02-04 Text Strings

 

Best Practice article:
Formula Optimisation in Anaplan

Tagged:

Comments

  • Misbah
    Misbah

    Rule 2.02-05 Create “joins” in smallest hierarchy.  If a text string join is needed, create the joins in the smaller hierarchies first to minimize the size of the text strings. It goes against PERFORMANCE, LOGICAL, NECESSARY elements of PLANS if you wish to go against this rule

    Here is how it was done in Pre Planual Era:

    Misbah_0-1591029822400.png

    What is wrong with this method? There are three joins in the above screenshot (Customer Code, “_” and Product Code). Customer Code and the Product Code calculations are fine, it is the separator that doesn’t necessarily have to cut across all the dimensions.

     Here is how it should be done in Planual Way:

    When you need to join text together in this way, it is likely there will be a common pattern used elsewhere within your model. In our example, the Company code could be the prefix for many other text strings. So, following the rule of “calculate once, reference many times,” we should put the &”_” join in the module by Company. As a rule of thumb, make the join in the smaller of the lists. In our case, we have 1,000 Companies and 3,500 Products, so it is more efficient to add this as a suffix to Company Code & not Prefix to the Product Code.

    Misbah_1-1591029822416.png

     

    The line item formula for Code to use = Code&”_”. The final step is to replace the formula in the ultimate target with a reference to this intermediate line item.

    Formula = ‘SYS05 Company Details’. Code to Use & ‘SYS06 Product Details’.Code

    One final step to ensure that calculations are fully optimized is to always consider whether the summary options are applicable for the calculations. In our example, by default, Text items default to None because combinations at the parent or total levels do not make sense. The code of the Company item is not applicable to its parent. So, for Text formatted items, there is nothing more to do. However, always check if the Summary options are needed.

Title

Categories