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

import '../models/base-model/users.model.aml' { users }
import '../models/base-model/orders.model.aml' { orders }

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

// import cities model and countries model to this file 

import '../base-model/cities.model.aml' {cities}
import '../base-model/countries.model.aml' {countries}

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