Formulas: Are there SUM and LOOKUP combinations?
When is this a problem?
Having SUM and LOOKUP in the same line item is always a problem and can cause performance issues.
Why is this a problem?
Using SUM and LOOKUP in the same formula generally causes large formula calculations, especially if time is a dimension or when the source and target structures are very different.
How to correct:
Split SUM and LOOKUP into different modules and line items.
More information:
0
Model Optimization Checklist
- Step 1 - Lists
- Does the list use Time as part of the key?
- Does the list use a combination of properties as a code?
- Does the list have a code?
- Does the list use Properties?
- Are there subsets on larger lists?
- Does the subset list follow proper naming conventions?
- Does the list include a top level when it is not necessary?
- Step 2 - Modules
- What dimensions are used in the module? Are they all necessary?
- What time dimensions are being used in the module? Can a Time Range be used?
- Are the dimensions in order?
- If there are any saved views, do they use the correct filtering?
- If there are saved views, are all of the line items in the view necessary?
- Is the default view clean?
- Step 3 - Line Items
- Which line items do not need a summary?
- Are text formatted line items overused?
- Are there more than 50 line items in a module?
- Are there excessive subsidiary views?
- Are there line items used as headers where the format is not set to No Data?
- Step 4 - Formulas
- Are there line items that can be placed in a systems module rather than within a calculation module?
- Are there any formulas that that combine text strings?
- Are there any formulas that are daisy chains?
- Are there long formulas, such as complex IF THEN ELSE statements?
- Is there an excessive use of the FINDITEM function?
- Are there SUM and LOOKUP combinations?
- Is the TEXTLIST function used?
- Do any formulas use the SELECT function, especially with time?
- Do any of the line items use conditional formatting with summaries turned on?
- Step 5 - Actions
- Are all the actions necessary?
- Do any of the actions result in errors when they are run?
- Check the data sources. Are some of them lists or modules?
- Check the data sources. Does each data source have an action associated with it?
- Step 6 - Time
- Is the main time scale of the model larger than it needs to be?
- Additional Resources
- Model Optimization Tracker
- Planual