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.
Currently, Hashboard joins are always LEFT OUTER JOINS. Any unmatched rows from the joined model will simply have blank values for the attributes.
There are two steps to setting up a join between two models:
- Configuring a primary key on the joined model (e.g. Customers)
- Configuring a foreign key on the base model (e.g. Sales)
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:
Navigate to the model builder for the relevant model.
Ensure you have defined an attribute for the primary key to be assigned to.
Select an attribute and click
Set as primary keyin its configuration pane.
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.
Navigate to the model builder for the base model.
Ensure the attribute you wish to use for the join exists on the model.
Join modelsicon button.
Select an attribute on the base model that corresponds to the primary key you previously configured, and select the model to join in.
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.
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:
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:
name column is not unique across joined models, the SQL won't be valid. This can solved by doing the following:
- Set an
aliasfield 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:
- Use the alias you just set as a table name in the custom SQL you wrote: