Skip to main content
Adaptive Insights
Knowledge and Support - Adaptive Insights

Manual Recalculation

Contains preview content for the upcoming 2017.3 release. 

Describes Sheet Isolation and Manual Recalculation.

Enabling manual recalculation significantly affects how your model is calculated. When it is enabled, portions of your model will not automatically refresh when other areas are updated. You should only enable it when the resulting improvement in performance is worth the ongoing administrative effort of recalculating manually. We recommend discussing its use with Adaptive Insights.

In a typical Adaptive instance, the entire model recalculates when any sheet is saved. For performance reasons, you may want to exclude some areas from automatic recalculation, especially during periods of heavy updates.

You can isolate modeled and cube sheets from the rest of your instance so that their calculations only occur on demand instead of occurring every time your data changes. Calculating on demand provides a performance benefit by storing the results of calculations and using those results until you decide to recalculate. You can trigger recalculations by clicking Recalculate Formulas. Calculations are also performed for each row on a modeled sheet or cube sheet whenever that row is edited.

Watch this video: 1m 9s

Manual Recalculation
 

For example, you might enable manual recalculation for a modeled sheet that performs complex calculations but depends on infrequently changed data. A complex personnel model may only need its rows to be updated when some base assumptions, like expected raise amounts, are changed. Individual employee rows on the sheet will recalculate whenever they are edited directly, but all other rows will recalculate only when Recalculate Formulas Recalculate Formulas is clicked.

Manual recalculation in Adaptive Planning is similar to Manual Calculation in Excel, which disables the default auto calculation for complex Excel spreadsheets to improve performance.

You can recalculate other rows on a modeled sheet when a related row is changed by enabling the Recalculate on match setting to indicate the other rows you also want to recalculate when a given row changes.

Where Manual Recalculation Could Help Improve Performance

  • On very complicated, calculation-intense models
  • On sheets that are very formula-driven and that have rarely-changing data
  • On complex sheets full of very complicated formulas that refer to many rows in other areas of the model
  • On complex sheets that don't change often, and you don't want them to change often
  • On personnel sheets with thousands of employees where a portion of each employee's salary depends on the total personnel spend for a department or discipline
  • On cube sheets which calculate ratios based on prior year sales or expenses

Enable Manual Recalculation in a Modeled Sheet

  1. Navigate to Modeling > Model Management.
  2. Click Level or User Assigned Sheets.
  3. Click Edit for an existing modeled sheet.
  4. Click Columns and Levels.
  5. Click Sheet Properties.
  6. Click the Settings tab in the Sheet Properties.
  7. Click Recalculate on Demand at the bottom of the dialog.
  8. Click OK.
  9. Click Save SheetViewer-Save.png.

The Recalculate Formulas button appears in a sheet's toolbar only if Manual Recalculation has been enabled for that sheet.

Enable Manual Recalculation in a Cube Sheet 

  1. Navigate to Modeling > Model Management.
  2. Click Level or User Assigned Sheets.
  3. Click Edit for an existing cube sheet.
  4. Click Dimensions, Attributes and Levels.
  5. Click Sheet Properties.
  6. Click the Settings tab in the Sheet Properties.
  7. Click Recalculate on Demand at the bottom of the dialog.
  8. Click OK.
  9. Click Save.

The Recalculate Formulas button appears in a sheet's toolbar only if Manual Recalculation has been enabled for that sheet.

Recalculate on Match

In the modeled sheet builder you can determine which rows recalculate when a single row is changed by selecting the Recalculate on match option for dimensions or text selector columns. Any time a row on a modeled sheet is edited, that row will be recalculated along with all other rows that have matching values in the columns with Recalculate on Match enabled.

If no columns have Recalculate on match, then only the edited row recalculates.

Recalculating All Rows On a Modeled Sheet

When a sheet uses manual recalculation it does not stay updated after changes to the rest of your model. Clicking Recalculate Formulas for a manually recalculated sheet recalculates formulas on all rows on the selected level and brings them back into sync with the rest of your model. To recalculate across all levels, navigate to the root level and click the Recalculate Formulas button there.

You may need to recalculate in an isolated sheet to pick up changes in other sheets, or in other rows within this sheet. An example of this could be having a salary cap that only needs to be applied at the end of the quarter.

Adding and Deleting Splits or Rows on Modeled Sheets

Similar to editing a row, adding a row triggers a recalculation on that row. Deleting a row does not force a calculation on other rows unless they contain a Recalculate on Match dimension.

Splits of rows are treated the same as rows when recalculating on demand. However, adding a split does not trigger a recalculation of its parent.

Cube Sheet Rollup Cells

Rollup cells in cube sheets are always calculated automatically because they are not calculated using formulas.

Recalculate All Sheets

To recalculate all of the cube and modeled sheets in your instance at once, navigate to Modeling > Model Management > Level Assigned Sheets and click Recalculate All Sheets.

  • Was this article helpful?