Performance affect of "SELECT:" and "LOOKUP:" used together
I couldn't find any information on whether it's recommended to split SELECT and LOOKUP for better performance. This thread helped me to understand that SELECT works in a similar way as LOOKUP (thanks to @DavidSmith for insight). So does it mean that SELECT and LOOKUP can be used together, unlike SELECT and SUM?
And what is the overall performance affect of SELECT compared to LOOKUP? Should it be avoided if possible? Or it has almost no affect on calculation time?
Good question. I find this information in Planual:
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
The main restrictions on the use SELECT are not related to performance, but to the ALM configuration and development rules (less hardcode). I have not heard of any negative consequences in combining formulas.
Technically you can use SELECT with any dimension but as per the best practice SELECT should be used only with Anaplan Native Versions ,Top Levels, YTD and YTG. Before I explain further I assume you have good understanding on PLANS methodology.
When you use SELECT on any non Production List you are hard coding it and Anaplan wants us to get away with the hard code as it will not follow Scalable/Sustainable (S) element of PLANS
When you try it to use it on Production list Anaplan will either not allow you to use SELECT on Production list ( if you have already marked the list as production) or else Anaplan will not allow you to mark the list as Production list if SELECT has already been used on the list - both times error message pops up.
LOOKUP Modules or Constant Modules is better way of modelling as it doesn't breach S element of PLANS methodology. As far as performance is concerned I feel SELECT will be better than LOOKUP , although I don't have data to back myself up.
Avoid using these Compound Functions ( SUM & SELECT, LOOKUP & SELECT and SUM & LOOKUP) together in one line item. SUM & LOOKUP is the worst performing compound function - that's proven already and I believe LOOKUP and SELECT should be the second in line ( Not Proven).
@Hayk SELECT and LOOKUP work in a similar way and, in my opinion, there is no difference in terms of "calculation performance".
However there are other reasons that I would try to use LOOKUP over SELECT and the reason is not the performance, but the parametrization and flexibility of the formulas.
My personal interpretation is this: SELECT is the hard-coded version of LOOKUP.
The SELECT refers to a particular element from a list and needs to be hard-coded.
If for some reason, the formula needs to refer to another element, the only way to solve this is to change the formula (structural modification)... if LOOKUP is used: just change the value of a cell (new mapping) and the formula will return the new desired value.
I try to use SELECT only:
- to refer in a formula a particular Anaplan Version different from the "Current Version"
- to refer Top-level elements from a List
All reasons that do not have anything to do with the performance... 🙂