This article applies to the Admin Console > Data > Joins settings.
Joins specify to Exago the relationship between Data Objects.
All existing Joins are listed in the Main Menu under Data. All the joins you are adding or editing will be displayed in a tab entitled Joins.
Create, Edit, Delete Joins
- To add a new join click Joins in the Main Menu and either:
- click the Add icon at the top of the main menu
- right-click and select Add from the context menu
- or to quickly add data joins, use the Automatic Database Discovery tool
- To edit a join either:
- double click it
- select it and click the Edit icon at the top of the main menu
- right-click it and select Edit from the context menu
Important
Review the Modifying Joins section below when making changes to joins in the Admin Console.
- To delete a join either:
- select it and click the Delete icon at the top of the main menu
- right-click it and select Delete from the context menu
- To save changes and new joins click the Apply or Okay buttons.
Join Properties
Every join has the following properties:
From Object
The first Data Object you would like to join.
To Object
The other Data Object you would like to join.
- The order of the Data Objects is important if you have a one-to-many relation type or a Left/Right Outer Join type. See below for details.
Join Column(s)
Specify the field(s) of each Data Object that must match to join an entity in the From Object to an entity(s) in the To Object.
Join Type
Specify whether rows from either Data Object that do not have a match should or should not be included.
- Inner — include only rows of the From Object that have a match in the To Object and vice versa.
- Left Outer — include rows of the From Object that do not have a match in the To Object but not vice versa.
- Right Outer — include rows of the To Object that do not have a match in the From Object but not vice versa.
Note
SQLite Data Sources do not support right outer joins.
- Full Outer — include rows in either Data Object that do not have a match.
Note
MySQL Data Sources and SQLite Data Sources do not support full outer joins.
Relationship Type
Specify if the join type is one-to-one or one-to-many.
- One-to-One — each row in the From Object can join to at most one row from the To Object.
- One-to-Many — each row in the From Object can join to any number of rows from the To Object.
Weight
Give a join weight in order to set its precedence when multiple join paths exist between Data Objects. The path with the higher weight will be utilized.
- A report contains three Data Objects ‘Students’, ‘Professors’ and ‘Comp Sci 101.’ Students is joined to ‘Professors’ and ‘Comp Sci 101.’ Additionally ‘Professors’ is joined to ‘Comp Sci 101.’ There are two available join paths between ‘Students’ and ‘Comp Sci 101.’ Adding weight to a join will clarify which of the two paths Exago should use.
Modifying Joins
Although joins are created in the Admin Console, they are also saved within each individual report. If changes are made in the Admin Console, the affected reports need to refresh their joins. To do so:
- Open the report for editing in the Report Designer.
- From the Advanced Options > Joins menu, click the Recreate button.
- Click Okay on the confirmation dialog.
For more information, review Advanced Report Designer: Advanced Options.
Cross Source Joins
Data Objects from different Data Sources can be joined together.
Caveats
- Filter Dependency only works when all data objects originate from the same Data Source.
- The data types and size of the joined columns must match exactly. For example, an
Unsigned Int
column may only be joined with anotherUnsigned Int
column.
ImportantCross Source Joins are done in-memory, so this process may be memory intensive for very large data sets.