Dimensions & Measures

Intro

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 dimensions, you can create Custom Dimensions 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 a 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 -> Custom Dimension,
  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

note
  • Custom dimensions can only refer to fields within the same model. If you need to create a cross-model dimension, you can create a transform(SQL) model that joins the related models and includes the necessary fields, then create the custom dimension within the model's code or as a custom dimension.
  • You can use your SQL DB's equivalent of the CAST() operation in a custom field to create a new field with the new datatype. e.g. CAST( {{ #THIS.field_a }} AS INTEGER )
  • When you use Persistence settings, Custom Dimensions will not be persisted to the database.

Adding measures to data model

Measures are similar to Custom Dimensions, except that they're created using aggregate functions/operations. Think of Measures as the aggregate expression of your typical SQL statement. They do not need a table to be aggregated beforehand and will automatically apply GROUP BY clauses based on the reports you build.

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

note
  • Custom measures do not accept pre-defined GROUP BY clauses as the generated report will modify and include the required GROUP BY clauses, allowing them to be reused across a variety of use cases.
  • Custom measures can only refer to fields within the same model. If you need to create a cross-model measure, you can create a transform(SQL) model that joins the related models and includes the necessary fields, then create the pre-aggregate and define the measure within the model's code, or create a custom measure.
  • You can use your SQL DB's equivalent of the CAST() operation in a custom field to create a new field with the new datatype. e.g. COUNT(CAST( {{ #THIS.field_a }} AS INTEGER ))
  • When you use Persistence settings, Custom Measures will not be persisted to the database.