Measure

Measure

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

Parameter

Parameter name	Description
label	Specifies how the measure will appear in the Ready-to-explore Dataset
type	Specifies the data type you want to apply to the measure (currently Holistics only support number/date/datetime)
description	Add measure description
hidden	Hides measure from the Exploration interface of Dataset and Report
aggregation_type	Specify aggregate function of Measure. Currently we only support: count, count, distinct, sum, avg, max, min, max, median, stdev, stdevp, var, varp, custom
definition	Determines 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'
   }

Examples

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'
    }