2.02-08 Never use SUM and LOOKUP together

SUM and LOOKUP used in the same expression generally cause large formula calculations and may cause intermediate relationship calculations especially if Time is a dimension or when the source and target structures are very different.  Splitting them into different modules and line items considerably reduces the size of the intermediate calculations

 

Exception:

2.02-08a The LOOKUP is a constant: Where the LOOKUP line item is a constant, the formula will operate as a SELECT, so the performance implications are negated

 

Best Practice article:
Formulas and their effect on model performance

Tagged:

Comments

  • Rule 2.02-08 Never use SUM and LOOKUP togetherSUM and LOOKUP used in the same expression generally cause large formula calculations and may cause intermediate relationship calculations especially if Time is a dimension or when the source and target structures are very different.  Splitting them into different modules and line items considerably reduces the size of the intermediate calculations. It goes against PERFORMANCE element of PLANS if you wish to go against this rule.

    Note: This rule is also applicable to other Compound formulae like SELECT & LOOKUP and SUM & SELECT. Since most of the good model builders don’t use SELECT that often we will focus this rule only on SUM & LOOKUP

    Use Case: Let’s say we have a Source module with three dimensions i.e., Customer, Account and Time and we want to do the top down to pull the data into Target module with again three dimensions i.e., Customer, GL Accounts and Time

    Here is how it was done in Pre Planual Era:

    If we look at the dimensions of the modules we know that two of the dimensions don’t match whereas only one dimension match between the two modules

     

    Source Module: Here Time is in Quarters, hence only Customers is Green or matching, rest two are Red or Not matching

    Misbah_0-1591099091324.png

    Target Module: Here Time is in Months

    Misbah_1-1591098966832.png

     In order to pull the data from Source Module to Target Module we need to make sure that these two modules talk to each other either by LOOKUPs or Aggregation functions. Because of which there are other SYS modules that are in place which map the differentiating dimensions

    SYS GL Accounts Module: This module shares the link between Accounts list and GL Accounts list

    Misbah_2-1591098966844.png

    SYS Time Module: This module holds all the Time formats of the timescale

    Misbah_1-1591099138775.png

    Now that we have all the pre-requisites in place, let’s understand the dimensionality

    Source Module Dimensions

    Target Module Dimensions

    Comments

    Customer

    Customer

    Matching

    Time Quarters

    Time Months

    Not Matching but Mapping Available in SYS Time Mod

    Account

    GL Accounts

    Not Matching but Mapping Available in SYS GL Account Mod

     

    Target Module and Formula:

    Misbah_2-1591099164361.png

    What is wrong with this method? SUM & LOOUP being used in the same expression has shown performance degradations and should NEVER be used together. Not only is this the rule but it is one of ten guidelines (Zen) of the Planual as well.

     Here is how it should be done in Planual Way:

    Break up the formula into two line items – Use SUM in the first line in one module and LOOKUP in another line item in a different module

    Create Intermediate Target Module: Use “SUM”

    Misbah_3-1591099201898.png

     Create Final Target Module: Use “LOOKUP”

    Misbah_4-1591099229222.png