DocumentationData Queries
This section is only available for users with administrator privileges
In this guide:
Basics of Data Queries
With Data Queries, data from external databases can be prepared for use in dashboards.
Data Queries has the following advantages:
Development and testing of SQL queries directly in the SKOOR Dashboard admin section
Dashboard performance can be increased
by the use of parameters which leads to smaller result sets from the databases
by the use of the Materialize feature which automatically maintains a caching table of the query result on the database
Nested data queries are possible
Versions of queries can be published, older published versions could be reused.
Jump directly to dashboards consuming a data query and from those dashboards back to the data query
See Data Query Examples for some useful examples.
Interested in using Data Queries with Filter Matrix, check the details in: Filter Matrix & Data Queries
Create Data queries
At least one data source must be configured in SKOOR for use with data queries.
Click the three dots at the top left side of the screen to add a new query group or just add a data query to the default group:
Data Query names must be systemwide unique
The Create Data Query is shown, where the name of the Data Query and the Data Source it uses need to be set.
After clicking on Create, the Data Query is shown.
Enter an SQL query in the Query field. Check the box Accessible from dashboards if the data query will be used in dashboards:
Queries can be tested directly by pressing the Run query button on the right-hand side of the editor. Click Toggle filters to enable and use filters to search for specific records in the result set. In case of errors, hints or messages will be displayed in the editor or above.
Now the new query is ready to be published.
Publish Data queries
Queries can be published by clicking the Publish button as shown below. Unpublished versions of a query will not affect any dashboards.
Published versions of a query are marked in the version drop-down. Hover over the question mark to see the Publish note given at publish time:
Publishing a Data Query successfully will automatically delete all the unpublished versions of that query up to the publishing point.
Previoulsy published version are not effected.
Materialize Data queries
If the Materialize box is checked, database objects will be created to store the result set of the query as soon as the query is published. This will significantly increase the performance of accessing the data from dashboards. Creating indices of the key database attributes is always a good idea.
Delete Data queries
Click the three dots 
of a group or query and click Delete.
Move Data queries
Queries can be moved between groups by drag & drop.
Duplicate Data queries
Queries can be copied easily by clicking Duplicate in the context menu.
Rename Data queries
When a data query is opened, its name can be easily adjusted by typing in the new name in the Name field.
This will create a new version of the Data Query and publishing it is required to see the new name in the dashboards.
When renaming a Data Query that is nested within other Data Queries, the name change will propagate to all dependent queries. This propagation will result in new versions of those dependent queries, making them ready to be published.
List of settings in Data Query
Setting | Description |
|---|---|
Create Group | Add groups for associated queries |
Add data query | Add new query to a group |
Name | Name of the data query. This name can be used also for nested data queries |
Data source | Name of the data source used for this data query |
Accessible from dashboards | Data queries can be selected from widgets of dashboards when this box is checked |
Query | SQL language query |
Query history | Previously published versions of a query can be reactivated/published if needed |
Publish | Make the current query available to other data queries or dashboards. Unpublished queries do not affect data displayed in dashboards |
Dependencies | Shows subqueries, queries and dashboards that use the current data query. Mouse-over shows a list of the specific items which when clicked will jump to the corresponding dashboard. |
Materialize | Create database objects to cache result set of the query to increase dashboard performance |
Indices | Select key database attributes SKOOR should create indices for |
Refresh now | Refresh the result set cache (materielized data) |
Copy refresh link | Copy the link of the current materialized query. A SKOOR job can be created to perform refreshes using that link |
Run query | Runs and saves the SQL query and displays the result set in the Data Preview section |
Toggle filters | Switch on or off filter fields below the columns of the result set to search for specific records |
Nested queries
Data from other data queries can be queried or joined the same way as other database tables or views. The version of underlying data queries will be pinned to the current one so that changes will not lead to unexpected behavior. At the time a new version of a data query is created, underlying queries will be set to their newest versions as well.
The following data query selects a subset of the data query get_sales_data:
The dependency between the get_sales_data and get_tech_sales data queries is visible at the top right of both data query screens:
get_tech_sales query:
get_sales_data query:
Make sure to always check dependencies before changing and publishing queries
Export and import
Export
Data query groups can be exported from and imported to any SKOOR system. This allows the exchange of queries for instance between test and production systems.
Export dialog:
To finish the export, copy the export string or download it in JSON format. Check the Plain JSON checkbox to read the export string as JSON.
The following must be considered when exporting/importing Data Queries
On the target system, the data sources configured in the data queries must exist.
Data scopes and Write permissions of the used data source are exported/imported with the Data Query, for this to be imported successfully all the users/user groups used in the settings should be available in the target system. (This option can be opted out while importing)
Data queries will be updated by imported queries with the same name, regardless of the group they belong to
Version numbers of queries on the source and target systems do not necessarily match, they are increased independently
Published queries
By default, the last published version of each data query of a group will be exported.
On the target system, the following will be done when importing:
If the latest version of a query is unpublished or a different published version, the imported query of the same name will be added as an unpublished minor version
If the latest published version is the same as the imported one, a new published version of the query will be added.
Unpublished queries
To export the latest unpublished changes, export the required group with Include unpublished versions checked.
A new unpublished minor version of each imported query will be created on the target system.
Import
Select import data queries from the context menu of Data Queries or a specific Data query group.
Paste the exported string into the respective field of the import dialog. After validation, the import actions will be described on the screen:
The flag DataSource’s Permissions and Data scopes is used to optionally import the Permissions and Data scopes.
If the actions are ok, click Import Data Queries.