Skip to main content

Cache Optimizer

The Cache Optimizer analyzes the references and NecAccess formulas in your Excel file and produces a report with recommendations on which dimensions to add, remove, or keep in each reference. Applying these changes directly affects how the cache is built, which has a direct impact on calculation speed and refresh times. Before using the Cache Optimizer, make sure you understand how references and caching work in the Add-in.

Excel Add-in Cache Optimizer

How the cache optimizer works​

The optimizer runs in two stages:

  1. Analyze — Scans all references and NecAccess formulas in your Excel file.
  2. Recommend — Produces a report with actionable changes to improve cache efficiency.

Run an analysis​

Before running the optimizer, make sure you are connected to your Central Point and that the Excel file you want to analyze is open. The Cache Optimizer only generates recommendations when the Excel file contains Add-in references and NecAccess formulas.

  1. Open the Add-ins tab and select Cache Optimizer.
  2. Enter a name for the optimization report sheet and click Ok. The name must not match any existing sheet.
  3. Wait for the analysis to complete. During the process, you can:
    • Stop analysis — stops immediately. A partial report is generated only if at least one reference was fully analyzed.
    • Skip reference — skips the current reference and moves to the next one.
  4. When complete, a new sheet containing the report is added to your Excel file.

note

Analysis time increases with the size of your Excel file, particularly when it contains many references or large data models.

Apply recommendations​

The optimization report highlights each dimension with a color that indicates the recommended action.

  • Remove a dimension is highlighted in red.
  • Add a dimension is highlighted in red.
  • Leave a dimension unchanged is highlighted in green.

Apply changes​

  1. In the Change to apply column, select a cell and choose an action from the dropdown menu.
  2. Repeat for each dimension you want to update.
  3. Select Apply changes and recalculate at the top of the report to apply all changes at once.
  4. Track progress in the Change to apply column. When all cells turn green, the changes have been applied and are reflected in the references configuration.

important

The Apply changes and recalculate button works only if your UI language matches the language used when the report was generated. If the languages do not match, regenerate the report in the correct language before applying changes.