Offline Queries

Queries can be made offline to store query results in the workbook. When a query is offline, the data will not be refreshed from the underlying data source but will instead be taken from cached results of previous executions. Offline queries can be used for data sources that update very seldomly, and when it's beneficial to read older data instead of querying new data in each refresh.

Offline queries is particularly useful when troubleshooting reports as it allows you to view and modify a report without having direct access to the source systems.

🚧

Offline queries can result in very large files and should, therefore, be used with caution for large queries. Because the query does not get refreshed from the data source, any changes in the query configuration will not affect the result until the query is “online” again.

Creating an Offline Query

To make an offline query, follow these steps:

1. In the Query Manager, click on the Offline Query button.
2. The Offline Query Configuration lists all the queries in the workbook. To enable offline querying for a particular query, tick the Offline toggle next to it.

3. After enabling the offline query, you may then configure and view other options:
   • Cumulative Offline Query
   • Refresh Data and Refresh Time allows you to choose whether to refresh the query (from the data source) after a specified time. If set to Refresh Data = Refresh After, a time can be specified that defines how long the cached data should be kept. For example, if Refresh Time = 0d:1h:0m, the query will refresh every hour. If set to Refresh Data = Refresh Daily At, a time can be specified for when to refresh the data.
   • Last Executed and Size show the last time the query was refreshed from the data source as well as the size of the result.
   • Max Size and Max Size Action allows you to specify a maximum size limit of the query result as well as what action to use when this limit is breached. Actions can either be Disabled, result in a Warning, or result in an Error.
   • View launches a window that displays information about the stored data for the offline query such as last execution times, size, and parameters. It is also possible to delete the data if, for example, the query needs to be re-executed, by clicking the Delete button in this window.
   • Clear and Clear All Data clears/deletes the offline query data for the selected query or for all the queries. These may be used to update the cached offline data next time the query or queries are executed.
   • Select All and Deselect All checks and unchecks the Offline box for all queries, respectively.

4. When done with all the configuration, press OK. Apply saves the configuration but keeps the configuration window open.

📘

Note that disabling (unchecking) the offline query will remove the cached offline data.

Cumulative Offline Queries

Offline queries can be configured to use cumulative offline cache. This means that the result is stored with its parameters as key. If a parameter is changed, a new dataset will be stored (while keeping the previous data) when the query is refreshed.

You can view previously cached executions of an offline query and their corresponding parameters through the View button. In the example below, note that the date parameter changes for each line. As before, you can delete a dataset if you want the query to be re-executed for another set of parameters.