Skip to main content

Model Relationship

Knowledge Checkpoint

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

Once you've created your data models, you can specify how they should be joined together by setting up Relationships.

This step is needed for Holistics to build the right SQL query when you combine fields from different models to create a report.

Differences between relationship in Holistics 3.0 and 4.0

For both versions, relationship is used to specify how data models are joined together. However, in Holistics 3.0, relationship is dependent on the data models. It cannot exist without predefined data models.

In Holistics 4.0, relationship definition is decoupled from data model. This means Relationship definitions can be placed inside data model files, or dataset files or as separate files:

// Approach 1: Relationship defined within the Data Model file
// File ecommerce.model.aml
model my_model {
...
}
relationship my_relationship {
...
}

// ---------------------
// Approach 2: Relationship defined in a Dataset file
// File ecommerce.dataset.aml
dataset abc {
models: [model_1, model_2]
relationships: [ relationship_config ]
}

// ---------------------
// Approach 3: Relationship defined in a separated file
// File relationship.aml
relationship aa {
...
}

When you delete your data models, the relationships between them are not deleted unless you explicitly do so.

Creating Relationship

info

Please refer to AML Relationship to learn more about all available parameters and their example usage.

In Holistics 4.0, a relationship can be defined in a dataset, in a model or as separate relationship files. You can either use the interactive UI to create a relationship, or define them programatically using AML syntax.

  • Using the interactive UI to define Relationship:

    Note that the current UI only supports defining dataset-level relationships, so this UI will only appear in dataset files, i.e files ending with .dataset.aml.

Automatic Relationship Creation

In some databases where foreign key constraints are already implemented, Holistics will automatically detect these constraints and turn them into relationships.

Currently, this feature is available for the following databases:

  • PostgreSQL
  • MySQL
  • Microsoft SQL Server
  • Redshift

Note: At the moment, Automatic Relationship Creation is available in Holistics 3.0 only. It will also be introduced in Holistics 4.0 in the future.

Relationship on Composite Keys

There are cases where models must be joined on two keys. For example, last year's sales aggregation by cities vs. this year's sales aggregation by cities:

The intended query is:

select
ty.city
, ty.month
, sales_this_year
, sales_last_year
from this_year ty
join last_year ly
on ty.city = ly.city and ty.month = ly.month -- using two keys

In Holistics, to replicate this behavior, you can concatenate the individual keys to create compound keys (using Custom Dimensions), and add a relationship on these fields.

With this approach, the generated query will be:

select
ty.city
, ty.month
, sales_this_year
, sales_last_year
from this_year ty
join last_year ly
on concat(ty.city, ty.month) = concat(ly.city, ly.month) -- using one compound key

Handling many-to-many (n-n) relationship

It is not possible to specify a many-to-many (n - n) relationship directly. To join models with n - n relationships, you will need a junction model, which you can create with a Query Model, so that the relationship is interpreted as 1 - n - 1.

Consider the following case where you have two models: products and merchants. Products can have multiple merchants, and a merchant can provide many products.

To join these two models, you will need a products_merchants junction model:

After that, you are good to explore datasets with those 3 data models.

Common got-chas

Fan-out issues

Note

This issue will not occur if you define your model measures using Holistics AML

When you set relationships between models (many-to-one or one-to-one ), we assume that the fields at the "one" end is already unique.

If the field is not unique, when you drag in fields from those two models, a fan-out will happen. The result set will be a Cartesian product of the two models and may "explode" into millions of rows.

This would result in the fan-out error in the Data Exploration window.

Visit Cannot combine fields due to fan-out issues? to learn more about this error and how to troubleshoot it.


Let us know what you think about this document :)