Adding Custom Fields to Model

Each data model contains fields (similar to data table and their respective columns). There are 3 types of fields:

  • Base Dimension: the "original" fields in your source data.
  • Custom Dimension: created by using non-aggregate functions to transform one or multiple dimensions.
  • Custom Measure: created by using aggregate functions (SUM, COUNT, etc)

Base Dimension

Base Dimensions are the "original" dimensions that you got when you first create your model. Think of this as, when you "load" your database table into a model, the table's columns become base dimensions.

Base dimensions can only be hidden, and cannot be removed via the UI.

Adding custom dimensions to data model

To extend on base dimension, you can create Custom Dimension by supplying a formula. Custom dimensions are created using SQL syntax with non-aggregate operations (e.g CASE WHEN, CONCAT(), field_a + field_b, etc)

For example, given a birth_date field, you can create custom dimension age to determine how old a user is.

Model users {
type: 'table'
label: 'Users'
table_name: 'public.users'
// .. other basic fields
dimension birth_date {
label: 'Birth Date'
type: 'datetime'
definition: @sql{{ birth_date }};;
}
dimension age {
label: 'Age'
type: 'number'
definition: @sql {{ date_part('year', current_date) -
date_part('year', {{ #THIS.birth_date }}) }};;
}
}

To add a custom dimension:

  1. Go to the model view UI, click Add -> Calculated Column,
  2. Input field name and SQL formula.
  3. Click Create, and a new column named will be created

calculated fields

I can also make use of my aforementioned fields by creating another field named Age Group based on their age:

calculated field

Notes:

  • When you use Persistence setting, Custom Dimensions will not be persisted to the database.
  • You can use your SQL DB's equivalent of the CAST() operation in a custom dimension to create a new field with the new datatype.

Adding measures to data model

Measures are similar to Custom Dimension, except that they're created using only aggregate functions/operations. Think of Measure as the aggregate expression of your typical SQL statement.

Similarly, I can create a simple Measure on contestants model to count the number of winners:

measures

Or something more interesting, like the average gift value of young winners (this will combine a base field Gift Price and the Age Group field I created earlier):

measures