Skip to main content

AML Dimension & Measure

Knowledge Checkpoint

A grasp of these concepts will help you understand this documentation better:

(This feature is currently in active development. Please reach out if you want to become beta tester)

Dimension​

Dimension syntax defines the dimension of a data model. Dimension represents a column in a table or a calculation.

Parameter Definition​

Parameter nameDescription
labelSpecifies how the dimension will appear in the Ready-to-explore Dataset
typeSpecifies the data type you want to apply to the dimension (currently Holistics only support text/number/date/datetime/truefalse)
descriptionAdd dimension description
hiddenHides dimension from the Exploration interface of Dataset and Report
definitionDetermines how the dimension will be defined or calculated based on SQL queries. Learn more about the definition parameter below. πŸ‘‡

SQL Definition of Dimension​

The concept of definition: @sql {{ }};; refers to the way you specify how each dimension’s values should be retrieved or calculated based on SQL queries.

References: There are two ways to reference columns or dimensions within the SQL definition:

  • @sql {{ #SOURCE.column_name }};; : This references a column in the table connected to the current model.
  • @sql {{ dimension_name }};; : This references another dimension defined within the same model.

Default Behavior: If the SQL definition for a dimension is not explicitly provided, Holistics assumes that there is a column in the underlying table with the same name as the dimension. This is useful for cases where the dimension's name matches a column name, and you want to use that column as the dimension's source.

Example of Dimension Syntax​

Model orders {
type: 'table'
label: "Orders"
table_name: 'ecommerce.orders'
data_source_name: 'mydemodb'
description: "This is the AML Orders Model"

dimension status {
label: 'Status'
type: 'text'
//to reference the "status" column in the source table
definition: @sql {{ #SOURCE.status }};;
}

dimension created_at {
label: 'Created At'
type: 'datetime'
}

dimension created_at_year {
label: 'Created At Year'
type: 'number'
//to calculate the year from the "created_at" dimension
definition: @sql extract(year from{{ created_at }});;
}
}

Measure​

Measure syntax defines the measure of a data model. Measure represents an aggregated dimension in a model.

Parameter Definition​

Parameter nameDescription
labelSpecifies how the measure will appear in the Ready-to-explore Dataset
typeSpecifies the data type you want to apply to the measure (currently Holistics only support number/date/datetime)
descriptionAdd measure description
hiddenHides measure from the Exploration interface of Dataset and Report
aggregation_typeSpecify aggregate function of Measure. Currently we only support: count, count, distinct, sum, avg, max, min, max, median, stdev, stdevp, var, varp, custom
definitionDetermines how the measure will be defined or calculated based on SQL queries. Learn more about the definition parameter below. πŸ‘‡

SQL Definition of Measure​

Forms: There are two primary forms that definition for measures can take:

  1. Native Holistics Aggregation Type: Use aggregation type that Holistics natively supports such as sum, count, count_distinct, avg, etc.. For example:

    measure total_users {
    label: 'Total Users'
    type: 'number'
    // The definition here is the inner expression of the aggregation
    definition: @sql {{ user_id }};;
    aggregation_type: 'count'
    }

    When used in an explore, this measure will be treated as COUNT({{ user_id }})

  2. Custom Aggregation Form (aggregation_type: 'custom' - this is the default when aggregation_type is not specified): The entire definition is used as the aggregation expression. This allows you to use aggregation functions from the source database that are not supported by Holistics (e.g. PERCENTILE_CONT from Redshift).

    measure percentile {
    label: 'percentile'
    type: 'number'
    // must be valid aggregation expression that can be run in
    // aggregation position of a query
    definition: @sql percentile_cont(0.6) within group (order by {{ profit }});;
    aggregation_type: 'custom'
    }
    SELECT
    col,
    -- ...other group by columns
    COUNT(*) -- The definition of custom aggregation must be an expression
    -- that can be placed here
    FROM orders
    GROUP BY
    1
    -- , ...other group by columns

    Additionally, you can write a custom measure with calculations between measures:

    measure profit {
    label: 'Profit'
    type: 'number'
    // must be valid aggregation expression that can be run in
    // aggregation position of a query
    definition: @sql {{ measure_revenue }} - {{ measure_cost }} + sum({{ dimension_discount }});;
    aggregation_type: 'custom'
    }

    However, it's important to note that you cannot directly use dimensions without aggregation in a custom measure. For example:

    measure profit {
    label: 'Profit'
    type: 'number'
    // top level must be aggregated
    definition: @sql {{ measure_revenue }} - {{ measure_cost }} + {{ dimension_discount }};;
    aggregation_type: 'custom'
    }

Example of Measure Syntax​

Model users {
type: 'table'
label: "Users"
description: "This is the AML Users Model"
table_name: '"ecommerce"."users"'
data_source_name: 'demodb'

measure total_users {
label: 'Total Users'
type: 'number'
definition: @sql count({{#SOURCE.id}});;
aggregation_type: 'custom'
}
}

FAQs​

How should I define aggregate functions for measures, and what are the important considerations?​

  • Choose between using aggregation_type parameter or using aggregation functions from the database within definition: @sql ;; parameter. It’s important to note that you should not define an aggregate function in both parameters.

    • If you define an aggregate function using the aggregation_type, the definition: @sql ;; parameter must not contain any aggregate functions.

      //What you should write:
      measure measure_1 {
      ...
      definition: @sql {{ user_id }};;
      aggregation_type: 'count'
      }
      ---------------------------
      //What you should NOT write
      measure measure_1 {
      ...
      definition: @sql count{{ user_id }};;
      aggregation_type: 'count'
      }
    • If you define the aggregate function within the definition: @sql ;; parameter, make sure to set the aggregation_type to custom.

      //What you should write:
      measure measure_2 {
      ...
      definition: @sql sum({{#SOURCE.id}});;
      aggregation_type: 'custom'
      }

Let us know what you think about this document :)