Create dashboard relationships

Important

This feature is in Public Preview.

Define relationships and cross-dataset measures in an AI/BI dashboard to model joins once and use them across all visualizations, instead of pre-joining data in SQL or duplicating join logic across datasets. For an explanation of how relationships work and the data models they support, see Dashboard relationships.

Requirements

To use dashboard relationships, your workspace must meet the following requirements:

  • The Dashboard Relationships Public Preview must be enabled for your workspace. A workspace admin can enable the feature from the Previews portal. See Manage Azure Databricks previews.
  • You must have CAN EDIT permissions on the dashboard.
  • You must have CAN USE permissions on a SQL warehouse.
  • The dashboard must have at least two datasets.

Define a relationship

A relationship joins two dashboard datasets on compatible fields and defines cardinality so the query engine knows how to combine them correctly.

To create a relationship:

  1. Open an AI/BI dashboard in edit mode.
  2. Click the Data tab.
  3. In the Relationships pane, click Add relationship. If the dashboard doesn't have a semantic model yet, first click Get started with Relationships. Azure Databricks initializes a semantic model from the datasets already in the dashboard.
  4. Select the left dataset and the join field from that dataset.
  5. Select the right dataset and the join field from that dataset.
  6. Select the cardinality:
    • Many-to-One: Use when the left dataset is a fact table and the right dataset is a dimension table (for example, web_sales.customer_id = customers.customer_id).
    • One-to-Many: Use when the left dataset is a dimension table and the right dataset is a fact table.
  7. Click Save.

After you save the relationship, the query engine automatically resolves the joins each visualization requires at runtime. Filters on dimension tables propagate to related fact tables without requiring manual correlated subqueries in each dataset definition.

Add a cross-dataset measure

Cross-dataset measures are reusable calculations that reference fields across related datasets. You define them in the Relationships pane, and they are available in the visualization editor for any widget that uses the modeled datasets.

To add a cross-dataset measure:

  1. In the Relationships pane, click Cross-dataset measure.
  2. Enter a name for the measure.
  3. Define the measure expression, referencing fields from any related dataset.
  4. Click Save.

Cross-dataset measures are only available after you define at least one relationship between the relevant datasets.

Use the field picker

When you build a visualization, the field picker surfaces fields from all datasets connected by relationships, grouped by dataset name. As you select fields, the field picker updates availability based on what the query engine can resolve. Fields that would result in an ambiguous or unsupported join path are excluded.

The order in which you select fields affects which fields remain available. For an explanation of why, see Why field order matters.

Limitations

The following limitations apply to dashboard relationships in this Public Preview:

  • Dashboard relationships are scoped to the dashboard and aren't reusable across other surfaces.
  • Relationships must join two datasets on a single equality condition. Many-to-many joins, cyclical graphs, and ambiguous join paths are not supported.
  • Cardinality is limited to Many-to-One and One-to-Many. Model direct many-to-many relationships using a bridge table instead.
  • A dashboard supports at most one semantic model.

Additional resources

  • Last updated on