Query
Daigo offers a built-in query builder to achieve no-code data query.
Becasuse every kind of data source has its unique data format / structure and query interface, Daigo abstracts a common data query logic based on SQL and provides a unified query editor. As a result, no coding is required for common data query tasks. For complex data processing tasks, it's always recommended to use ETL tools to pre-process the data.
How queries work#
In widget editor -> data tab, you will be prompted to set the following things:
- Dimensions
- Metrics
- Filters (Optional)
- Order By (Optional)
Based on your inputs, a query is constructed behind the scene and results data set will be returned in a tabular format (also displayed in the previewer).
When there are valid query results returned, the selected widget will look for data value from dimensions and metrics columns.
For example,
A line graph takes dimension as its X axis, and metrics to plot the line.
A donut chart takes dimension as its labels, and metrics to divide it.
Now let's look at how each option is used.
Dimensions#
Dimension is used to categorize the data. When applied with metrics, the query measures the aggregated value for each category.
For example, a dimension can be date, product type, city, etc.
When you set dimensions to "date", and metric to "value", the results data will consist rows with each available date as an entity followed by calculated values in the next columns.
SQL
Dimensions is equivalent to group by clause in SQL.
Metrics#
When data is aggregated, metrics is represented by the calculation the group of data.
- Sum
- Min
- Max
- Average
SQL
Metrics is equivalent to select clause in SQL.
Filters#
A filter comes handy when data set is too big to fit in a single view, or only a subset of data is analyzed.
There are two types of filters available:
- Range - Applicable to number attribute.
- Multiselect - Applicable to text attribute. You can either filter in (equal to) or filter out (not equal to) certain value.
SQL
Filters is equivalent to where clause in SQL.
Order By#
To sort the data by value of dimension, set it to ascending or descending for certain field.
SQL
Order by is equivalent to order by in SQL.
Query results explained#
Let's talk about how a query can get the results you want.
| Dimension | Metric 1 | Metric 2 | ... | Metric N |
|---|---|---|---|---|
| A | metric1(A) | metric2(A) | ... | metricN(A) |
| B | metric1(B) | metric2(B) | ... | metricN(B) |
| C | metric1(C) | metric2(C) | ... | metricN(C) |
| ... |
This is how one dimensional query result looks like. Usually, the first column is defined by dimension, and the second to the last columns are defined by metrics.
A widget will always look for values from dimensions and metrics by default. Depending on the type of widget, you have the control over which metric should be used to displayed, etc.
Multiple dimensions#
More than one dimension can be set in a query.
However, not all widgets can display more than one dimension in a single view, in which case the 2nd or 3rd dimension are usually represented in a selection or a filter dropdown menu.
Two dimensions - Results will be transformed into a pivoted table. A result data set looks like this:
| Dim 1 \ Dim2 | alpha | beta | ... |
|---|---|---|---|
| A | metric(A, alpha) | metric(A, beta) | ... |
| B | metric(B, alpha) | metric(B, beta) | ... |
| C | metric(C, alpha) | metric(C, beta) | ... |
| ... |
Please note this is different from how a SQL generates results with multiple group by conditions.
Three dimensions - Results will be transformed into a pivoted table with a selection of a 3rd dimension.
Think about adding one more dimension to the two-dimensional cases, a three-dimensional query will get you multiple tables like above.
However, since most widgets can only display two dimensional results, a proper way to use a three-dimensional result data is to switch the 3rd dimensional value to show corresponding results.
| Dim3 = 1 \ Dim 1 \ Dim2 | alpha | beta | ... |
|---|---|---|---|
| A | metric(A, alpha) | metric(A, beta) | ... |
| B | metric(B, alpha) | metric(B, beta) | ... |
| C | metric(C, alpha) | metric(C, beta) | ... |
| ... |
| Dim3 = 2 \ Dim 1 \ Dim2 | alpha | beta | ... |
|---|---|---|---|
| A | metric(A, alpha) | metric(A, beta) | ... |
| B | metric(B, alpha) | metric(B, beta) | ... |
| C | metric(C, alpha) | metric(C, beta) | ... |
| ... |