Planual Explained - Day 16

Misbah
Misbah
in Modeling

"Rule 2.02-04": Article 2, Chapter 2, and Rule 4 – “Text Strings”. “Treat 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 is meant by “appropriate dimensionality"; we should only calculate expressions with dimensions that are appropriate to affect the result.

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

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.

Tagged:

Answers

  • rob_marshall
    rob_marshall

    @Misbah ,

     

    An even better way is to remove the concatenation of the delimiter and put that in the system module with less members.  Not only will you get the same result, but you are removing a concatenation (2 to 1) which will make a big difference.

     

    2020-06-01_10-02-54.png2020-06-01_10-03-06.png2020-06-01_10-03-46.png

     

    Rob

  • Misbah
    Misbah

    @rob_marshall 

     

    I wanted to stick to this rule strictly..Initially I thought to club both the rules but then I couldn't convince myself because this article is already available under Best Practices and I just split it up as per the rules here.

     

    Next Rule: Create "joins" in smallest hierarchy, will do some plagiarizing there as well.

  • rob_marshall
    rob_marshall

    @Misbah ,

     

    Just wanted to give the full picture, especially with the post earlier (different poster) doing the concatenation in the "poor" way.

Categories