Cardinality

Notation

By default, Cognos Cube Designer uses Merise notation. Merise notation marks each end of the relationship with the minimum and maximum cardinality of that end.

When you interpret cardinality, you must consider the notation that displays at both ends of the relationship. Possible end labels are shown in the following list:

• 0..1 (zero or one match)
• 1..1 (exactly one match)
• 0..n (zero or more matches)
• 1..n (one or more matches)

The first part of the notation specifies the type of join for this relationship:

• An inner join (1)

  An inner join shows all matching rows from both objects.

• An outer join (0)

  An outer join shows everything from both objects, including the items that do not match. An outer join can be qualified as full, left, or right. Left and right outer joins take everything from the left or right side of the relationship respectively and only what matches from the other side.

Your users see a different report depending on whether you use an inner or outer join. For example, your users want a report that lists salespeople and orders. If you use an outer join to connect salespeople and orders, the report shows all salespeople, regardless of whether they have any orders. If you use an inner join, the report shows only the salespeople who have placed orders.

Data in one object might have no match in the other object. However, if the relationship has a minimum cardinality of 1, an inner join is always used. Conversely, if all the items match, but the relationship in the model has a minimum cardinality of 0, an outer join is always used, although the results are the same with an inner join. For example, the underlying table for one object contains a mandatory (non-NULLable) foreign key for the other. Ensure that the data and cardinalities match.

The second part of the notation defines the relationship of query items between the objects.

Cardinality in generated queries

IBM Cognos software supports both minimum-maximum cardinality and optional cardinality.

In 0:1, 0 is the minimum cardinality, and 1 is the maximum cardinality.

In 1:n, 1 is the minimum cardinality, and n is the maximum cardinality.

A relationship with cardinality specified as 1:1 to 1:n is commonly referred to as 1 to n when focusing on the maximum cardinalities.

A minimum cardinality of 0 indicates that the relationship is optional. You specify a minimum cardinality of 0 if you want the query to retain the information on the other side of the relationship in the absence of a match. For example, a relationship between customer and actual sales might be specified as 1:1 to 0:n. This indicates that reports will show the requested customer information even though there might not be any sales data present.

Therefore, a 1 to n relationship can also be specified as shown in the following list:

• 0:1 to 0:n
• 0:1 to 1:n
• 1:1 to 0:n
• 1:1 to 1:n

It is important to ensure that the cardinality is correctly captured in the model because it determines the detection of fact query subjects and it is used to avoid double-counting factual data.

When generating queries, IBM Cognos software follows these basic rules to apply cardinality:

• Cardinality is applied in the context of a query.
• 1 to n cardinality implies fact data on the n side and implies dimension data on the 1 side.
• A query subject may behave as a fact query subject or as a dimensional query subject, depending on the relationships that are required to answer a particular query.