Role-Playing Dimensions Pattern

What are Role-Playing Dimensions?

Role-playing dimensions are when the same dimension is used multiple times in a fact table, each time with a different meaning. The classic example is dates: order date, ship date, delivery date all reference the same date dimension.

For example, an Order model contains information about when an order is created, delivered, cancelled, and refunded. You want to enable users to analyze orders by any of these dates - but they all refer to the same underlying date dimension.

Visual:

The Challenge

In Holistics, only ONE relationship between two models can be active at a time.

This means you cannot have multiple active relationships from fct_orders to dim_dates simultaneously. If you try to analyze orders by date, which date should be used? Created date? Delivered date?

Solution

Holistics provides two approaches to handle role-playing dimensions:

1. Using with_relationships() - Define multiple relationships (only one active) and explicitly specify which relationship each metric should use
2. Using extend() - Create separate extended models for each role, avoiding the one-active-relationship limitation

Let's explore both approaches.

Approach 1: Using with_relationships()

This approach uses a single date dimension with multiple relationships, where you explicitly specify which relationship to use for each metric.

Step 1: Define Models

First, create your fact model with multiple date fields that will reference the same date dimension. Notice how each date field represents a different stage in the order lifecycle.

fct_orders.model.aml

Model fct_orders {
  type: 'table'
  table_name: 'ecommerce.orders'
  data_source_name: 'your_datasource_name'

  dimension id {
    label: 'Order ID'
    type: 'number'
    primary_key: true
    hidden: true
  }

  // Multiple date fields (role-playing)
  dimension created_at {
    label: 'Created At'
    type: 'datetime'
  }

  dimension delivered_at {
    label: 'Delivered At'
    type: 'datetime'
  }

  dimension cancelled_at {
    label: 'Cancelled At'
    type: 'datetime'
  }

  dimension refunded_at {
    label: 'Refunded At'
    type: 'datetime'
  }

  measure order_count {
    label: 'Order Count'
    type: 'number'
    definition: @aql count(fct_orders.id);;
  }
}

dim_dates.model.aml

Model dim_dates {
  type: 'table'
  table_name: 'ecommerce.dates'
  data_source_name: 'your_datasource_name'

  dimension date {
    label: 'Date'
    type: 'date'
    primary_key: true
  }

  dimension month {
    label: 'Month'
    type: 'text'
  }

  dimension quarter {
    label: 'Quarter'
    type: 'text'
  }
}

Step 2: Define Dataset with Multiple Relationships

Set up your dataset with multiple relationships to the same dimension. Only one can be active by default, and you'll use with_relationships() to specify which one to use for each metric.

role_playing_example.dataset.aml

// Dataset with role-playing dimension
Dataset role_playing_example {

  data_source_name: 'your_datasource_name'

  models: [fct_orders, dim_dates]

  relationships: [
    // Only ONE active by default
    relationship(fct_orders.created_at > dim_dates.date, true),      // Active
    relationship(fct_orders.delivered_at > dim_dates.date, false),   // Inactive
    relationship(fct_orders.cancelled_at > dim_dates.date, false),   // Inactive
    relationship(fct_orders.refunded_at > dim_dates.date, false)     // Inactive
  ]

  // Default metric uses active relationship (created_at)
  metric total_created_orders {
    label: 'Total Created Orders'
    type: 'number'
    definition: @aql fct_orders | count(fct_orders.id);;
  }

  // Override with with_relationships() for other dates
  metric total_delivered_orders {
    label: 'Total Delivered Orders'
    type: 'number'
    definition: @aql
      fct_orders
        | count(fct_orders.id)
        | with_relationships(fct_orders.delivered_at > dim_dates.date)
    ;;
  }

  metric total_cancelled_orders {
    label: 'Total Cancelled Orders'
    type: 'number'
    definition: @aql
      fct_orders
        | count(fct_orders.id)
        | with_relationships(fct_orders.cancelled_at > dim_dates.date)
    ;;
  }
}

Step 3: Query with Role-Playing Dimensions

explore {
  dimensions {
    dim_dates.date
  }
  measures {
    total_created_orders,
    total_delivered_orders,
    total_cancelled_orders
  }
}

Key Points for with_relationships() Approach

This approach gives you:

• Explicit control over which relationship each metric uses
• Works well for metrics defined in datasets
• Trade-off: Requires with_relationships() for every metric using inactive paths

Approach 2: Using extend()

AML Extend provides a cleaner way to implement role-playing dimensions without the one-active-relationship limitation. Instead of managing multiple relationships to one model, you create separate extended models for each role.

How it Works

With AML Extend, you define one base Date model and extend it into different models for each role (Created Date, Delivered Date, etc.). This avoids the relationship conflict entirely.

Implementation with extend()

Start with a base date model, then extend it into separate models for each role. Each extended model points to the same underlying table but has its own label and field names.

dim_dates.model.aml

// Base date model
Model dim_dates {
  label: 'Dates'
  type: 'table'
  table_name: 'ecommerce.dates'
  data_source_name: 'your_datasource_name'

  dimension date {
    label: 'Date'
    type: 'date'
    primary_key: true
  }

  dimension month {
    label: 'Month'
    type: 'text'
  }

  dimension quarter {
    label: 'Quarter'
    type: 'text'
  }
}

created_date.model.aml

// Extend for different roles
Model created_date = dim_dates.extend({
  label: 'Created Date'
  dimension date {
    label: 'Created At'
  }
})

delivered_date.model.aml

Model delivered_date = dim_dates.extend({
  label: 'Delivered Date'
  dimension date {
    label: 'Delivered At'
  }
})

cancelled_date.model.aml

Model cancelled_date = dim_dates.extend({
  label: 'Cancelled Date'
  dimension date {
    label: 'Cancelled At'
  }
})

ecommerce.dataset.aml

Dataset ecommerce {
  data_source_name: 'your_datasource_name'

  models: [fct_orders, created_date, delivered_date, cancelled_date]

  relationships: [
    // Each extended model has its own active relationship
    relationship(fct_orders.created_at > created_date.date, true),
    relationship(fct_orders.delivered_at > delivered_date.date, true),
    relationship(fct_orders.cancelled_at > cancelled_date.date, true)
  ]
}

Querying with Extended Models

explore {
  dimensions {
    created_date.date,
    delivered_date.date,
    cancelled_date.date
  }
  measures {
    fct_orders.order_count
  }
}

Result: Users see separate date dimensions for each role, making it intuitive to choose the right date

Key Points for extend() Approach

This approach offers:

• Cleaner and more concise - no need for with_relationships()
• All relationships can be active - avoids the one-active-relationship limitation
• No duplication - extends from a single base model
• Trade-off: Too many date fields - might confuse end-users when they want to apply date filter in dashboard

Additional Resources

• Back to Overview
• AML Extend Documentation
• with_relationships() Reference