2.02-04 Text Strings

rob_marshall
rob_marshall
in Planual

Treat text strings with caution.  Try and avoid multiple joins and split common joins to separate line items.

Make use of IF ISBLANK() when joining text, if the strings are empty set to BLANK.

 

Related to Rule:
2.02-05 Create "joins" in smallest hierarchy
2.03-02 Try not to use TEXT as a format, or limit it as much as possible

 

Best Practices articles:
Formula Optimisation in Anaplan

Tagged:

Comments

  • Misbah
    Misbah

    Rule 2.02-04 Text StringsTreat text strings with caution.  Try and avoid multiple joins and split common joins to separate line items. 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:

    Let’s assume we have a module that is dimensioned by Company, Products, and Months. To allow us to connect to another part of the model, we need to create a combined text string that is the code of the Company and the code of the Product. We could create a text formatted line item with the following formula:

    CODE(ITEM(Company))&”_”&CODE(ITEM(Products))

    This will give the following result:

    Misbah_0-1591022998653.png

    What is wrong with this method? 

    The same result is calculated for ALL Products and ALL months even though the expression does not relate to those dimensions.

    Misbah_1-1591022998691.png

    The same happens for the second expression, CODE(ITEM(Product)).

    Misbah_2-1591022998722.png

    The same result is returned for ALL Companies for ALL months.

    And finally, let’s see what the “_“ is doing:

    Misbah_3-1591022998735.png

    The text is “calculating” for ALL Companies, for ALL Products, for ALL months.

    This is a classic case of Over Calculating. We do not need to calculate any of these expressions by month, and each expression only needs to calculate for the applicable list, or dimension. This is what I meant by “appropriate dimensionality"; we should only calculate expressions with dimensions that are appropriate to affect the result.

    There is a simple rule to apply.

    If the dimensions of the line item are greater than the appropriate dimensions for the expression, then the expression should be done elsewhere.

    Here is how it should be done in Planual Way:

    CODE() expression should be done in a module that only contains Company (e.g. Company Details), and the second CODE() expression, in a module only dimensioned by Product (Product Details).

    Following the DISCO methodology, you should have System modules for the Company and Products lists, so this is the natural place to house the new line items.

    Misbah_4-1591022998742.png

    Misbah_5-1591022998751.png

    We can then bring them together in the target module.

    Misbah_6-1591022998779.png

    This will give significant savings in calculation time.

    Note: There is a simple rule to apply.

    If the dimensions of the line item are greater than the appropriate dimensions for the expression, then the expression should be done elsewhere.

Title

Categories