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 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
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.
To add a custom dimension:
- Go to the model view UI, click Add -> Calculated Column,
- Input field name and SQL formula.
- Click Create, and a new column named will be created
I can also make use of my aforementioned fields by creating another field named Age Group based on their age:
- 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:
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):