AML Model Fields

DimensionCopy Anchor Link

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

Parameter definitionCopy Anchor Link

Definition of dimensionCopy Anchor Link

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 definitionCopy Anchor Link

• @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, the compiler assumes that there is a column in the underlying table with the same name as the dimension.

AQL definitionCopy Anchor Link

• @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 syntaxCopy Anchor Link

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) ;;
  }
}

MeasureCopy Anchor Link

Measure represents an aggregation operation in a model.

Parameter definitionCopy Anchor Link

AQL definition of measureCopy Anchor Link

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

   Copy Text

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

2. Pipe form:

   Copy Text

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

Example of measure syntaxCopy Anchor Link

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

FAQsCopy Anchor Link

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

The short answer is: you should not. hidden is a presentation hint, not an access rule. Access control belongs in the layer that serves the data, not in the model.

Use hidden: true when you want a dimension or measure to stay available for other definitions to reference, but kept out of the field list that consumers pick from.

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

A hidden field is still a field. It can be referenced in a relationship, used in a filter, or named in any AQL expression, and anyone who can write AQL against the dataset can read it.

So hidden primarily serves to declutter the field list of your dataset. If certain users must not see certain fields, enforce that where the data is served, not with hidden.