Relationships exist between two query subjects. The cardinality of a relationship is the number of related rows for each of the two query subjects. The rows are related by the expression of the relationship; this expression usually refers to the primary and foreign keys of the underlying tables.
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 8 to properly relate data at different levels of granularity .
You must ensure that all relationships and cardinality correctly reflect your users’ reporting requirements.
For more information, see Cardinality in Generated Queries and Cardinality in the Context of a Query.
When importing from a relational data source, cardinality is detected based on a set of rules that you specify. The available options are
The most common situation is to use primary and foreign keys as well as matching query items that represent uniquely indexed columns. The information is used to set some properties of query items as well as to generate relationships.
To view the index and key information that was imported, right-click a query subject and click Edit Definition. For a query subject, you can change the information in the Determinants tab.
Optional relationships, full outer joins, and many-to-many relationships can be imported from your data source. Framework Manager will run them as queries.
Note: All regular dimensions begin as query subjects. If you converted a query subject to a regular dimension, note that determinant information for the query subject is leveraged as a starting point to define the levels of a single hierarchy. We recommend that you review the levels and keys created in the hierarchy of the dimension.
By default, Framework Manager uses Merise notation. Merise notation marks each end of the relationship with the minimum and maximum cardinality of that end. You can also use Crowsfeet notation, which provides a pictorial representation of the relationship. For information about how to change the notation, see Change the Settings for Diagrams.
When you interpret cardinality, you must consider the notation that appears at both ends of the relationship.
Possible end labels are
- 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 and these records are ignored. 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.
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 may be specified as 1:1 to 0:n. This indicates that reports will show the requested customer information even though there may not be any sales data present.