Skip to main content

References Configuration

A reference is an abbreviated name that identifies a specific data model published for Excel, along with the environment from which the data should be retrieved. References also define the caching strategy used by the Add‑in, helping improve performance through efficient cache generation and reuse.

Create one reference for each data model you want to access using the NecAccess formula in your Excel file. If you need to retrieve data from multiple data models or environments, you can create as many references as needed—there is no limit per Excel workbook.

To open the References Configuration dialog, go the Add-ins tab and select References Configuration.

Understand references, cache, and performance​

References are part of the Add‑in’s caching system, which improves formula performance by reducing repeated data retrieval.

For optimal performance, configure fewer, broader references. A larger generated cache typically performs better than many small ones. In most cases, using one reference per data model is more efficient than creating multiple narrow references.

Performance may be reduced when cached fields are used with certain types of logic, including:

  • Exclusion logic such as Exclusion or NOT (e.g., Account NOT 40000)
  • Alphanumeric ranges (e.g., Company between AAAA and BBBB)
  • Date filters (e.g., 2019-01-01)
tip

Experiment with different reference configurations to determine the most efficient setup for your Excel workbook.

See Cache Optimizer for guidance on optimizing references and formulas.

Create a new reference​

  1. Click Add to create a new reference, or Edit to modify an existing one.
  2. In the Data Model field, select the data model you want to access.
  3. In the Environment field, choose the environment the reference should use.
  4. Select one or more Dimensions where values are expected to change.
  5. In the Reference field, enter a name and click Ok to save.
  6. Select the reference and click Ok to close the References window.
note

The cache system analyzes formulas and predicts which additional values may be needed. Choosing the right dimensions helps the system generate a more efficient cache.

Example – Calculate the sum of sales for a year​

Imagine you create a formula that calculates the Sum of Sales for a specific Product in the Current Year.

To speed up future calculations, the cache system may automatically pre‑calculate additional values. Depending on how your report behaves, it might choose one of the following strategies:

  1. Pre‑calculate sales for every product for the current year.
  2. Pre‑calculate sales for the selected product for all years.
  3. Pre‑calculate sales for every product for every year.

Option 3 covers all possibilities but is the most expensive and slowest to generate. You can guide the system toward a more efficient choice by selecting the right Dimensions when creating the reference:*

  • If the report changes by Year, option 2 is more efficient.
  • If the report changes by Product, option 1 is more efficient.

This helps the cache system focus on the values your Excel workbook actually needs, improving performance without sacrificing flexibility.

Set data model parameters​

You can set data model parameters directly in the Excel Add‑in without logging out. Just like on the web server, dynamic values can be applied immediately.

For example, you might set the value 7 for the Month parameter of the Budget Entry data model. The variable $$month will then be used inside SQL scripts in the Data Model Designer.

  1. Select Set Data Model Parameters.
  2. The fields displayed correspond to the parameters defined in the data model. The Value column shows the default value; update it as needed.
  3. To bypass the parameter screen, select Skip.
  4. Select Ok the changes. This clears the cache and refreshes worksheet data. See Clear Cache for more details.

Execute stored procedures​

Stored procedures can also be executed directly from the Excel Add‑in without logging out.

  1. Select Execute Stored Procedure.
  2. The fields shown correspond to the parameters defined for the stored procedure on the web server.
    • Value (From) displays the default value. Update it as needed. Required parameters are marked with an exclamation icon.
    • Use the prompt icon to open the Prompt dialog and choose from a list of values.
  3. If the stored procedure supports ranges, a Value (To) column appears.
  4. To skip values, click Skip.
  5. Select Ok the changes. This clears the cache and refreshes worksheet data. See Clear Cache for more details.