Joining Data in the Synonym Editor

You can use the Synonym Editor to create a cluster synonym by joining together multiple tables or synonyms from your data source that contain related data. You can create a cluster synonym when connecting to data stored in a remote database, or you can create a new cluster synonym from existing synonyms. In both of these processes, you use the Join Editor in the Synonym Editor to add and edit joins in the cluster. The Join Editor presents a modeling view of the cluster synonym that visually represents the structure of the cluster.

To connect to tables in your data source that you want to join together in a new cluster synonym, on the WebFOCUS Home Page, select the Common tab or Data tab on the Action bar and then click Connect. In the Connect to Data wizard, right-click an existing connection under Server Datasources and click Show DBMS objects. If the connection that you want to use is not listed, you can create a new adapter connection by clicking New Datasource, or create a new connection with an existing adapter by right-clicking the adapter name and clicking Add Connection.

When you click Show DBMS objects, a list of tables in the connected data source displays. Select the check box next to the tables that you want to use. Make sure to select the check box in the Fact column for your fact tables, and the check box in the Dimension column for your dimension tables. To speed up the process, use the check box in the Fact column to select the fact table that you want to use, then right-click it and click Add Related Dimensions. This adds all tables that are recognized as sharing fields with the selected table. Click the next arrow   on the ribbon to proceed to the Synonym Editor. Synonyms for each table are created and the tables are joined automatically based on matching field names and formats. A maximum of 1023 joins can be used in a single cluster synonym. The join structure of the cluster synonym is represented by segments and joining arrows in the Join Editor. The Join Editor with an example of a cluster synonym is shown in the following image.

If you have previously connected to and created synonyms for other tables that you want to join, navigate to them in the Data panel that appears to the left of the Join Editor, then drag them into the join canvas. Joins are created automatically based on shared fields. Once you are finished, save the new cluster synonym.

To create a new cluster synonym from existing synonyms, select the Data tab on the Action bar and click Metadata. In the WebFOCUS Reporting Server Console, select the application folder containing the synonym that you want to use as the root, or highest level in the cluster, which is typically a fact table. Right-click that synonym, point to Metadata Management, and click Create Cluster Synonym.

When the Synonym Editor loads, the selected synonym appears in the Join Editor. To add more related synonyms to the cluster, navigate to them in the Data panel that appears to the left of the Join Editor, then drag them into the join canvas. Joins are created automatically based on matching field names and formats. A maximum of 1023 joins can be used in a single cluster synonym. If the join is not automatically created when you drop the synonym onto the join canvas, drag the field from the parent synonym onto the key field in the target synonym with which you want to create the join. You may need to use this method to create joins manually if fields in two tables have the same values but different field names. Similarly, if the values in the fields that you want to use for the join do not match exactly, you can create a DEFINE field in one of the synonyms to transform the field values, and then use that field to create the join. Depending on the complexity of the transformation, there may be some performance impact.

As an alternative to dragging synonyms into the Join Editor canvas, above the Join Editor, click Insert, then click Insert Child. In the Insert Child dialog box, navigate to the locations of the other synonyms that you want to join to the cluster, select the check box for each one, and click OK. You can insert children with joins to any synonym in the cluster, including synonyms that are themselves joined as children of another synonym, allowing you to create a multi-level cluster synonym.

To edit a join, right-click the arrow in the Join Editor that connects the joined synonyms. The shortcut menu indicates the current join type and allows you to change it, as well as giving access to the join profiler by clicking Edit Parent Link. In addition to changing the join type, you can change the field or fields used to create the join and preview a list of the matched values. You can also see sample data by clicking Sample Data with parent key. To remove a synonym from the cluster, right-click it and click Delete. Once you have finished editing the cluster synonym save it. Once you save your cluster synonym, it is available as a data source from which you can create charts, reports, and other content.