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

Name	Type	Default	Description
model*	string	-	Model name
connection*	string	-	Connection name
label	string	-	Model label in UI
description	string	-	Model description in UI
access_roles	string[]	-	If specified, only users with the listed roles will have access to model
access_users	string[]	-	If specified, only the listed users will have access to model
always_join	string	-	Specify which joins must always be applied to a model
sql_always_where	string	-	Write a SQL WHERE condition (must use BlockML reference of dimension)
sql_always_where_calc	string	-	Write a SQL WHERE condition (must use BlockML reference of dimension/measures/calculation)
udfs	string[]	-	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

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