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
    rel(rel_expr: orders.user_id > users.id, active: 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: @sql count({{#SOURCE.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: @sql {{ id }};;
  aggregation_type: 'count'
}

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

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

Relationship File

Full-form Relationship

// 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

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)