Skip to main content

Query Model (AML)

Intro

Query Model defines a data model that sits on top of a SQL query.

Syntax Construct

import 'path/to/model_a.model.aml' { model_a }
import 'path/to/model_b.model.aml' { model_b }

Model model_name {
type: 'query'
label: "Model Label"
description: ""
owner: '[email protected]'
data_source_name: 'data_source_name'

models: [model_a, model_b]
query: @sql SELECT {{ #model_a.field_name }} FROM {{ #model_a }};;

dimension dimension_name_1 {
// dimension 1 definition
label: 'Dimension Label 1'
type: 'text | number | date | datetime | truefalse'
description: 'Field Description'
hidden: true|false
definition: @sql {{#SOURCE.column_name}};;
}

dimension dimension_name_2 {
// dimension 2 definition
}

measure measure_name_1 {
// measure 1 definition
label: 'Measure Name'
type: 'number | date | datetime'
definition: @sql {{#SOURCE.column_name}};;
aggregation_type: 'count | count_distinct | sum | avg'
}

}

Parameter definition

Parameter nameDescription
importAdd files to a model
modelsAdd models to be used in the querying
typeModel type (whether it is Table model or SQL model)
labelSpecifies how the model will appear in the Ready-to-explore Dataset
descriptionAdd model description
ownerDefine who should be in charge of managing the current model
table_nameChanges the SQL table on which a model is based
data_source_nameSpecify the database that Holistics will execute the generated query against (in the current model)
dimensionDefine a dimension.
measureDefine a measure.

Example Code

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

}