AX1562

Refresh data only when dependent tables have been modified (time-stamped Axiom queries)

You can configure an Axiom query so that it will only refresh data if one or more dependent tables have been modified since the last execution of the query. If the dependent tables have not been modified, then the query will not execute and the existing data in the sheet will be left as is. This feature is known as time-stamped Axiom queries.

This configuration is primarily intended for Axiom queries that bring in supporting data for the file, where this supporting data does not change frequently. Eliminating the automatic execution of these queries when the table data has not changed may improve file performance.

By default, the dependent table is the primary table for the query. You can also optionally specify additional dependent tables when enabling the time-stamped refresh behavior.

Configuring a query as a time-stamped query

To configure an Axiom query as a time-stamped query, use the following settings. These settings can only be viewed and modified on the Control Sheet, in the Refresh behavior section of the Axiom query settings.

Item	Description
Refresh only if primary table changed since last refresh	Specifies whether the query uses time-stamped refresh behavior: If On, then the query will only run if dependent tables have been modified since the last time the query was refreshed. The dependent tables for the query are the primary table and any additional tables listed in the Additional table dependencies field. The query must also meet the following requirements: The primary table of the query must be explicitly defined in the query settings, not inferred from field definition entries. The sheet-wide setting Convert Axiom Query results to zero on save must be disabled for the sheet. If it is enabled, then the Refresh only if primary table changed since last refresh setting will be ignored, and the query will zero and refresh as normal. If Off (default), then time-stamped behavior does not apply and the query will run whenever it is eligible to refresh.
Additional table dependencies	Optional. One or more additional tables to consider when using time-stamped refresh behavior. Only applies if Refresh only if primary table changed since last refresh is enabled. If a table is listed here, then the query will run if that table has been modified since the last time the query was refreshed (regardless of whether the primary table has been modified). You can list multiple table names, separated by commas. It is not necessary to list the primary table here, as the primary table will always be considered.
Last refresh time	The date and time that the query was last run. The last modified date-times of the dependent tables are compared to this date-time to determine whether the query should be run. The last refresh time is automatically stamped in this field when the query is run. This date-time is written in UTC (Coordinated Universal Time) to ensure consistent date-time comparisons—the date-time is not converted to the local time of the client where the refresh occurred. See the following section, How time-stamped queries work, for more information.

Item

Description

Refresh only if primary table changed since last refresh

Specifies whether the query uses time-stamped refresh behavior:

If On, then the query will only run if dependent tables have been modified since the last time the query was refreshed. The dependent tables for the query are the primary table and any additional tables listed in the Additional table dependencies field. The query must also meet the following requirements:
- The primary table of the query must be explicitly defined in the query settings, not inferred from field definition entries.
- The sheet-wide setting Convert Axiom Query results to zero on save must be disabled for the sheet. If it is enabled, then the Refresh only if primary table changed since last refresh setting will be ignored, and the query will zero and refresh as normal.
If Off (default), then time-stamped behavior does not apply and the query will run whenever it is eligible to refresh.

Additional table dependencies

Optional. One or more additional tables to consider when using time-stamped refresh behavior. Only applies if Refresh only if primary table changed since last refresh is enabled.

If a table is listed here, then the query will run if that table has been modified since the last time the query was refreshed (regardless of whether the primary table has been modified). You can list multiple table names, separated by commas. It is not necessary to list the primary table here, as the primary table will always be considered.

Last refresh time

The date and time that the query was last run. The last modified date-times of the dependent tables are compared to this date-time to determine whether the query should be run.

The last refresh time is automatically stamped in this field when the query is run. This date-time is written in UTC (Coordinated Universal Time) to ensure consistent date-time comparisons—the date-time is not converted to the local time of the client where the refresh occurred.

See the following section, How time-stamped queries work, for more information.

Example Axiom query with time-stamped behavior enabled

How time-stamped queries work

Time-stamped queries use the Last refresh time for the query on the Control Sheet. Each time an Axiom query is run, the date and time of the refresh is written to this field. If Refresh only if primary table changed since last refresh is enabled, then before the query is executed, Axiom compares the last refresh date-time of the query to the last modified date-times of the dependent tables:

If no dependent tables have been modified since the last refresh of the Axiom query, then the query is not refreshed and the existing data in the sheet is left as is.
If any of the dependent tables have been modified since the last refresh of the Axiom query, then the query is refreshed.
The dependent tables for the query are the primary table and any tables listed in the Additional table dependencies field.

The Last refresh time field is system-controlled and should not be manually modified. The only exception is that you can clear the field if you want to "reset" the last refresh time of the query. If needed, you can clear the field manually, or you can use the Clean Document tool on the Axiom Designer ribbon tab.

NOTES:

The last modified date-time of tables reflects both data changes and structure changes. The query will also refresh if the structure or properties of any dependent tables have changed.
By default, the Last refresh time field is not populated when refreshing Axiom queries in a template file for a file group. This is because when you create plan files from the template, the field must be blank so that it can be populated for each individual plan file. However, if you are using virtual plan files and therefore you want to maintain a time stamp in the template itself, you can use the Process Template List task for this purpose. This task runs Axiom queries set to Refresh during template processing and then time-stamps them for use in virtual plan files. For more information, see Scheduler task: Process Template List.

The time-stamped refresh behavior is honored by all Axiom query refresh settings (manual refresh, file open, after save data, etc.). If you enable this feature for a query, and then you later change something about the query—for example, if you add a column to the field definition, or you change a formula in the in-sheet calc method—then you should clear the last refresh time of the query so that the query will execute using the new query settings. Otherwise the query will not execute again until a dependent table is modified.

Design considerations

Generally speaking, the time-stamped query feature is only a good fit for queries that meet the following criteria:

The Axiom query settings are static. Query settings do not use formulas to dynamically change based on other factors such as refresh variables. The exception would be an Axiom query in a plan file that uses a formula to filter the query based on the current plan code, because in that case the filter will always be the same for that plan file.

For example, you would not want to use this feature with a query where the filter changes based on a refresh variable selection, because the query would not refresh to reflect the changed filter.
The data in the dependent tables is mostly static. Modifications to the table data are rare. If the query has to run each time the file is opened because the dependent tables keep getting modified, then this configuration does not provide much benefit.
The data in the dependent tables is available to all users who will access the file. The users do not have security filters that restrict access to the tables. If the users who access the file have different security filters on the dependent tables, then the query needs to always run to ensure that the current user's filter is applied to the query.

If you enable this feature for a query that does not meet these criteria, the setting may not have any benefit and/or the query results may not be as expected.

NOTE: This feature is not supported for use with file processing. Specifically, it should not be enabled for any queries that are executed for each pass of file processing. It could be enabled for a query that runs on file open to provide supporting information for the file, but otherwise the query is not executed during file processing.