AML Model Fields

Knowledge Checkpoint

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

• Dimensions & Measure

Dimension

Dimension represents a column in a table or a calculation. You can combine dimensions in the current model into a new dimension.

Parameter definition

Parameter name	Required	Description
label	Yes	Specifies how the dimension will appear in the Ready-to-explore Dataset.
type	Yes	Data type of the dimension. Possible values: 'text', 'number', 'date', 'datetime', 'truefalse', 'json', 'unknown'.
definition	No	How the dimension is calculated, using @sql or @aql syntax. If omitted, Holistics assumes a column with the same name exists in the underlying table.
description	No	Describes the semantic of the dimension.
hidden	No	Default false. Hides the dimension from the Exploration interface of Dataset and Report. This is not a Security Feature (Reason).
format	No	Display format for the dimension's value. See Number Format and Date Format.
primary_key	No	Default false. Marks this dimension as the primary key of the model. This enables query optimization and supports Single Model Conditions in AQL. Only one dimension per model should have primary_key: true.

Definition of dimension

You can define dimensions using either @sql or @aql syntax. As a rule of thumb:

• Use @sql when referencing underlying table columns directly.
• Use @aql for calculations or transformations based on existing dimensions in the model (it provides better syntax suggestions, typechecking, and validation).

SQL definition

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

Default behavior: If the 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.

AQL definition

• @aql definitions can reference existing dimensions in the current model using the model.dimension_name syntax.
• Unlike @sql, AQL definitions cannot reference underlying table columns directly (only dimensions that are already defined in the model).

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 id {
    label: 'Order ID'
    type: 'number'
    primary_key: true  // Mark as primary key for optimization and AQL features
    definition: @sql {{ #SOURCE.id }};;
  }

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

  // AQL definition: references the existing "created_at" dimension
  dimension created_at_year {
    label: 'Created At Year'
    type: 'number'
    definition: @aql date_part('year', orders.created_at) ;;
  }
}

Measure

Measure represents an aggregation operation in a model.

Parameter definition

Parameter name	Required	Description
label	Yes	Specifies how the measure will appear in the Ready-to-explore Dataset.
type	Yes	Data type of the measure's result. Possible values: 'text', 'number', 'date', 'datetime', 'truefalse', 'json', 'unknown'. Typically 'number'.
definition	Yes	How the measure is calculated. Use @aql (recommended) or @sql. Learn more below.
description	No	Describes the semantic of the measure.
hidden	No	Default false. Hides the measure from the Exploration interface of Dataset and Report. This is not a Security Feature (Reason).
aggregation_type	No	Default 'custom'. Aggregation kind: 'avg', 'sum', 'count', 'count distinct', 'min', 'max', 'median', 'stdev', 'stdevp', 'var', 'varp', 'running sum', 'running avg', 'running count', 'running min', 'running max', or 'custom' (when the definition itself is already an aggregation).
format	No	Display format for the measure's value. See Number Format and Date Format.

AQL definition of measure

You can use either @sql or @aql to define measures, but we recommend using @aql for most cases as it provides better syntax suggestions, typechecking, and validation.

AQL provides built-in aggregation functions such as count(), sum(), avg(), min(), max(), count_distinct(), median(), and more. For a full list, see the Aggregation Functions reference.

Syntax forms: There are two equivalent ways to write an AQL aggregation:

1. Function call form (most common):

   measure total_orders {
     label: 'Total Orders'
     type: 'number'
     definition: @aql count(orders.id) ;;
   }

2. Pipe form:

   measure total_orders {
     label: 'Total Orders'
     type: 'number'
     definition: @aql orders.id | count() ;;
   }

Both produce the same result. Use whichever feels more natural.

Referencing other measures: You can create derived measures by referencing other measures in the same model:

measure average_order_value {
  label: 'Average Order Value'
  type: 'number'
  definition: @aql total_revenue / total_orders ;;
}

caution

You cannot use dimensions directly in a measure definition without wrapping them in an aggregation function. Every measure must produce an aggregated value.

Example of measure syntax

Model orders {
  type: 'table'
  label: "Orders"
  table_name: 'ecommerce.orders'
  data_source_name: 'demodb'

  dimension id {
    label: 'Order ID'
    type: 'number'
    primary_key: true
    definition: @sql {{ #SOURCE.id }};;
  }

  dimension amount {
    label: 'Amount'
    type: 'number'
  }

  // Simple count aggregation
  measure total_orders {
    label: 'Total Orders'
    type: 'number'
    definition: @aql count(orders.id) ;;
  }

  // Sum aggregation
  measure total_revenue {
    label: 'Total Revenue'
    type: 'number'
    definition: @aql sum(orders.amount) ;;
  }

  // Derived measure referencing other measures
  measure average_order_value {
    label: 'Average Order Value'
    type: 'number'
    definition: @aql total_revenue / total_orders ;;
  }
}

FAQs

Should I use the 'hidden' property in dimension/measure for data restriction purposes?

The short answer is: you should not. The proper way to set up permission control is via Column-level Permission.

To explain, you can use the hidden: true property if you would like a dimension/measure to be accessible during development, but concealed from users in Reporting. This is achieved by hiding it within the Dimension/Measure Selection of the Dataset.

dimension id {
    label: 'Id'
    type: 'number'
    hidden: true
    definition: @sql {{ #SOURCE.id }};;
}

However, this method should not be applied as a means to restrict others from using these dimensions/measures in Reporting.

Despite being hidden, these dimensions/measures remain accessible through Dashboard Filters, Dataset Relationship, or AQL Expression.

Therefore, using the 'hidden' property primarily serves to declutter the Dimensions/Measures list in your Dataset.

Therefore, if you want to restrict access control and disallow certain users to see certain Dimensions/Measures, you should use Column-level Permission instead.