Cardinality and how Cognos Analytics uses it

Tables are related using relationships that denote the numerical number of related rows in each table. Common relationships are 1 to many, and 1 to 1.

Relationships between two tables reference one or more columns from both tables. Typically, the relationship reflects the referential integrity defined in a database (primary, unique, and foreign keys). When metadata is imported, IBM® Cognos Analytics attempts to locate any available referential integrity to create default relationships.

The Cognos Analytics query service uses the cardinality of a relationship in the following ways:

To identify tables that behave as facts (n side of the relationship) or dimensions (1 side of the relationship).
To avoid double-counting of measures.
To support loop joins that are common in star schema models.

A relationship can specify the minimum-maximum cardinality and optional cardinality.

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

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

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

A minimum cardinality of 0 indicates that the relationship is optional. As an example, a relationship between Customer and Sales might be defined as 1:1 to 1:n by default. In this case, customers without sales aren’t returned because cardinality on both sides isn’t optional. To include customers without sales, use the 1:1 to 0:n cardinality. This cardinality indicates that queries will show the requested customer information even though there might not be any sales data present.

Relationships can be defined to describe the following scenarios:

1:1 to 1:n (inner join)
0:1 to 1:n (right outer join)
0:1 to 0:n (full outer join)
1:1 to 0:n (left outer join)

Ensure that the cardinality is correctly defined in your metadata to avoid ambiguity. Tables with only 1 cardinality are always considered dimensions in the context of a query, while tables with only n cardinalities are always considered facts. Tables with a mix of 1 and n cardinalities are defined either as dimensions or facts, depending on the context of the query. For more information about context as it relates to queries, see Cardinality in the context of a query.

When generating queries, the Cognos Analytics query service follows these basic rules to apply cardinality:

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

In Framework Manager, the default annotation in the relationship diagram uses 1..1 or 0..1 and 1..n or 0..n to represent the minimum and maximum cardinalities. In data modules, 1 and n are displayed to show the maximum cardinalities in the relationship diagram, and optional cardinality is indicated by a white background versus a blue background in the cardinality annotation, as shown in the following screen capture.

Note: If you try to create a join between two tables where the data types of the keys don’t match, you might be tempted to cast one of the keys to match the data type of the other key. This isn’t recommended as performance can be negatively impacted. The data type mismatch should be resolved in the database so that the database optimization for primary and foreign keys can be leveraged. When you use a calculation in the modeling tools to cast a data type, a new data column is created at run time that doesn’t exist in the database and won’t be optimized.