Model Relationship
Knowledge Checkpoint
We think it would be best if you have a clear understanding of these concepts before reading this documentation:
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.
Creating Relationship
- 3.0
- 4.0
Create 3.0 Relationship
To create a relationship:
- Go to the model view
- At a Dimension field, click on Context Menu -> Add Relationship
- Choose the destination model and field you want to link it to.
- Currently Holistics support one-to-one (1-1) and one-to-many (1-n) relationships
For example, I will connect contestants model and users model by adding a relationship on email field:
Once you define the relationships, Holistics shows you how the models are linked together.
Create 4.0 Relationship
info
Please refer to AML Relationship Reference to learn more about all available parameters and their example usage.
In 3.0, you define relationships at the model level. When creating the dataset, the relationships are pre-loaded and users only need to toggle.
In 4.0, we keep and extend this.
- Relationships can also be defined inside an AML model file and reused in the dataset.
- You can also define the (ad-hoc) relationship directly inside the AML dataset file.
To define relationship, you need to specify:
- Relationship type
- The dimension used as join field between two related models
Additionally, to make relationships functional in AML dataset, you need to also specify the configuration of the relationship (active status).
Relationship Syntax
// Relationship full form defined in dataset file
RelationshipConfig {
rel: Relationship {
type: 'many_to_one | one_to_one'
from: ref('model_name','dimension_name')
to: ref('model_name','dimension_name')
}
active: true | false
}
// Relationship short form defined in dataset file, relationship type > is many_to_one
relationship(model_a.field_name > model_b.field_name, true)
// Relationship short form defined in dataset file, relationship type - is one_to_one
relationship(model_a.field_name - model_b.field_name, true)
// Relationship defined in model file
Relationship relationship_name {
type: 'many_to_one | one_to_one'
from: ref('model_name','dimension_name')
to: ref('model_name','dimension_name')
}
// Relationship imported from model file to dataset file
import '../path/to/file/store/relationship_file' { relationship_name }
RelationshipConfig {
rel: relationship_name
active: true | false
}
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
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 Transform 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.