AML Examples

Introduction

This page serves as a quick lookup for commonly used AML patterns.

Dataset File

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
    relationship(orders.user_id > users.id, true)
  ]
}

Model File

Table Model

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: @aql count(users.id) ;;
  }
}

Query Model

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

Measure

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

measure total_users {
  label: 'Total Users'
  type: 'number'
  definition: @aql count(users.id) ;;
}

Dimension

// 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, using AQL.
dimension created_at_year {
  label: 'Created At Year'
  type: 'number'
  definition: @aql date_part('year', orders.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 AQL 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: @aql case(
    when: users.age < 18, then: 'Under 18',
    when: users.age >= 18 and users.age < 23, then: '18 - 22',
    when: users.age >= 23 and users.age < 31, then: '23 - 30',
    when: users.age >= 31 and users.age < 41, then: '31 - 40',
    else: 'Over 40'
  ) ;;
}


// This is a dimension to extract month's name from the base dimension `created_at`.
dimension month_name {
  label: 'Month Name'
  type: 'text'
  definition: @aql date_format(orders.created_at, '%m - %B') ;; // example output: '12 - December'
}

Relationship File

Full-form Relationship

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

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

Short-form Relationship

Many-to-One relationship

// Relationship short form defined in dataset file, relationship type > is many_to_one
relationship(ecommerce_cities.country_code > ecommerce_countries.code, true, 'one_way')

One-to-One relationship

// Relationship short form defined in dataset file, relationship type - is one_to_one
relationship(ecommerce_merchants.admin_id - ecommerce_users.id, true)