.A relationship describes how to create a relational query for multiple objects in the model. Without relationships, these objects are isolated sets of data.
Relationships work in both directions. You often must examine both directions to fully understand the relationship.
The different types of relationships are
one-to-one
One-to-one relationships occur when one instance of data in a query subject relates to exactly one instance of another. For example, each student has one student number.
one-to-many or zero-to-many
One-to-many or zero-to-many relationships occur when one instance of data in a query subject relates to many instances of another. For example, each teacher has many students.
many-to-many
Many-to-many relationships occur when many instances of data in a query subject relate to many instances of another. For example, many students have many teachers.
When importing metadata, Framework Manager can create relationships between objects in the model based on the primary and foreign keys in the data source. You can create or remove relationships in the model so that the model better represents the logical structure of your business.
After you import metadata, verify that the relationships you require exist in the project and that the cardinality is set correctly. The data source may have been designed without using referential integrity. Often, many primary and unique key constraints are not specified. Without these constraints, Framework Manager cannot generate the necessary relationships between fact tables and dimension tables.
Framework Manager stores relationships in the nearest common parent of the objects that participate in the relationship. The parent can be either a folder or a namespace. If you move one of the participating objects outside the common parent, the relationship moves to the next namespace that is common to both ends of the relationship. If you move a relationship to a different folder or namespace, the participating objects also move to the same folder or namespace.
Tip: Use the Search tab (Tools pane) to find an object of class Relationship whose name matches a specified pattern. For example, if you search for a relationship whose name contains Order Header, Framework Manager finds all relationships that have Order Header as one end. If you renamed a relationship, a search of this type may not find it.
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 table.
Cognos 8 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
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 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
use primary and foreign keys
use matching query item names that represent uniquely indexed columns
use matching query item names
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.
Framework Manager supports both minimum-maximum cardinality and optional cardinality.
In 0:1, 0 is the minimum cardinality, 1 is the maximum cardinality.
In 1:n , 1 is the minimum cardinality, 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 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.
Therefore a 1 to n relationship can also be specified as 0:1 to 0:n, 0:1 to 1:n, or 1:1 to 0:n.
Use the Relationship impact statement in the Relationship Definition dialog box to help you understand cardinality. For example, Sales Staff (1:1) is joined to Orders (0: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, Cognos 8 follows these basic rules to apply cardinality:
Cardinality is applied in the context of a query.
1: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.
When modeling for analysis or reporting, it is important to consider the nature of the business questions versus the nature of the data source.
A common scenario is that a relationship between a dimension and a fact table in a star schema is optional. This means that not every dimensional member is mandatory in the fact table. OLAP engines compensate for this by inserting an appropriate value when creating the OLAP structure for any dimensional intersection points that do not have data.
For example, an Analysis Studio user wants to create this report:
| 2005 | 2006 | |
| Canada | 1,000,000 | |
| Mexico | 500,000 | 750,000 |
| United States | 1,000,000 | 1,250,000 |
When modeling, it is common to override optional relationships between dimensions and facts for improved performance. However, when performing analysis or reporting on sparse data where you require information about dimensional members that have no facts, outer joins must be enabled to ensure that data is returned for valid dimensional intersection points.
To enable outer joins, we recommend that you do the following:
Check with your database administrator to ensure that the data source can support full outer joins.
Import metadata with outer joins enabled.
After you import data or create a relationship in Framework
Manager, you can rename the relationship and redefine cardinality.
You can create custom relationship expressions by selecting an operator from the list or by manually changing the expression in the expression editor.
You can also create a complex expression for the relationship .
You can view the relationships that already exist for an object by selecting the object and clicking Launch Context Explorer from the Tools menu.
Click a relationship and, from the Actions menu, click Edit Definition.
To modify existing elements, on the Relationship Expression tab, select the query items, cardinalities, and operator you want.
The query items must have the same data type.
To create an additional join, on the Relationship Expression tab, click New Link, and define the new relationship.
To test the relationship, on the Relationship SQL tab, identify the number of rows you want returned and click Test.
Click OK.
If your metadata is from an OLAP data source, click Close.
You can create complex expressions for relationships by using functions, parameters, and objects from the model.
Click a relationship and, from the Actions menu, click Edit Definition.
On the Relationship Expression tab, click the ellipses (...) button next to the Expression box.
Define the expression.
If you insert session parameters or prompts and you want to specify the values that they
represent when you test the expression, click the options button 
.
Click OK.
You create a relationship to join logically related objects that your users want to combine in a single report. This is useful for relationships between objects that were not selected during metadata import, were not joined in the data source, or are from multiple sources.
You can directly create a relationship between the query items.
You can also create a complex expression for the relationship .
You can also use Framework Manager to automatically generate
relationships between
objects based on selected criteria.
You can view the relationships that already exist for an object by selecting the object and clicking Launch Context Explorer from the Tools menu.
Ctrl+click one or two dimensions, query subjects, or query items.
From the Actions menu, click Create, Relationship.
If this relationship is a valid target for a relationship shortcut, Framework Manager asks if you want to create a shortcut to that relationship. For more information, see Create a Relationship Shortcut.
Click OK.
The Relationship Definition dialog box appears.
You can use this dialog box to modify the relationship .
A relationship shortcut is a pointer to an existing relationship. You can use relationship shortcuts to reuse the definition of an existing relationship. Any changes to the source relationship are automatically reflected in the shortcut. You can also use relationship shortcuts to resolve ambiguous relationships between query subjects Resolving Ambiguous Relationships.
Framework Manager asks whether you want to create a relationship shortcut whenever you create a relationship and both of the following conditions apply:
At least one end for the new relationship is a shortcut.
A relationship exists between the original objects.
Ctrl+click the objects that you want to participate in the relationship shortcut.
From the Actions menu, click Create, Relationship.
Framework Manager asks if you want to create a shortcut to that relationship.
Click Yes.
A list appears of all relationships in which one end is a model object and the other end is either another model object or a shortcut to another model object.
To retrieve all relationships in which both ends can be either a model object or a shortcut to a model object, click Find All.
Click the relationship that you want to be the target of the relationship shortcut.
Click OK.
You can use Framework Manager to detect and generate relationships between two or more existing objects in your model. This is useful when you import metadata in stages, or when you want to change the criteria that apply to existing relationships, such as whether they include outer joins.
When importing star schema metadata, avoid generating relationships based on matching column or query item names unless you have naming conventions in place. Data warehouses often apply naming standards to columns, such as surr_key as the default column name for surrogate keys in dimensions. In this case, generating relationships that are based on matching column names generates inappropriate relationships between all dimension tables.
Ctrl+click two or more objects.
From the Tools menu, click Detect Relationships.
Select the rules you want to apply to each pair of tables.
Rule | Result |
Use primary and foreign keys | Creates joins that are based on primary key and foreign key relationships. The query item names do not have to match. |
Use matching query item names that represent uniquely indexed columns | Creates joins between query items whose names and data types match, if one or both of the underlying columns are uniquely indexed. |
Use matching query item names | Creates joins between query items whose names and data types match. This generates as many relationships as possible. |
Indicate whether you want Framework Manager to detect and generate relationships between
the selected objects
each selected object and every object in the project that is not selected
the selected objects and every other object in the project
Identify whether you want Framework Manager to create outer joins or inner joins based on outer joins that exist in the data source.
If you want to disable the automatic prevention of double-counting, convert all n cardinalities to 1 by clearing the Fact detection enabled check box.
For more information, see Guidelines for Modeling Metadata.
Click OK.
