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:
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 like:
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
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:
select p.id as product_id , p.name as product_name , p.price , m.name as merchant_name , m.address as merchant_address from products_merchants pm left join products p on pm.product_id = p.id left join merchants m on pm.merchant_id = m.id
Updated 4 months ago
Start exploring your data models with Dataset