AX1471

Data lookups

Data lookups are data queries that can be configured to execute at specific times. They can serve as a regular data refresh option, or they can be used to bring in "static" points of data that do not need to be continually refreshed while the user is working in the file.

Data lookups are created by using a DataLookup data source. Each row in the data source defines the parameters for a value that you want to return from the database. When the query is executed, the value for each row is returned into a designated column of the data source. These values can then be referenced in the file just like values returned via Axiom functions. Currently, data lookups can be used to return the same data as the following Axiom functions:

  • GetData
  • GetFeatureInfo
  • GetFileGroupProperty
  • GetFileGroupVariable
  • GetFileGroupVariableEnablement
  • GetFileGroupVariableProperty
  • GetPlanItemValue
  • GetSecurityInfo
  • GetTableInfo
  • GetUserInfo

Data lookups support a variety of execution options to meet various needs. They can be configured to run automatically whenever a file is refreshed, or they can be configured to run only at specific times—such as on file open, or after a particular Axiom query is executed. You can also optionally expose the ability for end users to execute data lookups as needed, using the Execute Data Lookups command.

To configure a file to use data lookups, you must:

  • Define one or more DataLookup data sources in the file.
  • Configure the data lookup execution options as needed, so that the query executes when you want it to.

When to use data lookups

Data lookups are primarily intended to be a substitute for Axiom functions. For example, plan files typically need to query certain "static" points of data, such as the current plan code and description, which are then referenced throughout the file. Once a plan file is opened and these values are queried from the database, it is not necessary to refresh the values again during the current file session, because the plan code and description will not change. If you use Axiom functions to return the values, the functions will continue to execute a data query each time the file is refreshed or each time the cell is forced to calculate. However if you use a data lookup to return the value, then the query can be configured to only execute when you tell it to—in this example, when the file is initially opened.

Whenever possible, data lookups should be used instead of Axiom functions to bring in this type of data, for performance reasons. Data lookups have the following advantages over Axiom functions:

  • Execution of data lookups can be tightly controlled, unlike the behavior of Axiom functions. Axiom functions are updated each time the file is refreshed, and also any time the cell is forced to calculate using standard spreadsheet calculation behavior. If the values returned by these functions are not expected to change, then this processing overhead is unnecessary and can be eliminated to help improve file performance.

  • The database query used by data lookups is more efficient than Axiom functions, greatly reducing the database traffic required to return each value.

In some cases it is also possible to use a "refresh on open" Axiom query to return the desired data. For example, you can use an Axiom query to return values from a driver table, where the driver values are unlikely to change during the current file session. If the "manual refresh" behavior is disabled for the query, then the query will only run once when the file is opened; it will not execute again on subsequent refreshes. If it is possible to return the data via Axiom query, then that is generally the recommended approach.

However, sometimes you need to return a set of disparate data points that cannot be easily returned using an Axiom query, because the data cannot be joined. In this case, data lookups provide a flexible alternative. Each row in the DataLookup data source can query any type of data; the rows do not have to be related. One way to think of it is that each row in the data source corresponds to an Axiom function and can return any data that the Axiom function can.

AX1471