Cardinality

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:

• to avoid double-counting fact data
• to support loop joins that are common in star schema models
• to optimize access to the underlying data source system
• to identify query subjects that behave as facts or dimensions

The cardinality of a relationship defines the number of rows of one table that is related to the rows of another table based on a particular set (or join) of keys. Cardinality is used by IBM Cognos software to infer which query subjects behave as facts or dimensions. The result is that IBM Cognos software can automatically resolve a common form of loop join that is caused by star schema data when you have multiple fact tables joined to a common set of dimension tables.

To ensure predictable queries, it is important to understand how cardinality is used and to correctly apply it in your model. It is recommended that you examine the underlying data source schema and address areas where cardinality incorrectly identifies facts or dimensions that could cause unpredictable query results. The Model Advisor feature in Framework Manager can be used to help you understand how the cardinality is interpreted.

A query that uses multiple facts from different underlying tables is split into separate queries for each underlying fact table. Each single fact query refers to its respective fact table as well as to the dimensional tables related to that fact table. Another query is used to merge these individual queries into one result set. This latter operation is generally referred to as a stitched query. You know that you have a stitched query when you see coalesce and a full outer join.

A stitched query also allows IBM Cognos software to properly relate data at different levels of granularity. See What are multi-fact, multi-grain queries.