Cluster joins enable you to create a new file structure by linking existing synonyms of two or more relational tables using the same or mixed data sources. For example, you may join a DB2 table and an Oracle table. Use cluster joins to create new views in the metadata by linking together physical tables and easily report against the new view or structure. You can create cluster joins by using the Modeling View of the Synonym Editor.
The Master File that is created combines the fields of the joined tables within a single file. The Access File from the combined file contains information about the actual location of the data sources and the Join information. It also shows how the tables are linked.
The total number of tables that you can add to the tool is 1024 (using 1023 joins), which results in a new Master File that has a maximum of 1024 segments.
The Cluster Join tool enables you to create a Star Schema which consists of a fact table referencing a number of dimension tables. Optionally, you can also create a view that has more than one fact table.
Use the Modeling View to enhance an existing synonym by adding a segment.
The Synonym Editor opens.
The Synonym Editor Modeling View tab opens in the workspace.
The following context menu appears, as shown in the following image.
To insert a segment from an existing synonym:
The Insert Reference to Existing Synonym dialog box opens, as shown in the following image.
Note: Use this method if you are creating a cluster join with an existing table or synonym.
Tip: Click Save As from the Modeling View File menu if you do not want to modify the original synonym.
To insert a segment from an existing synonym with Snowflake:
Select Insert, then Reference to Existing Synonym with Snowflake.
The procedure is the same as Reference to Existing Synonym, except the selection list will show both candidate synonyms and any synonyms that they reference.
To insert a segment via Metadata Import:
Note: Use this method if you are creating a cluster join and need to use a synonym that does not exist. This option enables you to create the synonym and continue to create the cluster join.
The following image is an example of a screen that appears where you provide information for the connection parameters.
The following image is an example of a screen that enables you to select tables to create synonyms.
The selected synonyms will be created and added to the Modeling View.
To insert a segment manually:
Select Insert, then Segment Manually. This enables you to assign values to segment attribute fields in the Synonym Editor.
Note: Use this method if you are coding a new Master File, as you would for a FOCUS data source.
The segment is added in the Modeling View.
Note: This option is only available when using relational tables.
The Join Editor dialog box opens, as shown in the following image.
The Join Condition field automatically creates a Join if identical fields exist in both segments.
Another way to create a cluster join is to start with an empty synonym:
The New Master File dialog box opens.
The Synonym Editor opens.