Skip to main content

Model & Joins

.model

Model defines how Views are joined together. You can also define fields at the model level, this is convenient when you need to refer to fields from different views to calculate a new field.

Each Model must be stored as model_name.model YAML file:

model: model_name
connection: connection_name
label: 'model label in UI'
description: 'model description in UI'
access_roles:
- role
- role
- ...
access_users:
- user_alias
- user_alias
- ...
always_join: alias, alias, ...
sql_always_where: 'SQL WHERE condition for main query'
sql_always_where_calc: 'SQL WHERE condition for outer query'

udfs:
- user_defined_function_name
- user_defined_function_name
- ...

joins:
- from_view: view_name
hidden: false
label: 'join label in UI'
description: 'description string'
as: alias
hide_fields:
- alias.field_name
- alias.field_name
- ...
show_fields:
- alias.field_name
- alias.field_name
- ...

- join_view: view_name
hidden: false
label: 'join label in UI'
description: 'join description in UI'
as: alias
type: join_type
sql_on: 'SQL ON clause'
sql_where: 'SQL WHERE condition'
hide_fields:
- alias.field_name
- alias.field_name
- ...
show_fields:
- alias.field_name
- alias.field_name
- ...

# more join declarations
# - join_view: view_name

fields:
# List of fields (Dimensions, Time, Measures, Calculations, Filters)
# - dimension: dimension_name
# ...

Model Parameters

NameTypeDefaultDescription
model*string-Model name
connection*string-Connection name
labelstring-Model label in UI
descriptionstring-Model description in UI
access_rolesstring[]-If specified, only users with the listed roles will have access to model
access_usersstring[]-If specified, only the listed users will have access to model
always_joinstring-Specify which joins must always be applied to a model
sql_always_wherestring-Write a SQL WHERE condition (must use BlockML reference of dimension)
sql_always_where_calcstring-Write a SQL WHERE condition (must use BlockML reference of dimension/measures/calculation)
udfsstring[]-List all of user defined functions needed by current Model
joins*Join[]-Begin a section of joins
fields(Dimension | Time | Measure | Calculation | Filter) []-Begin a section of fields

Join

NameTypeDefaultDescription
from_view*string-Specify base view for joins
join_view*string-Join additional view
as*string-Alias name for join
typeenumleft_outerSpecify join type:
  • inner
  • cross
  • full
  • full_outer
  • left
  • left_outer (default value if no type specified)
  • right
  • right_outer
labelstring-Overwrite view name in UI
descriptionstring-Overwrite view description in UI
sql_on*string-Write a SQL ON clause (must use BlockML reference of dimension)
sql_wherestring-Write a SQL WHERE condition (must use BlockML reference of dimension). Condition will be applied only if join is required.
hide_fieldsstring[]-Specify the view fields you want to hide
show_fieldsstring[]-Specify the view fields you want to show