This section is only available for users with administrator privileges

In this guide:

Basics of Data Queries
Nested queries
Export and import
- Export
  - Published queries
  - Unpublished queries
- Import

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.

Setting	Description
Message	The message to display. Supports placeholders.
Search Type

Correlation Legend

Editing correlation legend widget