Performance affect of "SELECT:" and "LOOKUP:" used together

Hi community!

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?

Thanks,

Haik

Accepted answers

All comments

AntonMineev

Hi @Hayk!

Good question. I find this information in Planual:

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

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.

AntonMineev

UPD: I find this video.

But in my understanding, the problem is still not in performance, but in the cleanliness and correctness of development.

Снимок экрана 2021-04-21 в 13.51.07.png

Misbah

@Hayk

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).

Misbah

alexpavel

@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... 🙂

Hope it helps

Alex

Olek P

I had first used SELECT for a list member. However after reading this I tried to replace the SELECT with a LOOKUP and the formula wouldn't work. So LOOKUP & SELECT aren't exactly the same thing? In my case I can SELECT the list member but can't look it up. Any thoughts?

Quick Links

Can you help?

Unanswered questions

Not Able to Join the Level 1 Model Building Course
Hey Everyone, I was trying to learn about the Level 1 Model Building Course, when i log in to my system and click on "Start the course" option its showing training unavailable, Can anyone please help me with this @Dikshant and all?
Generating Failure Dump - even though no warnings/error in Actions
I've few actions in our Supply model which when run takes some time on the "generating failure dump" step at the end of the action even though the import is a full success without warning or errors. This is just a few seconds when run for a single action, however we have a process with 120 actions, and most of these…
OAUTH 2.0 vs Google Sheets Extension
Hi, My org is looking to explore leveraging Google Apps script to extract data out of Anaplan and update already built templates in Google Sheets. We use the Anaplan Extension currently but find it crashes often with the amount of tabs, data, etc. Has anyone used both Anaplan OAUTH 2.0 API and Goolge Apps Script to write…