Data Queries

With Data Queries, data from external databases can be prepared for the use in dashboards.

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 and rolled back
Jump directly to dashboards consuming a data query and from a dashboard back to the data query

This section is only available for users with administrator privileges

See Data Query Examples for some useful examples.

Create Data Queries

At least one data source must be configured in SKOOR for the 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

Select the data source from the respective drop down menu and 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 at 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.

As soon as the query is ready for use in a dashboard, it ca be published. 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 questionmark to see the description given at publish time:

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 significanty increase the performance to access the data from dashboards. It is always a good idea to create indices of the key database attributes.

Setting	Description
Add Group	Add groups for associated queries
Add 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
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

Delete queries or groups

Click the gear icon of a group or query and click Delete.

Move queries between groups

Queries can be moved between groups by drag & drop.

Duplicate queries

Queries can be copied easily by clicking Duplicate in the context menu.

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 behaviour. 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

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.

On the target system, the data sources configured in the data queries must exist

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 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

Right click a group on the target system and click Import. Paste the exported string into the respective field of the import dialog. After a validation, the import actions will be described on screen:

If the actions are ok, click Import Data Queries.