Creating a relationship manually

You need to create relationships when they are not detected by IBM® Cognos® Analytics.

About this task

Relationships Icon representing diagram view can be created between tables from the same source and from different sources.

The diagram is the most convenient place to view all data module relationships, and quickly discover the disconnected tables.

Important: The list of possible keys in the relationship editor excludes measures. This means that if a row in a column was misidentified as a measure, but you want to use it as an identifier, you will not see the row in the key drop-down list. You need to examine the data module to confirm that the usage property is correct on each column in the table.

Procedure

  1. In the data module tree or in the diagram, click the table for which you want to create a relationship, and from the table context menu Vertical actions menu icon, click New > Relationship.
    Tip: You can also start creating a relationship using the following methods:
    • In the data module tree or in the diagram, control-click the two tables that you want to join in a relationship, and click Relationship.
    • On the Relationships tab in the table properties, click Add a relationship.

    If the data module does not include the table that you need, you can drag the table from Selected sources directly onto the diagram.

  2. In the Create relationship dialog box, select the second table to include in the relationship.

    Depending on the method that you used to start the relationship, the second table might already be added, and you only need to match the columns.

  3. Find the matching columns in both tables, and select Match selected columns. For example, you can match on Product id columns.

    The matching columns are highlighted in the data grid. You might need to click Refresh to retrieve the data.

  4. Click Matched columns to specify the join operator for the match.

    In the Defined matches dialog box, select a join operator. By default, columns are matched based on similar values, by using the equal (=) operator. However, you can also match columns based on a range of values by choosing a different join operator. For more information, see Join operators.

  5. Click the relationship settings icon Relationship settings. By default, the relationship settings are detected from the source.
    Review, and if needed, modify the following settings:
    Realtionship Type
    The following types can be specified: inner join, left outer join, right outer join, and full outer join.
    Cardinality
    The following types can be specified: 1-to-1, 1-to-many, and many-to-1.
    Optimization
    Use the optimization filters to reduce the number of rows of data that are retrieved when the join is executed. For more information, see Join optimization.
  6. Click OK.

Results

The new relationship appears on the Relationships tab in the properties page of the tables that you joined, and in the diagram view.

What to do next

To view or edit all relationships defined for a table, go to the Relationships tab in the table properties. Click the relationship link, and make the modifications. To view a relationship from the diagram, click the join line to open a graphical view of the relationship. To edit a relationship from the diagram, right-click the join line, and click Edit relationship.

To delete a relationship for a table, go to the Relationships tab in the table properties, and click the remove icon remove icon for the required relationship. To delete the relationship from the diagram, right-click the line joining the two tables, and click Remove.