Dimensions & Measures
Introduction
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
- 3.0
- 4.0
Adding 3.0 Dimension 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)
To add a custom dimension:
- Go to the model view UI, click Add -> Custom Dimension,
- 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:
Adding 4.0 Dimension to Data Model
info
Please refer to AML Dimension Reference to learn more about all available parameters and their example usage.
- Go to your AML Data Model file.
- Add the dimension using AML Dimension syntax.
- (Optional) Click Preview to enter Preview mode. This is useful to verify that your Dimension behaves correctly.
- Click Save.
- Click Deploy to production to make your new dimension live.
Dimension Syntax
dimension dimension_name {
label: 'Dimension Label'
type: 'text' | 'number' | 'date' | 'datetime' | 'truefalse'
description: 'Field Description'
hidden: true|false
definition: @sql {{ dimension }};;
}
Important Notes
- 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.
- 3.0
- 4.0
Adding 3.0 Measure to Data Model
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):
Adding 4.0 Measure to Data Model
info
Please refer to AML Measure Reference to learn more about all available parameters and their example usage.
- Go to your AML Data Model file.
- Add the measure using AML Measure syntax.
- (Optional) Click Preview to enter Preview mode. This is useful to verify that your Dimension behaves correctly.
- Click Save.
- Click Deploy to production to make your new dimension live.
Measure Syntax
measure measure_name {
label: 'Measure Name'
type: 'number' | 'date' | 'datetime'
definition: @sql count( {{ dimension }} );;
}
measure measure_name {
label: 'Measure Name'
type: 'number | date | datetime'
definition: @sql {{ dimension }};;
aggregation_type: 'count' | 'count distinct' | 'sum' | 'avg' | 'max' | 'min'
}
Important Notes
- Custom measures do not accept pre-defined
GROUP BY
clauses as the generated report will modify and include the requiredGROUP 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. :::