Query Model

Knowledge Checkpoint

Related docs:

• Query Model

Intro

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

Syntax Construct

AML 1.0

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

}

AML 2.0

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

AML 1.0

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

AML 2.0

// 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 name	Description
import	Add files to a model
models	Add models to be used in the querying
type	Model type (whether it is Table model or SQL model)
label	Specifies how the model will appear in the Ready-to-explore Dataset
description	Add model description
owner	Define who should be in charge of managing the current model
table_name	Changes the SQL table on which a model is based
data_source_name	Specify the database that Holistics will execute the generated query against (in the current model)
dimension	Define a dimension.
measure	Define a measure.