Relationships

Relationships determine how entities in your semantic models are associated, forming a web of interconnected data. This document explores the significance, types, and practical implementation of relationships in semantic mapping.

What are relationships?

Relationships are the links between different entities in a semantic model. They enable data from one entity to be effectively used in conjunction with data from another, creating a network of interconnected data points. These relationships reflect real-world associations between the various concepts and processes of your business.

Relationships are defined with the following schema in the semantic model:

relationships:
  - name:
    referenced_entity:
    on: "$foreign_key = $referenced_entity.primary_key"
    type:
    meta:
      description:

This table outlines the purpose of each relationship property:

Properties	Description	Example
name	Unique identifier for the relationship	opportunities
referenced_entity	The entity that this relationship connects to	opportunity
on	Condition defining how entities are related	"$src__account_id = $opportunity.src__id"
type	Type of relationship; either one_to_many or many_to_one	one_to_many
meta (optional)	Metadata for the relationship, typically a description	description: Opportunities related to this account

Why are relationships important?

Establishing relationships in a semantic model offers several key benefits:

• Integrating information: Connecting different entities - from different sources especially - enables metrics and analytics that surface unique insights.
• Holistic data exploration: Relationships enable complex analytical questions that rely on 'joins' and 'aggregations' across entities.
• Data consistency: Relationships enable the semantic model to "define once and use everywhere", referencing and aggregating dimensions from a single source rather than recreating the same fields in different entities.

Creating a new relationship

When creating or modifying entities' relationships, there are 3 main steps to ensure accuracy and reliability.

Selecting the referenced entity

The first step is simple, select the unique identifier of the entity which this relationship is connecting. For example, we can connect an entity to the user who is the owner of our entity:

relationships:
  - name: owner
    referenced_entity: user

Entities can have several relationships between them. The name property is the unique identifier which is used in further defining the entity's dimensions and calculating metrics based on this entity. For example, connecting to a user through an owner or a created_by relationship.

Defining keys

Keys are used to accurately bridge different entities. Just like in SQL, there are primary and foreign keys. Primary keys are the entity's id or other unique identifier field while foreign keys are the primary keys of other related entities. For example, a customer has an $src__owner_id foreign key that is equal to the $src__id primary key of the user:

relationships:
  - name: owner
    referenced_entity: user
    on: "$src__owner_id = $user.src__id"

By comparing foreign and primary keys, relationships make the correct connections between instances of different entities.

Choosing relationship type

There are two types of relationships an entity can have; many-to-one and one-to-many. The type of relationship can be determined by answer the question:

"Am I _pointing at an entity or are other entities pointing at me?"._

Where "pointing" typically means having a foreign key equal to the primary key of the referenced entity.

Many-to-one

In a many-to-one relationship, the entity we are modelling is pointing at another specific entity. It has a foreign key that is being compared to the primary key of the target entity. It's called "many-to-one" because there are "many" of the modelled entity that are pointing at "one" related entity with this kind of relationship. This can be any kind of "parent" relationship such as the owner of a customer, the account contacts are related to or the specific product the line item is for.

For example, this relationship on the customer defines its association to the owner who is working it:

relationships:
  - name: owner
    referenced_entity: user
    on: "$src__owner_id = $user.src__id"
    type: many_to_one
    meta:
      description: The owner of this customer

In this example, the customer has a foreign key - $src__owner_id - that is equal to the user's primary key - $src__id.

One-to-many

In a one-to-many relationship, the entity we are modelling is associated with multiple instances of another entity. It's called "one-to-many" because this kind of relationships connects one instance of the modelled entity to "many" instances of the related entity. This is typical in scenarios of many "child" entities like a single customer having multiple orders or an campaign being linked to numerous campaign members.

For example, consider this relationship on the account entity that defines its association with multiple opportunities:

relationships:
  - name: opportunities
    referenced_entity: opportunity
    on: "$src__account_id = $opportunity.src__id"
    type: one_to_many
    meta:
      description: Opportunities related to this account

In this example, each account is associated with several opportunities. The account has a primary key - $src__account_id - which corresponds to a foreign key in the opportunity entity - $src__id.

Using relationships

Utilizing relationships in semantic models is fundamental for defining dimensions and calculating metrics in a meaningful way. By leveraging relationships, dimensions can be enriched with data from related entities, enhancing the depth and context of analytics.

In dimension definition

When defining dimensions, relationships enable the inclusion of attributes from related entities. For instance, you can define a dimension in an order entity that includes the customer_first_purchase_date from a related customer entity. This enriches the 'Order' data with customer information, providing a more complete view.

dimensions:
  - name: customer_first_purchase_date
    type: timestampz
    sources:
      - sql: $customer.first_purchase_date
    meta:
      display_name: Customer first purchase date
      description: The date of the customer's first purchase.

In metric calculation

Relationships are also instrumental in calculating metrics that require aggregation of multiple related entities. For example, calculating the total revenue from all orders linked to a specific customer requires a one-to-many relationship between the customer and order entities.

For more information on using relationships in metric calculations, please consult the Joins document in our metric syntax documentation.

Wrapping up

Relationships create the necessary connections between entities in your semantic model. They provide the means to integrate information from various sources, enabling comprehensive and consistent analytics.

In this document, we've outlined the importance of relationships, the types of relationships available, and how they can be used. By applying this information, you can ensure that semantic models are not only accurately representing real-world relations but also unlocking consistent, complex analytics that produces unique insights.