AX2262

Bringing data into Axiom files

Axiom Software supports three different ways to query data from the Axiom database and bring it into Axiom files, such as plan files and reports:

  • Axiom queries
  • Lookup data sources
  • Axiom functions

All three features can be used in any Axiom file, however, each feature has its own strengths and weaknesses, as well as a performance impact.

When choosing which feature to use, you should give preference in the order listed above. Axiom queries are most often the very best method for returning data and should be used whenever possible. If an Axiom query cannot be used for a particular use case, then a Lookup data source is the next best option. Axiom functions should only be used if the data cannot be returned using either of the previous two methods. For more information, see the summaries of each feature below.

Once a file has been set up to query data, various additional features can be used to further impact the data that is brought into the file, such as data filters, refresh forms, and data conversion.

The following summary explains the basics of each query method, to help understand when to use them.

Axiom queries

Description

Axiom queries are the primary method of bringing data into Axiom files. Axiom queries have many advantages over other query methods, including:

Axiom queries are set up using a combination of Control Sheet settings and tags in the sheet. When a refresh occurs, the resulting data from the query is brought into a designated data range within the sheet. Axiom queries can return data from any client-defined table—including returning data from multiple tables within the same query. Axiom queries can also return data from any system table (one table per query).

When to Use It

Axiom queries should always be the first option you consider when you need to bring data into a file. Axiom queries are highly efficient and very flexible to meet a variety of reporting and data query needs.

More Information

See Axiom queries.

Data Lookups

Description

Data lookups are data queries that can be configured to execute at specific times. These queries can be used to bring in relatively "static" points of data that do not need to be continually refreshed while the user is working in the file (as they would be if using Axiom functions). For example, in a plan file, data lookups can be used to bring in static data such as the department description and the file group name. This type of data usually does not change, and even if it does change, it is not essential to reflect that change in the current file session.

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.

Data lookups are defined using a DataLookup data source. Each row in the data source defines a data point to be queried and returned into the designated column of the data source. These values can then be referenced throughout the file as needed.

When to Use It

Data lookups are primarily intended to be a substitute for Axiom functions, to return specific points of data into a file.

Use of data lookups over Axiom functions can greatly improve file performance, because the database query used for data lookups is more efficient, and because the execution of data lookups is completely controllable.

Currently, data lookups can be used to return the same data as the following Axiom functions:

  • GetData
  • GetFileGroupProperty
  • GetFileGroupVariable
  • GetFileGroupVariableEnablement
  • GetFileGroupVariableProperty
  • GetSecurityInfo
  • GetUserInfo
More Information

See Data lookups.

Axiom Functions

Description

Axiom functions bring data into specific cells (the cell containing the function). They can be used directly within Axiom files to create fixed sections of data, or they can be used within calc methods and therefore populated into Axiom query data ranges. Axiom functions can be used in combination with Excel functions or with other Axiom functions (nested functions).

The primary function used to return data is GetData. GetData can return data from any client-defined table as well as most system tables. Each GetData function returns a single value into a single cell. This value can be a single data point in the table, or it can be the result of summing a set of data (or using other alternate aggregations such as Average).

Other functions are available to return certain specialized data, such as process and security information, and file group properties.

When to Use It

Due to their slower performance, Axiom functions should be considered as the "last resort" for a particular data query need. Function-based data queries are much less efficient than the other two query methods. The same data that can be returned in a single Axiom query execution may instead require thousands of individual data queries when using functions. This difference in efficiency quickly adds up as functions are used in a file, and can significantly impact the file performance.

Function-based data queries are also less controllable. Function updates may be triggered continuously as users work in the spreadsheet, causing dependent and volatile functions to fire and therefore impact file performance. In rare cases this kind of continuous update may be necessary, but in most cases it is not and a more controllable query method should be used instead.

More Information

See Axiom functions.

AX2262