Dashboard Widgets Based On a SQL Connection

Metrics in SQL connection

Dashboards utilize two main UI elements: tables and widgets. Tables display datasets featuring multiple columns, with data originating from various sources.
Widgets, on the other hand, display single metrics. The data source for widgets must include a metric.

To clarify, datasets contain dimensions and metrics. Dimensions are typically string or date/time data, while metrics are numerical values that can be aggregated.
To use widgets, you need metrics. Where do you find them?

When you first connect SQL data, Conduit automatically categorizes all columns as dimensions. If you attempt to create a widget at this stage, you will encounter a message stating 'No metrics found'

Step 1

The initial step is to define metrics. Start by accessing the UI SQL connection terms.

Choose the "Metric" button, make sure you set the correct data type and click on the 'Save' button

Step 2

Once defined, the column can be used in Workflow, and it will be recognized as a metric:

Step 3

We are now ready to create a widget using this metric.

Converting non-integer columns to metrics

If you encounter an error message during step 1 that reads "Column 'Started Free Trial' has incorrect type, consider changing it to dimension," this is likely due to the SQL type of the column not being an Integer.

In such instances, you'll need to employ a forced data type conversion within your SQL expression.

For instance, if you're using the Boolean type to track steps in a Funnel, you can convert a Boolean to an integer with a CAST expression. The specific syntax for this conversion varies based on your SQL server.

Example:

CAST(registered AS INTEGER) AS "Registered"

After executing this, click the "Run SQL" button once more. You should now be able to change the column type to Metric without errors.

Manual updating of workflows metadata

When you modify columns or their types in the SQL connection, these changes do not automatically reflect in Workflows and Dashboards.
This means you will want to rebuild the SQL select block after altering column types.

Updated on: 30/04/2024