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.

Create Relationships

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)
• Many-to-many relationship

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:

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

Many-to-many relationship

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 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.

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