Model Joins

In many cases, it can be useful to reuse data from one data model in others. This can be done in Hashboard by configuring joins across models. In essence, this is defining a primary-foreign key relationship across two Hashboard models.

After configuring a join, you'll be able to use any attributes from the joined model in the base model. Note that joins only include the model attributes, not the model measures.

Setting up

There are two steps to setting up a join between two models:

1. Configuring a primary key on the joined model (e.g. Customers)
2. Configuring a foreign key on the base model (e.g. Sales)

1. Configuring a primary key

You first need to configure a primary key on the model you wish to join. Primary keys must meet the following conditions:

• They must have a string or numeric type.
• They must be unique in the underlying data table.

You can configure a primary key using the following steps:

1. Navigate to the model builder for the relevant model.

2. Ensure you have defined an attribute for the primary key to be assigned to.

3. Select an attribute and click Set as primary key in its configuration pane.

2. Configuring a foreign key

Once you've set up a primary key on the model you wish to join, you can set up a foreign key on the base model. The foreign key's type must match the primary key.

1. Navigate to the model builder for the base model.

2. Ensure the attribute you wish to use for the join exists on the model.

3. Click the Join models icon button.

4. Select an attribute on the base model that corresponds to the primary key you previously configured, and select the model to join in.

5. Click Join.

At this point, you'll see the name of the joined model attached to the attribute you just configured:

This means the join is fully configured. Once you save your model, you can start using the joined attributes.

Using joined attributes

You can use joined attributes anywhere you use regular model attributes! Throughout Hashboard, anywhere you configure filters, breakouts, axes, and more, you'll see the attributes from the base model as well.

For example, they'll be added into the attribute tray alongside your pre-existing attributes:

Using joined columns in custom SQL

It's also possible to write custom attribute or measure SQL that utilizes columns from joined models. However, if you write e.g. the following query:

UPPER(name)

and the name column is not unique across joined models, the SQL won't be valid. This can solved by doing the following:

1. Set an alias field on the model that owns the column you are trying to reference. This can be done via the YML Editor in the UI, or via Data Ops:

alias: customers_model

2. Use the alias you just set as a table name in the custom SQL you wrote:

UPPER(customers_model.name)