AX2066

Axiom query design considerations

This topic details some general design considerations for Axiom queries.

Processing order of Axiom queries

Axiom queries are processed in the following order:

  • When refreshing the entire file, sheets are processed in the order listed on the Control Sheet, from left to right.

  • Within a sheet, by default Axiom queries are processed sequentially in ascending order by Axiom query number (AQ1, then AQ2, and so on.). If batch numbers are used, then processing works as follows:

    • If a batch number is defined for an Axiom query, then all queries with the same batch number are processed in parallel before any other Axiom queries on the sheet. If multiple batch numbers are specified, the batches are processed in ascending order (batch 1, then batch 2, and so on).

    • After the batch queries are processed, the remaining queries on the sheet are processed sequentially in ascending order as normal.

    For more information on using batch processing, see Batch processing for Axiom queries.

It is important to understand this order when setting up configurations such as nested queries (where one query is built out by a prior query), or configurations where the settings of one query are impacted by the results of a previous query.

Refreshing Axiom query settings between queries

By default, when a file is refreshed, all of the Axiom query settings are read once at the start of the process. If a query setting is dynamic and changes during the processing, that change will not be recognized until the next time the file is refreshed.

If you want an Axiom query setting to be dependent on the results of another Axiom query, you can modify this behavior so that the Control Sheet is calculated after each query is run, and each query's settings are read individually before the query is run. To do this, set Refresh Control Sheet between every AQ to On. This setting is located on the Control Sheet, in the Data/Zero Options section.

For example, you might enable this setting if:

  • You want one query to define a data filter for a subsequent query.
  • You want a query to be enabled or disabled based on the results of a prior query.

You should only enable this option if you have query settings that are dependent on the results of other queries. Enabling this option can introduce many additional calculation cycles to the refresh process, which may impact performance.

Data security considerations

Each user's table filters from security are applied when an Axiom query is refreshed. However, before the refresh occurs, the file will display whatever data was last-saved in the file, such as from a previous user refreshing the query and then saving the file.

To prevent users from seeing Axiom query data that was inadvertently saved in a report, you should configure the file using one (or both) of the following options:

  • Zero AQs on Save: If this option is enabled, then when the file is saved the Axiom query data is converted to zeroes. The next user to open the file would see zeroes until a refresh occurs.

    This option can be configured on a per sheet basis using the Sheet Assistant, in the Worksheet Settings section. On the Control Sheet, it is located in the Data/Zero Options section, and is named Convert Axiom Query results to zero on save.

    If the refresh behavior for the query is Update Only or Insert and Update, then you must also enable Zero on Update in order to update the data with zeroes on save.

    		  

    NOTES:  

    • The refresh behavior Refresh during document processing must be enabled in order for an Axiom query to zero on save. This behavior is enabled by default.

    • If some Axiom queries use the refresh behavior Refresh after save data, then the queries will be zeroed before the file is saved. After the file has been saved, the queries will be refreshed.

    • If an Axiom query has an assigned batch number, that query is not zeroed on save.

    • If an Axiom query uses Insert Only refresh behavior, then the query will be zeroed on save but that zeroed data will never be updated. Either the zero on save option should not be enabled for the sheet, or the query should use a different refresh behavior (such as Rebuild or Insert and Update).

  • Refresh on Open: If this option is enabled, then when the file is opened the Axiom query automatically refreshes (and therefore the user's table filters are applied). In this case it doesn't matter if the prior user's data was saved within the file, because the data will be immediately replaced with the current user's data when the file is opened.

    This option can be configured on a per Axiom query basis using the Sheet Assistant, in the Axiom Queries > Refresh Settings section. On the Control Sheet, it is located in the Axiom query settings, in the Refresh behavior section.

This consideration does not apply to plan files in most cases. Generally, plan files are designed to be refreshed by an administrator (or other power user) and then that data is saved within the file for planning purposes. Access to the plan file is then restricted to only those users who need to work with that data.