Direct Execution
With this mode, you can directly execute the Query on the original Datasource, without any storage. In this case, when a widget is displayed, it will fetch the data in real time from the underlying Datasource.
Note: The loading time under Direct Execution is directly proportional to the time it takes for the Query to execute.
For direct queries against your Datasource, you can enable the cache option for a given Query with parameters to cache the results for the specified period of time after the initial load which results in the following advantages:
- If your Queries are slow, it can reduce the load on your database
- Faster performance and improved user experience after the first load
Note: If runtime parameters are used, it'll cache for each combination of parameters.
Non-Direct Execution
For non-direct queries, results will be stored in Knowi's Elastic Store. This is useful in the following scenarios:
- Long-running queries
- Reduce load on your database for reporting workloads by offloading it to the ElasticStore
- The results can be used as a parent dataset to other derived queries off this dataset. Useful in cases where the resulting dataset tracked in the ElasticStore runs into the millions of records
- In cases with large raw datasets in the underlying database, this can be used to incrementally update the ElasticStore for that dataset without running the entire historical query
With ElasticStore usage, Overwrite Strategy will provide you control on how the data is updated for the dataset when the Query is run. The values can be one of the following:
|
Overwrite Strategy |
Execution |
|
Upsert |
Selecting this will replace the existing values for the same key with the latest and insert new records where such keys do not exist. |
|
Replace All |
Selecting this will replace all data in the current dataset with the latest run. |
|
Replace All - Include Empty |
Same as Replace All, except that in cases where no data is found for the query run, it will update the dataset with no data (whereas Replace All will not update) |
|
TTL - Time-Based Retention |
Selecting this will skip the records beyond a specific time period for each run. Example: DateField-3m will keep any records where DateField is within 3 months and will drop all prior records for each run. |
|
Append |
Selecting this will leave the existing data as it is while adding new records for each run. Caution: This is not common and you may end up with duplicates if data is the same. |
Non-direct execution can be enforced if you choose to run the Query once or at scheduled intervals.
Run Once
Select this option to execute the Query once.
Key: Key to base the replacement strategy where you want to replace/insert data based on Keys (for Upserts and TTL - Time-based Retention) use cases. For any same key-value found for the current query run, it'll update values in the existing dataset with the new values. For new values, it'll insert new records. Please note that Key is case-sensitive.
Execute Query at Scheduled Intervals
Select this option to execute Query at scheduled intervals. You can define the execution frequency in terms of minute(s), hour(s), day(s), and month(s) and the records shall be updated based on your selection.
Overwrite Strategy: You can define Overwrite Strategy to store the results into Knowi's ElasticStore and leverage on the benefits as discussed above. You can choose to ignore defining Overwrite Strategy if you want to append the data to the existing dataset.
Key: Key to base the replacement strategy off. For any same key-value found for the current query run, it'll update values in the existing dataset with the new values. For new values, it'll insert new records. Please note that Key is case-sensitive.