Skip to main content

AML Quick Reference

Introduction​

This page serves as a quick lookup for common AML syntaxes to be used with Holistics 4.0.

AML Dataset FileJump to exampleGo to Reference Page
AML Field FileJump to exampleGo to Reference Page
AML Model FileJump to exampleGo to Reference Page
AML Relationship FileJump to exampleGo to Reference Page

Dataset File​

Dataset Syntax Example​

Dataset raw_ecommerce {
  label: 'Raw Ecommerce'
  description: "This Dataset is about Ecommerce data"
  owner: '[email protected]'
  data_source_name: 'demodb'

  models: [
    users,
    orders
  ]

  relationships: [
  // define relationship between orders and users is many to one
    rel(rel_expr: orders.user_id > users.id, active: true)
  ]
}

Field File​

Measure Syntax Example​

measure total_orders {
  label: 'Total Orders'
  type: 'number'
  definition: @sql {{ id }};;
  aggregation_type: 'count'
}

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

Dataset raw_ecommerce {
  label: 'Raw Ecommerce'
  description: "This Dataset is about Ecommerce data"
  owner: '[email protected]'
  data_source_name: 'demodb'

  models: [
    users,
    orders
  ]

  relationships: [
    // define relationship between orders and users is many to one
    rel(rel_expr: orders.user_id > users.id, active: true)
  ]
}

Field File​

Measure Syntax Example​

measure total_orders {
  label: 'Total Orders'
  type: 'number'
  definition: @sql {{ id }};;
  aggregation_type: 'count'
}

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

Dimension Syntax Example​

// This is a simple dimension. Holistics automatically assume there is a `created_at` column in your
// underlying table
dimension created_at {
  label: 'Created At'
  type: 'datetime'
}

// This is a derived dimension based on the dimension above. A custom SQL is applied to extract the
// year part of the timestamp.
dimension created_at_year {
  label: 'Created At Year'
  type: 'number'
  definition: @sql extract(year from{{ created_at }});;
}

// This is a simple dimension, with explicitly defined column name.
dimension status {
  label: 'Status'
  type: 'text'
  // If you don't include this line, Holistics will automatically use the table's column that bear the
  // same name with the dimension (i.e status).
  definition: @sql {{ #SOURCE.status }};;
}

// This is a dimension which uses case-when syntax.
dimension age_group {
  label: 'User Age Group'
  description: "'Under 18', '18 - 22', '23 - 30', '31 - 40', 'Over 40'"
  type: 'text'
  hidden: false
  definition: @sql
    case
      when {{ age }} < 18 then 'Under 18'
      when {{ age }} >= 18 and {{ age }} < 23 then '18 - 22'
      when {{ age }} >= 23 and {{ age }} < 31 then '23 - 30'
      when {{ age }} >= 31 and {{ age }} < 41 then '31 - 40'
      else 'Over 40'
    end
  ;;
}


// This is a dimension to extract month's name from the base dimension `created_at`.
dimension month_name {
  label: 'Month Name'
  type: 'text'
  description: ''
  definition: @sql
    CONCAT(
      EXTRACT(MONTH FROM TO_DATE(TO_CHAR({{ order_created_at }}, 'Month'), 'Month')),
      ' - ',
      TO_CHAR({{ order_created_at }}, 'Month')
    )
  ;;
  hidden: false
}

Model File​

Table Model Syntax Example​

Model users {
  type: 'table'
  label: "Users"
  data_source_name: 'bigquery_dw'
  table_name: 'users'

  dimension id {
    label: 'ID'
    type: 'number'
    hidden: false // optional
    definition: @sql {{#SOURCE.id}};; // optional
  }

  dimension email {
    label: 'Email'
    type: 'number'
    // without "definition", it automatically uses the same column name as the dimension name ('email')
  }

  measure user_count {
    type: 'number'
    label: 'Count Users'
    definition: @sql count({{#SOURCE.id}});;
  }
}

Query Model Syntax Example​


Model location {
  type: 'query'
  label: 'Location'
  data_source_name: 'demodb'
  models: [cities, countries]

  query: @sql
    select {{ #ci.id }} as city_id
        , {{ #ci.name }} as city_name
        , {{ #co.code }} as country_code
        , {{ #co.name }} as country_name
        , {{ #co.continent_name }}
    from {{ #cities as ci }}
      left join {{ #countries as co }} on {{ #co.code }} = {{ #ci.country_code }};;


  dimension city_id {
    label: 'City Id'
    type: 'number'
  }

  dimension city_name {
    label: 'City Name'
    type: 'text'
  }

  dimension country_code {
    label: 'Country Code'
    type: 'text'
  }

  dimension country_name {
    label: 'Country Name'
    type: 'text'
  }

  dimension continent_name {
    label: 'Continent Name'
    type: 'text'
  }
}

Relationship File​

Full-form Relationship Syntax Example​


// Relationship full form defined in dataset file
RelationshipConfig {
  rel: Relationship {
    type: 'many_to_one'
    from: ref('products','merchant_id')
    to: ref('merchants','id')
  }
  active: true
},

// Relationship defined in model file
Relationship relationship_name {
  type: 'many_to_one'
  from: ref('orders', 'user_id')
  to:  ref('users', 'id')
}

Short-form Relationship Syntax Example​

Many to One relationship:​


// Relationship short form defined in dataset file, relationship type > is many_to_one
rel(rel_expr: orders.user_id > users.id, active: true)

One to One relationship:​

// Relationship short form defined in dataset file, relationship type - is one_to_one
rel(rel_expr: users.id - accounts.user_id, active: true)

Let us know what you think about this document :)