Skip to main content

Query Model

Knowledge Checkpoint

We think it would be best if you have a clear understanding of these concepts before reading this documentation:

Intro

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

Syntax Construct

// You don't need import statements in AML 2.0

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

}

Query Model Syntax Example

// You don't need import statements in AML 2.0

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

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.

Let us know what you think about this document :)