Planual Explained - Day 18
"Rule 2.02-08": Article 2, Chapter 2, and Rule 8 – “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. 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 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 approach 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
Target Module: Here Time is in Months
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
SYS Time Module : This module holds all the Time formats of the timescale
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:
What is wrong with this method? SUM & LOOKUP being used in the same expression has shown performance degradation 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”
Create Final Target Module : Use “LOOKUP”
