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.
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.
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:
- Microsoft SQL Server
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:
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:
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:
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.