Creating a relationship
You need to create relationships when they are not detected by IBM® Cognos® Analytics.
About this task
Relationships 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.
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 , click
. 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.
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.
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.
- 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.
- Click the relationship settings icon . By default, the relationship settings are detected from
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.
- The following types can be specified: 1-to-1, 1-to-many, and many-to-1.
- 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.
- Click OK.
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 for the required relationship. To delete the relationship from the diagram, right-click the line joining the two tables, and click Remove.