Create, Delete Joins

Joins between folders determine what gets charted in BI Dashboard Builder, as each returned row gets charted. Thus, the join type (Inner, Left Outer, Right Outer) is quite important, as the set of returned results for each type can vary greatly.

The following guidelines are recommended when creating joins:

1. When creating an INNER join, always keep Lookup folders on the right side, as this will allow the join to appear correctly in ERD diagrams.

2. Don’t forget to make OUTER joins, as required.

   • If at least one of the joined fields is optional, most likely the join must be an OUTER type join.

   • The direction of the OUTER join (LEFT or RIGHT) should be pointing to a Detail folder.

     For example, for an OUTER join:

     Left Folder: “JC Jobs”

     Right Folder: “SD Regions”

     Join Fields: “JC Jobs – Location Code” and “SD Regions – Region Code”

     As Location Code field in JC Jobs is optional, the join must be an OUTER type join. In this case, “JC Jobs” is a “Detail” folder and “SD Regions” is a “Lookup” folder. Therefore, the OUTER join should be a “LEFT OUTER” join pointing to the “JC Jobs” folder side.

Create Joins

To create a join, select the left Folder’s Field from the left pane, and select the right Folder’s Field from the right pane, which causes the [>> Join Fields <<] button between the panes to become enabled. Click the button to create the join, and the joined fields will be listed in the Joined Fields section (rightmost side of screen). Then, use the Join section to set the join’s type (Inner, Left Outer, Right Outer) via the Join Type field.

Delete Joined Fields

To delete joined fields, either select the two joined fields via the panes and click the [<< Unjoin Fields >>] button between the panes, or click the icon of the joined fields in the Joined Fields section.

Also, the [Delete Join] button in the Join section can be used to delete all of a join’s joined fields, deleting the join entirely.

Joining Three or More Tables – Example Relating to Data Query

Example of BI Dashboard Builder, using three joined tables from a data source created in BI Catalog Builder

To set up joins for a query statement that joins three or more folders (tables/views), simply specify the join between the first two folders, then the join between the second and third folder, and so on. In BI Dashboard Builder, the application is smart enough to know how to combine them to return the specified columns.

Join – Property Pane Section

Sample of Join section used to define joins

The following provides details about this section’s button and fields:

[Delete Join] – Button

Deletes the join.

Left Folder

Name of join’s left folder.

Join Type

Since each returned result for joined folders gets charted, the join type affects what gets charted in BI Dashboard Builder.

Join Type	Description
Inner	A row is returned only for matched rows of joined folders.
Left Outer	A row is returned for every row of left folder, with every field from the right folder set to NULL if there was no match between the left folder’s row and a row in the right folder.
Right Outer	A row is returned for every row of right folder, with every field from the left Folder set to NULL if there was no match between the right folder’s row and a row in the left folder.

Right Folder

Name of join’s right folder.

Active – Checkbox

If checked, join is active for data source.

Joined Fields – Property Pane Section

Example of Join Fields section, which lists joins between fields of selected folders

This section lists the joined fields between the selected folder on the left and the selected folder on the right.

The icon is used to delete the corresponding joined fields.