Formulas: Is there an excessive use of the FINDITEM function?

When is this a problem?

The FINDITEM function requires a lot from the engine, as it is attempting to find an item in a list and it does this for every cell that is related to that line item. This is especially true when the list includes null values. Excessive use depends on your model and the lists it is being run against.

Why is this a problem?

Using FINDITEM excessively makes the engine work harder than necessary.

How to correct:

Check the data for null values before running FINDITEM. So where prod_text is the name of the list, the formula to check for null values is IF ISNOTBLANK(prod_text) THEN FINDITEM(prod_text) ELSE blank. This function is often used when loading data. Think about loading the data into spoke models from the data hub as list formatted line items rather than text.

More information:

Planual 2.02

 

Model Optimization Checklist