Loop joins
Loop joins are caused when table relationships are ambiguously defined based on cardinality. This can produce unpredictable results. However, this issue doesn’t apply to star schema loop joins when cardinality clearly identifies facts and dimensions.
IBM® Cognos® Analytics with Watson can automatically resolve loop joins that are caused by star schema data when you have multiple fact tables joined to a common set of dimension tables.
In the following example of a loop join multiple fact tables are joined to a common set of dimension tables.
When tables are ambiguously defined based on cardinality, which means that it's not clear if a table is a fact or a dimension, and are part of a loop join, the joins that are used in a query are decided based on a number of factors, including the following ones:
- Location of the relationships
- Number of segments in join paths
- First join path in the alphabetical order (if all other factors are equal)
In the following scenario, the Sales staff table is ambiguously defined based on cardinality. The table has the 1 and n cardinality attached, and it's a part of a loop join. If columns from all three tables are selected in a query, it's unclear which join paths would be selected. Would it be Branch to Sales staff and Order, and the join between Sales staff and Order would be ignored? Would it be Branch to Order and Sales staff to Order, and the join from Branch to Sales staff would be ignored?
To remove this uncertainty, you could have Branch act as a role-playing dimension (in this case the Sales staff branch) to resolve the loop join, as shown in the following example:
You could even combine Sales staff branch and Sales staff into one table that contains the Staff and Branch columns to simplify the presentation.
