Creating Cluster Joins

How to:

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, and so on. 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 64 (using 63 joins), which results in a new Master File that has a maximum of 64 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.


Top of page

x
Procedure: How to Create a Cluster Join by Enhancing Existing Synonyms

Use the Modeling View to enhance an existing synonym by adding a segment.

  1. From the Projects or Data Servers area, open a synonym by double-clicking a Master File from the Master Files folder.

    The Synonym Editor opens.

  2. Click the Modeling View tab.

    The Synonym Editor Modeling View tab opens in the workspace.

  3. Right-click a segment in the workspace and select Insert.

    The following context menu appears, as shown in the following image.

    Context Menu

  4. Insert tables (segments) through one of the methods listed:

    To insert a segment from an existing synonym:

    1. Select Insert, then Reference to Existing Synonym.

      The Insert Reference to Existing Synonym dialog box opens, as shown in the following image.

      Insert Reference to Synonym

    2. Select a synonym to be inserted and click Select.

      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:

    1. 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:

    1. Select Insert, then Segment via Metadata Import. This enables you to add segments by using the Create Synonym tool. This tool creates a synonym and includes it as a segment in the synonym from which the tool was launched.

      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.

    2. When this option is selected, you are first presented with the Adapter dialog box where you can select a configured adapter connection to continue or configure a new adapter, if necessary. The Adapter dialog box is shown in the following image.

      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:

    1. 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.

  5. Right-click a segment and select Join Properties, as shown in the following image.

    Note: This option is only available when using relational tables.

    The Join Editor dialog box opens, as shown in the following image.

  6. Select a radio button for One-to-Many (Join All) or One-to-One (Join Unique).
    • One-to-Many. Indicates a multiple-instance join. At run time, each host record can have many matching records in the cross-referenced file.
    • One-to-One. Indicates a single-instance join. At run time, each host record has, at most, one matching record in the cross-referenced file.
  7. Select a Join Type from the drop down menu. The choices are Inner, Left Outer, or Cross Join.

The Join Condition field automatically creates a Join if identical fields exist in both segments.


Top of page

x
Procedure: How to Create a Cluster Join Using a New Synonym

Another way to create a cluster join is to start with an empty synonym:

  1. Right-click a Master Files folder, select New, then Synonym via Synonym Editor.

    The New Master File dialog box opens.

  2. Enter a unique file name in the File name field.
  3. Click Create.

    The Synonym Editor opens.

  4. Click the Modeling View tab.
  5. Right-click in the workspace and select from one of the available options to start building the new view.


WebFOCUS