Cardinality

Relationships exist between two query subjects or between tables within a query subject. The cardinality of a relationship is the number of related rows for each of the two objects in the relationship. The rows are related by the expression of the relationship; this expression usually refers to the primary and foreign keys of the underlying tables.

IBM® Cognos® software uses the cardinality of a relationship in the following ways:

You must ensure that all relationships and cardinality correctly reflect your users' reporting requirements.

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:

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

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:

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: