CMiC Analytics: Catalog Builder - Folder Joins

• CMiC Analytics: Catalog Builder - Folder Joins
• Folder Joins
  • Overview – Folder Joins
  • View Joins
  • Navigation Panel (Folder Joins)
  • Sort Panel (Folder Joins)
• Import Joins
  • Import All Joins for a Selected Folder
  • Import a Join for a Specific Folder Pair
• Create, Delete Joins
  • Create Joins
  • Delete Joined Fields
  • Joining Three or More Tables – Example Relating to Data Query
  • Join – Property Pane Section
  • Joined Fields – Property Pane Section

Folder Joins

Overview – Folder Joins

Folder Joins Screen

This screen is used to create the joins between folders (tables/views) that will return the desired data to chart in BI Dashboard Builder. Also, in BI Dashboard Builder, filters and sorts for the returned data can be specified. Joins are used to combine data from two or more tables based on a related column between them.

NOTE: To modify folder joins, the user must have the following system privilege: ‘BIMODJOINS - Allows the user to modify Folder Joins in BI Catalog Builder’.

View Joins

As shown in the previous screenshot, when a folder is selected in the left pane, if joins exist between it and any other folders, the titles of the folders with joined fields are highlighted with red text in the right pane, along with the titles of business areas that also contain joined folders.

If a required, logical join does not exist, users can create it, as per the Create Joinsarticle.

To view the joined fields between the folder on the left and the folder on the right, click the folder on the right, and the joined field(s) will be listed in the Joined Fields section of the property pane (right-most pane).

Navigation Panel (Folder Joins)

A Navigation panel allows users to navigate the data source tree and selected tree nodes using drop-down lists. Each drop-down list has search functionality. Users can quickly navigate to the top levels of selected nodes by using the Arrow icons () next to the required level.

Sort Panel (Folder Joins)

The Sort panel allows users to sort business areas, folders and fields by:

• Default (using property position)

• Name Ascending

• Name Descending

Import Joins

This feature allows users to import joins from the Default Data Source, which is particularly useful for customers that have access to BI Catalog Builder, but don’t have the ability to modify joins (e.g., Cloud customers).

Users can import all joins for a specific folder or for a specific folder pair from the Default Data Source and use them with their own folders.

Also, when CMiC introduces new joins or modifies existing ones in the Default Data Source, users will be able to import these new joins into their custom folders.

Import All Joins for a Selected Folder

The [Import All Default Joins] button becomes enabled when a folder is selected in the left pane. Clicking this button will copy all joins for the corresponding database object of this selected folder. If the folder has joins that don’t exist in the Default Data Source, those joins won’t be modified. Other joins will be overridden.

Example of importing all default joins for a selected folder

Import a Join for a Specific Folder Pair

The [Import Default Join] button becomes enabled when a folder pair is selected on the left and right panes, indicating that a join will be created between these two selected folders. Clicking this button will copy the join between corresponding database objects of the selected folders from the Default Data Source. If there is already an existing join, it will be overridden.

Example of importing a join for a specific folder pair

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.