Skip to main content

​
Creating joins in the workbook

Unless promoted to the shared model, joins created in workbooks will only be available in the workbook where they were created.Queriers can create joins in workbooks, but Modeler or Connection Admin permissions are required for promotion.
In the field browser, right-click on a table and select Joins > New join. This opens the join builder:
Diagram showing the architecture of the system
From here, you can use the Builder or SQL tabs to define the join:
1
The left and right sides of the Builder tab represent the views you want to join. On each side, select the fields that should be used to join the views.For example, selecting orders.customer_id and customers.id.
2
Select a Type to define what type of join this is. Omni defaults to Left (LEFT JOIN), but you can change this. Refer to the join_type reference for more information about this setting.
3
Select a Relationship for the join. This setting is used to ensure that counts across the tables are accurate. If you’re not sure what this setting should be, click the Infer relationship button at the bottom left corner of the modal.Omni will check the cardinality between the datasets and select the relationship type on your behalf. Refer to the relationship_type reference for more information about this setting.
4
Check the Reversible box allow for exploration in both directions of the join.
This setting controls how fan out is handled and thus impacts the shape of result sets. Refer to the reversible reference for more information.
5
Use the Add to topic settings to select if the join should be added to the current topic, all possible topics, or not added.
6
Click Save to create the join.
The SQL tab is useful if you’re creating a complex join or just prefer writing in SQL:SQL tab in the join modal, useful for creating complex joins
1
Select the source and target views using the dropdowns.
2
In the code block, write the join using SQL. Use ${view_name.field_name} syntax to reference fields.
3
Select a Type to define what type of join this is. Omni defaults to Left (LEFT JOIN), but you can change this. Refer to the join_type reference for more information about this setting.
4
Select a Relationship for the join. This setting is used to ensure that counts across the tables are accurate. If you’re not sure what this setting should be, click the Infer relationship button at the bottom left corner of the modal.Omni will check the cardinality between the datasets and select the relationship type on your behalf. Refer to the relationship_type reference for more information about this setting.
5
Check the Reversible box allow for exploration in both directions of the join.
This setting controls how fan out is handled and thus impacts the shape of result sets. Refer to the reversible reference for more information.
6
Use the Add to topic settings to select if the join should be added to the current topic, all possible topics, or not added.
7
Click Save to create the join.
Saving the join will add a new relationship definition to the workbook model’s relationships file, which you can view by clicking Model > Model layers > Workbook.
Promote the join to the shared model to make it accessible outside the workbook.

​
Defining relationships in the model IDE

Connection Admin or Modeler permissions are required to access the model IDE.
For a code-forward approach, you can use the model IDE to create and modify joins.
1
Open the model IDE by clicking Develop in the left navigation, then clicking the model you want to work with.
2
In the Settings section, click Relationships.
3
The relationships file will open in the editor panel. Add a relationship definition, which will look similar to the following example:
- join_from_view: main__orders
  join_to_view: main__customers
  join_type: always_left
  on_sql: ${main__orders.customer_id} = ${main__customers.id}
  relationship_type: assumed_many_to_one
Click the Template tab in the above code block for a copy + pasteable relationship definition!
4
Define the parameters to complete the join. In addition to the parameters in the above example and template, you can add parameters like where_sql or reversible to further customize the relationship. Refer to the Relationship parameters reference for more information and examples.
5
Click Save changes.
If the model is currently configured to require branches, merge the branch to apply the changes to the shared model.If the model also uses the git integration, you may need to create a pull request to accomplish this.