A relationship between two models specifies how those two models can be joined in a Dataset. It includes the two fields as join key, and the relationship type between them.
At the model structure tab, you can add relationships to a dimension (Base Dimension or Custom Dimension) but not to Measures.
By default, Holistics only support one-to-one (1 - 1) and one-to-many (1 - n) relationships. Please refer to the following sections about how to create:
Relationship on two keys (or more)
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 like:
It is not possible to specify a many-to-many (n - n) relationship directly. To join models with n - n relationship, you will need a junction model so that the relationships is turned in to 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.
If you decide to write a query to transform data, you can write like this: