Skip to main content

References Configuration

A reference is an abbreviated name that defines a specific data model published for Excel, along with an environment where the data can be retrieved.

References also define the caching strategy used by the Add-in to improve performance for its cache system.

Create a reference for each data model you want to access using the NecAccess formula in your Excel file. If you want to access data from multiple data models or environments, you can creeate multiple references—there's no limit to the number of references in a workbook.

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

Understand references, cache, and performance​

References are used in a cache system to improve formula performance.

For optimal performance, configure fewer, broader references. A larger generated cache generally improves performance. Using one reference per data model is preferable instead of many smaller ones.

Performance may be negatively impacted when cached fields are used in the following scenarios:

  • Using exclusion logic such as Exclusion or NOT (e.g., Account NOT 40000)
  • Using alphanumeric ranges (e.g., Company between AAAA and BBBB)
  • Applying date filters (e.g., 2019-01-01)
tip

Test different reference configurations to find the most efficient setup.

See Cache Optimizer for tips on optimizing references and formulas.

Creating a new reference​

  1. Click Add to create a new reference or Edit to update an existing one.
  2. In the Data Model field, select the data model you want to access.
  3. In the Environment field, select the corresponding environment.
  4. Select one or more Dimensions where the values will 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.

The cache system analyzes formulas and tries to predict what other values may be needed. Selecting the right dimensions helps improve efficiency.


➤ Example – Calculate the sum of sales for a year
Suppose you configure a formula to calculate the Sum of Sales for a specific product for the Current Year.

The system may automatically:

  1. Pre-calculate the sum of sales for every product for the current year.
  2. Pre-calculate the sum of sales for the specific product across all years.
  3. Pre-calculate the sum of sales for every product for every year.

Although the system may choose option 3, it is the most expensive. You can guide the system for better performance:

  • If the report changes by Year, prefer option 2.
  • If the report changes by Product, prefer option 1.

Setting data model parameters​

You can set parameters without logging out. Similar to the web server, dynamic values can be set directly in the Excel Add-in.

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

  1. Select Set Data Model Parameters.
  2. Field names are based on the parameters defined in the data model.
    The Value column shows the default value. Change values as needed.
  3. To skip the parameter screen, click Skip.
  4. Click OK to apply. This clears the cache and refreshes the worksheet data. See Clear Cache for more information.

Executing stored procedures​

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

  1. Select Execute Stored Procedure.
  2. Field names come from the parameters of the stored procedure set in the web server.
    • Value (From) displays the default value. Update 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 a range is supported, the Value (To) column appears.
  4. To skip values, click Skip.
  5. Click OK to apply. This clears the cache. See Clear Cache for more infomation.