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

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:

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

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

4.0

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.

To add a Custom Dimension in your Data Model:

1. Go to your AML Data Model file.
2. Add the dimension using AML Dimension syntax.
3. (Optional) Click Preview to enter Preview mode. This is useful to verify that your Dimension behaves correctly.
4. Click Save.
5. 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

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):

4.0

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.

To add a Measure in your Data Model:

1. Go to your AML Data Model file.
2. Add the measure using AML Measure syntax.
3. (Optional) Click Preview to enter Preview mode. This is useful to verify that your Dimension behaves correctly.
4. Click Save.
5. 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 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. :::