Parametric Query designing with the new Query Widget

In the past tutorials, we have seen how to do analysis by building a dataset through a Graph or a Table widget and attaching some charts to it, where users can compute and visualise distributions based on specific series data.
These series basically rely on the faceting information extracted from the datasource limited to the partial dataset built in Arcade through the correspondent primary widgets. You can also compute distributions according to the whole datasource’s dataset, but this feature is not the main topic of this post, then it will be set aside.

A dataset consists of:

  • data: records retrieved from the datasource. These records will be propagated to the connected chart widgets and used to build series as stated in the specific chart widget
  • metadata: information about the datasource model

Here comes the new Query Widget that allows the users to fetch data from the datasource to define a reference dataset by defining a query as complex as needed, including for example functions, aggregations, grouping and order clauses.

Once you have built a custom dataset, you can attach different charts to it, specify meaningful series and visually inspect relevant data distributions from your datasource.

Moreover, you can:

  • build a parametric query and dynamically change the actual values in the query to examine how your analysis evolves
  • run the query every time you need to get fresh data from the datasource and keep your analysis synchronised with it. You can do that manually or by defining an auto-update temporal window

In this article, I will show you how to use the Query Widget in a hypothetical use case where we want to study the orders made by the customers in a specific time window.

First thing, we have to create a new Query Widget in a dashboard through the dedicated Add new Widget button at the top right. In the opening popup we have to choose a name for the widget, select query as widget type and the datasource we want to connect with.

 

 

An empty widget will appear in the dashboard as shown in the screenshot below.

 

The Query Widget offers two working modes:

  • read mode: allows the user to run query inside the dashboard and change the actual values to keep the attached charts coherently updated and synchronised with the source
  • design mode: allows the user to build the query, complex as needed, state parameters and choose additional query execution options

To configure the widget we need to switch into the design mode by opening the widget through the dedicated expand button in the widget panel header.  The empty Query Widget looks as follows.

 

 

Let’s focus on the query tab on the right and suppose that in our analysis we want to inspect all the orders of the first quarter of 2019. More specifically, we want to investigate the amounts of these orders, counting their occurrences and the total profit they brought, focusing just on the amounts and the total profits above two specific thresholds, functional at a hypothetical business study.

Moreover, we want to do that by designing a parametric query: in this way the read user, who can use the widget in read mode but not in design mode, is able to dynamically change the thresholds and other potential conditions’ values from inside the dashboard in order to inspect the global analysis evolution.

Here an example of a query that could figure out our problem.

 

As you can see parameters are automatically identified through the ‘:prefix and for each of them a dedicated parameter box is added in the bottom area. In each box, you can edit the parameter to allow the read user to specify different actual values.

There are three types of parameters:

  • Free Text Parameter: allows the user to insert free text values, like strings, dates or numbers.
  • Single Value Parameter: allows the user to choose a single value as an actual value from a set of possible values. This set, that is our domain, must be specified by the user.
  • Multi-Value Parameter: allows the user to choose a list of values all at once as actual values from a set of possible values. This set, that is our domain, must be specified by the user.

The right parameter type choice just depends on the specific needs of the user and the query’s logic: for example amount and minTotalProfit are thresholds, then we will need to put just a single value.

Let’s suppose we want to choose a threshold for the amount parameter from the domain of all the actual amounts in the datasource; then we have to select Single Value as parameter type and build the domain by exploiting the faceting.
For this purpose, we choose the specific domain definition as shown below.

 

Then we can browse all the amounts by selecting Orders as class and Amount as property.

 

Eventually, we set a default value among those present in the reported list.

We want to fulfil the minTotalProfit parameter with free text: we can do that by choosing the Free Text parameter type

 

 

and setting our desired default threshold.

 

 

To narrow down our analysis to the orders of the first quarter of 2019, we could use a where condition on the date field, but to introduce the last domain definition we will follow a subquery approach, by filtering the orders of the external query with the set of the orders’ ids of the desired temporal window.

Then we choose Multiple Values as parameter type and a dynamic domain definition through the query.

 

 

Then we have to write down the subquery and run it.

 

 

As soon as the result set is retrieved, we can select some or all the values of the query-built domain.

Once we have configured all the parameters, we are ready to run the query to get our dataset.
Below is reported the tabular view of the outcoming dataset rows (30 rows in total).

 

Let’s save the Query Widget via the dedicated save button at the top right in the query tab and come back to the dashboard.

Now we have a designed parametric query and a dataset, we can finally start to play with some charts.
Let’s create a new pie chart choosing the designed query widget as a source.

 

 

In this way the Query Widget will work as the primary widget and the pie chart as secondary as follows:

  • primary query widget: allows the user to fetch data from the datasource in order to define and update the reference dataset.
  • secondary chart widget: allows distribution computation over the dataset defined and passed by the primary widget is connected with.

Once opened the new pie chart widget, we choose a category and a value to state the target series, where:

  • Category is the column of the flat table you want to use to categorise data in your dataset.
  • Value is the column containing the quantity that you want to inspect in your analysis.

In the pie chart, we will investigate the amount – amount occurrences distribution, then we will set the parameters as follows.

 

Below the visual outcoming distribution:

 

 

In the same way, we create in the dashboard a bar chart attached to the query widget

 

 

And we set it as shown below in order to investigate the amount – total profit series.

 

 

Now our dashboard is complete and we can quickly act on the query to change or keep updated the analysis.

 

 

For example, let’s suppose we want to focus on the first 15 amounts by total profit: we just have to change the designed query by adding a query limit, run again the query and save the new widget status.

 

 

The new dataset, once back to the dashboard, will be propagated to all the connected widgets.

 

 

The charts will be automatically updated at dashboard loading.

 

 

Finally, we can expand the query widget panel and select different values for each parameter in the query.

 

 

Read user can update the analysis in two ways:

  • manually: use the update play button to run the designed query and update your data every time you need
  • auto-update: schedule your query execution through the auto-update feature. Use the flag to refresh data according to the designed query and state the time interval by specifying the desired number of seconds in the dedicated form

I hope this post will help you to approach this new feature in Arcade Analytics to build powerful and fascinating dashboards.

Stay tuned for the next news and updates!

Gabriele Ponzi
Software Engineer