​
1. Introduction to Relationships

Relationships define how different semantic models connect to each other, similar to specifying join conditions in SQL. Defining these connections is crucial for enabling queries that combine data from multiple models.

By defining relationships, you allow the semantic layer to understand how to join data, enabling metrics, dimensions, and filters that reference fields across different models.

​
Basic Structure

Relationships are defined within the relationships: list inside a model definition. Each item in the list represents a link to another model:

models:
  - name: current_model_name
    # ... dimensions, measures, metrics ...
    relationships:
      - name: related_model_name  # Required: Name of the model being linked TO
        primary_key: pk_field     # Required: Key field in the *related* model
        foreign_key: fk_field     # Required: Key field in the *current* model
        type: LEFT                # Optional: Join type (LEFT, INNER). Default: LEFT
        cardinality: many-to-one  # Optional: Relationship cardinality (see below)
        description: "Explanation"  # Optional: Business context for the relationship

​
2. Defining Relationships

Each relationship entry requires specifying how the current model links to the related model.

​
Required Fields

  • name: The name of the related semantic model you are connecting to.
  • primary_key: The name of the field (usually a dimension) in the related model that acts as the join key on that side.
  • foreign_key: The name of the field (usually a dimension) in the current model that acts as the join key on this side.

​
Optional Fields

  • description: A clear explanation of the relationship in business terms (e.g., “Customer who placed the order”). Highly recommended for clarity.
  • type: The join type to use when connecting the models in a query. (See “Join Types” below).
  • cardinality: The nature of the relationship between the models. (See “Cardinality Types” below).

​
Simple Example: Orders to Customers

In an orders model, linking to a customers model:

models:
  - name: orders
    # ... dimensions: order_id, customer_id, order_date ...
    relationships:
      - name: customers         # Link to the 'customers' model
        primary_key: id         # The PK in the 'customers' model is 'id'
        foreign_key: customer_id # The FK in the current 'orders' model is 'customer_id'
        cardinality: many-to-one # Many orders belong to one customer
        description: Customer associated with this order

​
3. Relationship Details

Understanding cardinality and join types is key to defining accurate relationships.

​
Cardinality Types

Cardinality describes the numerical relationship between records in the two connected models. Specify this using the cardinality field.

​
Join Types

The type field specifies the SQL join type used when this relationship is invoked in a query:

  • LEFT (Default): Includes all records from the current model (the one defining the relationship), and matching records from the related model. If no match is found in the related model, fields from the related model will be NULL.
  • INNER: Includes only those records where a match exists in both the current and related models based on the specified keys.

​
Many-to-Many Relationships

Direct many-to-many relationships are typically modeled using an intermediary junction model. Each primary model has a one-to-many relationship to the junction model.

Example: Products and Orders via order_items

models:
  - name: products
    # ... dimensions: id, product_name ...
    relationships:
      # Link from Product to the junction table
      - name: order_items
        primary_key: product_id # Key in junction table
        foreign_key: id         # Key in products table
        cardinality: one-to-many # One product can be in many order items

  - name: orders
    # ... dimensions: id, order_date, customer_id ...
    relationships:
      # Link from Order to the junction table
      - name: order_items
        primary_key: order_id   # Key in junction table
        foreign_key: id         # Key in orders table
        cardinality: one-to-many # One order can have many order items

  - name: order_items # The Junction Model
    # ... dimensions: id, order_id, product_id, quantity ...
    relationships:
      # Link from junction table back to Products
      - name: products
        primary_key: id         # Key in products table
        foreign_key: product_id # Key in junction table
        cardinality: many-to-one # Many items can be the same product

      # Link from junction table back to Orders
      - name: orders
        primary_key: id         # Key in orders table
        foreign_key: order_id   # Key in junction table
        cardinality: many-to-one # Many items can be in the same order

​
4. Using Relationships: Cross-Model References

Once relationships are defined, you can reference dimensions and measures from related models within metrics and filters using dot notation: related_model_name.field_name.

# In the 'orders' model (which has a relationship defined for 'customers')
metrics:
  - name: revenue_per_customer
    # 'amount' is from orders, 'customers.id' references 'id' from the related customers model
    expr: "SUM(orders.amount) / COUNT(DISTINCT customers.id)"
    description: "Average revenue per unique customer. Assumes 'customers' relationship exists."

A query using revenue_per_customer must explicitly include the customers relationship/join for this reference to work. The semantic layer uses the relationship definition to execute the join correctly when requested in the query.

​
5. Best Practices

Define Bidirectionally

For clarity and completeness, define relationships in both related models (e.g., orders links to customers, and customers links back to orders).

Accurate Keys & Cardinality

Ensure primary_key, foreign_key, and cardinality accurately reflect the underlying data structure. Incorrect definitions lead to wrong query results.

Descriptive Labels

Use the optional description field to explain the business meaning of each relationship. This greatly improves model understandability.

Choose Join Type Wisely

Select LEFT or INNER based on whether records must exist in the related model for the query logic.

​
6. Comprehensive Example

Showing bidirectional relationships between orders and customers:

models:
  - name: orders
    description: Customer transaction data.
    # ... dimensions: order_id, customer_id, order_date ...
    # ... measures: amount ...
    relationships:
      # Link from Orders TO Customers
      - name: customers
        primary_key: id         # Customer's PK
        foreign_key: customer_id # Order's FK pointing to customer
        cardinality: many-to-one
        description: Customer who placed the order
      # Potentially link to order_items (junction model)
      # - name: order_items
      #   ...

  - name: customers
    description: Customer account information.
    # ... dimensions: id, name, signup_date ...
    relationships:
      # Link from Customers TO Orders
      - name: orders
        primary_key: customer_id # Order's FK pointing back to customer
        foreign_key: id         # Customer's PK
        cardinality: one-to-many
        description: Orders placed by this customer

​
7. Next Steps

After defining relationships between your models, you can:

  1. Leverage these relationships to define cross-model Metrics and Filters.
  2. View complete examples of semantic layer definitions incorporating relationships.
  3. Deploy your model.