Designing Parametric Queries With the New Query Widget
Designing Parametric Queries With the New Query Widget
Learn how to use Arcade Analytics to build powerful and fascinating dashboards.
Join the DZone community and get the full member experience.Join For Free
In 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 visualize 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 corresponding 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.
A dataset consists of:
- Data: records retrieved from the data source. 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 orderclauses. 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 synchronized 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 data source with which we want to connect.
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 queries inside the dashboard and change the actual values to keep the attached charts coherently updated and synchronized with the source.
- Design mode: allows the user to build the query (making it as complex as needed) and state parameters, and to 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's an example of a query that could figure out our problem.
As you can see, the 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, if
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 data source; then we have to select Single Value as a parameter type and bcan uild 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 the class and
Amount as the 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 we can set 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 the tabular view of the outcoming data set 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 data set, 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 will be the secondary widget, 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 with which it is connected.
After opening 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 data set.
- 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 when the dashboard loads.
Finally, we can expand the query widget panel and select different values for each parameter in the query.
The 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!
Opinions expressed by DZone contributors are their own.