AML Relationship

Knowledge Checkpoint

A grasp of these concepts will help you understand this documentation better:

• Relationship
• Create a 4.0 Relationship

Holistics supports many_to_one, one_to_one, many_to_many, and range relationships. Use long-form syntax to define the relationship type and join conditions, including composite keys and relationship filters.

Relationship Syntax Definition

In AML, relationships belong to datasets. Define relationships between related data models in the dataset's relationships list using long-form or short-form syntax.

Every relationship needs a few key pieces of information:

• Relationship type: many_to_one, one_to_one, many_to_many, or range
• Join fields: The dimensions you're connecting between the two models
• Active status: Whether the relationship should be enabled in your dataset
• Filter direction (optional): Whether filters can flow in one or both directions (see Controlling filter and grouping paths)
• Nullability (optional): Whether the joining column can contain NULL or unmatched values, which decides if Holistics generates a LEFT JOIN or an INNER JOIN (see How Holistics handles joins)
• RLP propagation (optional): How row-level permission filters flow through the relationship, independently of the filter direction (see Row-level permission and filter direction)

Define relationships in a dataset

Add both models to the dataset's models list. Then add their relationship to the relationships list.

Use short-form syntax for a single-column equality join. Use long-form syntax to write the join fields and configuration explicitly. Long-form syntax also supports composite keys, filters, many-to-many joins, and range joins.

Long-form syntax

Add a RelationshipConfig block for each relationship. Its rel field defines the join; the other fields control the relationship's behavior in the dataset.

This example joins each city's country_code to a country's code. It enables the relationship and sets all 3 optional configuration fields to their defaults:

ecommerce_test.dataset.aml

Dataset ecommerce_test {
  label: 'Ecommerce Test'
  data_source_name: 'demodb'
  models: [
    ecommerce_cities,
    ecommerce_countries
  ]
  relationships: [
    RelationshipConfig {
      rel: EqualityRelationship {
        type: 'many_to_one'
        from: FieldRef { model: 'ecommerce_cities', field: 'country_code' }
        to: FieldRef { model: 'ecommerce_countries', field: 'code' }
      }
      active: true
      direction: 'two_way'
      nullable: true
      rlp_propagation: 'inherit'
    }
  ]
}

Each FieldRef names a model and a dimension. The EqualityRelationship block compares the dimensions in from and to.

The configuration fields in this example are:

Field	Example value	Behavior and default
rel	EqualityRelationship { ... }	Defines the join type and fields.
active	true	Enables the relationship. Set false to disable it.
direction	'two_way'	Default: 'two_way'. Filters and groupings can flow in both directions.
nullable	true	Default: true. Holistics uses a LEFT JOIN to retain NULL or unmatched keys.
rlp_propagation	'inherit'	Default: 'inherit'. Row-level permission filters follow direction.

Omit direction, nullable, and rlp_propagation if you want their default values. See Relationship Config for all supported values.

See Relationship types and join conditions for composite keys, filters, many-to-many joins, and range joins.

Short-form syntax

Use relationship() to define the same cities-to-countries join in one line:

ecommerce_test.dataset.aml

Dataset ecommerce_test {
  label: 'Ecommerce Test'
  data_source_name: 'demodb'
  models: [
    ecommerce_cities,
    ecommerce_countries
  ]
  relationships: [
    relationship(ecommerce_cities.country_code > ecommerce_countries.code, true)
  ]
}

Choose the operator for the relationship type:

• >: many-to-one, with the many side on the left.
• -: one-to-one.

Set the second argument to true to enable the relationship, or false to disable it. The optional configuration fields use the same defaults as long-form syntax.

Filter direction

Set direction as the third argument or as a named argument. Both examples below restrict filters and groupings to one direction:

relationship(ecommerce_cities.country_code > ecommerce_countries.code, true, 'one_way')
relationship(ecommerce_cities.country_code > ecommerce_countries.code, true, direction: 'one_way')

If you omit direction, filters and groupings can flow in both directions ('two_way'). See Controlling filter and grouping paths.

Null or unmatched keys

Keep the default nullable=true to retain rows with NULL or unmatched keys. Set nullable=false only after verifying that every key is non-NULL and matches a row in the other model.

caution

With nullable=false, Holistics uses an INNER JOIN. NULL or unmatched keys cause rows to be dropped, which can reduce your totals. See How Holistics handles joins for a verification query.

relationship(ecommerce_cities.country_code > ecommerce_countries.code, true, nullable=false)

Row-level permission filters

Use rlp_propagation to control row-level permission filters separately from direction. This example allows permission filters to flow in both directions while other filters follow one_way:

relationship(fct_sales.country_id > dim_countries.id, true, direction: 'one_way', rlp_propagation: 'two_way')

The default is 'inherit', so permission filters follow direction. Set 'two_way' to let permission filters flow in both directions. Set 'one_way' to limit permission filters to the one-to-many direction. See Row-level permission and filter direction for examples and access restrictions.

Relationship types and join conditions

Many-to-one

The short-form entry uses > with the many side on the left:

relationship(ecommerce_cities.country_code > ecommerce_countries.code, true)

The equivalent long-form entry in the dataset's relationships list is:

RelationshipConfig {
  rel: EqualityRelationship {
    type: 'many_to_one'
    from: FieldRef { model: 'ecommerce_cities', field: 'country_code' }
    to: FieldRef { model: 'ecommerce_countries', field: 'code' }
  }
  active: true
}

One-to-one

The short-form entry uses -:

relationship(users.id - profiles.user_id, true)

The equivalent long-form entry in a dataset containing users and profiles is:

RelationshipConfig {
  rel: EqualityRelationship {
    type: 'one_to_one'
    from: FieldRef { model: 'users', field: 'id' }
    to: FieldRef { model: 'profiles', field: 'user_id' }
  }
  active: true
}

Composite keys

Put the first column pair in from and to. Add an on block for each extra pair. A row matches only when every pair is equal.

rel_demo.dataset.aml

Dataset rel_demo {
  label: 'Relationship Types Demo'
  data_source_name: 'DemoData'
  models: [dl_order_lines, dl_shipments]
  relationships: [
    RelationshipConfig {
      rel: EqualityRelationship {
        type: 'many_to_one'
        from: FieldRef { model: 'dl_order_lines', field: 'order_id' }
        to: FieldRef { model: 'dl_shipments', field: 'order_id' }
        on {
          from: FieldRef { model: 'dl_order_lines', field: 'warehouse_id' }
          to: FieldRef { model: 'dl_shipments', field: 'warehouse_id' }
        }
      }
      active: true
    },
  ]
}

The examples below show individual RelationshipConfig entries to add to a dataset's relationships list.

Many-to-many

Use ManyToManyRelationship with type: 'many_to_many' and write the join condition in match, a Boolean AQL expression. Here, from and to are unique row keys, not columns to compare.

RelationshipConfig {
  rel: ManyToManyRelationship {
    type: 'many_to_many'
    from: FieldRef { model: 'dl_subscriptions', field: 'id' }
    to: FieldRef { model: 'dl_periods', field: 'label' }
    match: @aql
      dl_subscriptions.started_on <= dl_periods.end_date
        and dl_subscriptions.ended_on >= dl_periods.start_date
    ;;
  }
  active: true
}

This example matches subscriptions to every period their dates overlap. match holds the full join condition and can reference only the two related models.

Range

Use RangeRelationship with type: 'range' when each source row matches at most one target row. As with many-to-many, from and to are unique row keys and match holds the full join condition.

RelationshipConfig {
  rel: RangeRelationship {
    type: 'range'
    from: FieldRef { model: 'dl_quotes', field: 'id' }
    to: FieldRef { model: 'dl_price_bands', field: 'id' }
    match: @aql
      dl_quotes.currency == dl_price_bands.currency
        and dl_quotes.quoted_at >= dl_price_bands.valid_from
        and dl_quotes.quoted_at < dl_price_bands.valid_to
    ;;
  }
  active: true
}

Relationship filters

Use EqualityRelationship and put the equality pair in from and to. Add where.from or where.to to limit which rows can match. Each filter is a Boolean AQL expression that references only its own model.

RelationshipConfig {
  rel: EqualityRelationship {
    type: 'many_to_one'
    from: FieldRef { model: 'dl_comments', field: 'commentable_id' }
    to: FieldRef { model: 'dl_posts', field: 'id' }
    where {
      from: @aql dl_comments.commentable_type == 'Post' ;;
      to: @aql dl_posts.is_published ;;
    }
  }
  active: true
}

Parameter Reference

Use the tables below to check the fields for each relationship block and its dataset configuration.

Long-form relationship blocks

Use these blocks inside RelationshipConfig.rel. All field references use FieldRef { model: 'model_name', field: 'field_name' }.

Parameter	Applies to	Description
type	EqualityRelationship	many_to_one or one_to_one.
type	ManyToManyRelationship	many_to_many.
type	RangeRelationship	range.
from, to	All 3 blocks	Required field references. Equality relationships compare these fields; many-to-many and range relationships use them to identify unique rows.
on { from, to }	EqualityRelationship	An additional equality pair of field references. Repeat the block for more composite-key columns.
where { from, to }	EqualityRelationship	Optional Boolean AQL filters on either or both sides. Each filter references only its own model.
match	ManyToManyRelationship, RangeRelationship	Required Boolean AQL expression with the complete join condition. References only the two related models.

Relationship Config

This wrapper enables a relationship in the dataset and controls its behavior.

Parameter name	Description
rel	The relationship this configuration applies to
active	Whether this relationship should be active in the dataset (true/false)
direction	Controls which direction filters and groupings can flow: 'one_way' or 'two_way' (defaults to 'two_way'). In short-form relationship() it can also be the third positional argument. See Configuring fact-dimension compatibility for details.
nullable	Whether the joining column on the "many" side can contain NULL or unmatched values (defaults to true, which generates LEFT JOIN). Set to false to assert referential integrity, so Holistics generates an INNER JOIN instead. See How Holistics handles joins.
rlp_propagation	How row-level permission filters flow through the relationship: 'inherit', 'one_way', or 'two_way' (defaults to 'inherit', which follows the relationship's direction). See Row-level permission and filter direction.