Hi folks,
I was testing which function performs better in Polaris to pull data using a 1:1 mapping. Let me first give you the context:
I have a DAT module where many KPI metrics are structured as a Flat List with only one line item (Amount) to store data. Then, I have a CALC module whose line items are also KPI metrics. This module contains all the same KPIs from the DAT module, plus new KPIs whose calculations are based on line items that comes from the DAT (e.g., as numerators and denominators). The overall goal of this CALC module is to unify KPIs from DAT as well as calculated KPIs in CALC.
To move data from DAT into CALC, I initially pursued this method:
- I created a Line Item Subset (LIS) based on the CALC line items.
- I created a SYS module dimensioned by this LIS, with a line item formatted as the Flat List used in the DAT module.
- Then, the formula is: 'DAT03 KPIs Data'.Amount Final[LOOKUP: 'SYS14 Final Mapping'.Flat List KPI Mapping]
I created a second method to pull the data from DAT by using SUM:
- I created another SYS module dimensioned by the Flat List used in the DAT module, with a line item formatted as the LIS from the CALC module.
- Then, the formula is: 'DAT03 KPIs Data'.Amount Final[SUM: 'SYS15 LIS KPI Mapping'.'Item: LIS KPI Mapping']
For both methods of pulling data from DAT (SUM and LOOKUP), I applied the formula and then restarted the model to analyze the Calculation Effort %. To my surprise, the Calculation Effort is higher when using LOOKUP than when using SUM.
My core question is: Is SUM generally more performant than LOOKUP in Polaris? Even for 1:1 mapping.
Here an analysis for the same line items between SUM and LOOKUP performance:
