Cognos 简明教程

Cognos - Relationships in Metadata Model

关系用于创建元数据模型中多个对象上的查询。关系可以是双向的，若不创建关系，则对象就是元数据模型中没有用处的独立实体。

Relationships are used to create queries on multiple objects in a metadata model. Relationships can be bidirectional and without creating relationship, objects are individual entities with no use in metadata model.

元数据模型中的每个对象都使用数据源中的主键或外键进行连接。您可以在元数据模型中创建或删除关系以满足业务要求。

Each object in metadata model is connected using primary or foreign key in the data source. You can create or remove relationships in the metadata model to meet the business requirements.

有一些可用的关系，其中一部分包括：

There are different relationships which are possible, some of them are −

  1. One to One − When an instance of one query subject is related to another instance. For example: Each customer has one customer id.

  2. One to Many − This relationship occurs when one instance of query subject relates to multiple instances. For example: Each doctor has many patients.

  3. Many to Many − This relationship occurs when many instances of a query subject relates to multiple instances. For example: Each patient has many doctors.

Cardinality Concept

它被定义为两个查询主题中每个主题的相关行的数目。基数以下列方式使用：

It is defined as the number of related rows for each of the two query subjects. Cardinality is used in the following ways −

  1. Loop Joins in Star schema

  2. Optimized access to data source

  3. Avoid double counting fact data

当使用关系型数据库作为数据源时，可以考虑以下规则来定义基数 −

While using the Relational database as a data source, Cardinality can be defined considering the following rules −

  1. Primary and Foreign keys

  2. Match query item names represent uniquely indexed columns

  3. Matching query item names

定义基数的最常用方法是使用主键和外键。若要查看已导入的关键信息，请右键单击查询主题 → 编辑定义。您可以从数据源中导入多对多关系、可选关系和外连接。

The most common ways to define Cardinality is by using the primary and foreign key. To view the key information that was imported, right click on the query subject → Edit Definition. You can import many to many relationships, optional relationships, and outer joins from the data source.

Relationship Notation in Metadata Model

在 Framework manager 中，关系由 Merise notation 表示。该符号的第一部分表示此关系的连接类型。

In the Framework manager, a relation is represented by Merise notation. The first part of this notation represents the type of join for this relationship.

  1. 0..1 represents zero or one match

  2. 1..1 represents one to one match

  3. 0..n represents Zero or no matches

  4. 1..n represents One or more matches

  5. 1 − An inner join with all matching rows from both objects.

  6. 0 − An Outer join with all objects from both, including the items that don’t match.

Creating or Modifying the Relationships

在元数据导入中创建一个关系，或者组合无连接的逻辑相关对象。您可以在对象之间手动创建关系，也可以基于选定条件自动定义对象之间的关系。

To create a Relationship or to combine logically related objects which are not joined in metadata import. You can manually create relationship between objects or can automatically define relationship between objects based on selected criteria.

若要创建关系，请使用 CTRL 键选择一个或多个查询项目、主题或维度。然后转到 Action Menu → Create Relationship 。

To create a Relationship, use CTRL key to select one or more query items, subjects or dimensions. Then go to Action Menu → Create Relationship.

如果这是个有效关系，Framework manager 将创建此关系的快捷方式。然后您可以单击确定按钮。

If this is a valid Relationship, the Framework manager wants to create a shortcut to the relationship. You can then click on the OK button.

f sales

在元数据导入后创建关系后，您还可以在 Framework manager 中修改该关系或基数。

Once you create a relationship after the metadata import, you can also modify the relationship or Cardinality in the Framework manager.

若要编辑关系，请单击关系，然后从 Action menu → click Edit Definition 中选择。

To edit a Relationship, click a relationship and from Action menu → click Edit Definition.

从 Relationship Expression tab → Select Query items, Cardinalities and Operators 中选择。

From the Relationship Expression tab → Select Query items, Cardinalities and Operators.

relationship defination

若要创建附加连接，请转到 Relationship Expression tab → New Link and Define New Relationship 。

To create an additional Join, go to the Relationship Expression tab → New Link and Define New Relationship.

若要测试此关系，请转到 Relationship SQL tab → rows to be returned → Test 。

To test this Relationship, go to Relationship SQL tab → rows to be returned → Test.

relationship sql tab

单击确定按钮。

Click on OK button.

Creating a Relationship Shortcut

关系快捷方式定义为指向现有关系的指针，并重用现有关系的定义。如果对源关系进行任何更改，则会自动在快捷方式中更新更改。关系快捷方式还用于解决查询主题之间的模棱两可的关系。

A Relationship shortcut is defined as the pointer to an existing relationship and to reuse the definition of an existing relationship. When you make any change to the source Relationship, they are automatically updated in shortcuts. Relationship shortcuts are also used to resolve ambiguous relationship between query subjects.

只要您创建关系并满足以下这两个条件，Framework Manager 就会询问您是否要创建关系快捷方式。

The Framework Manager asks whether you want to create a relationship shortcut whenever you create a relationship and both these conditions are true.

  1. At least one end for the new relationship is a shortcut.

  2. A relationship exists between the original objects.

转到 Action Menu → Create Relationship 。

Go to Action Menu → Create Relationship.

如果这是有效的关系，则 Framework manager 希望创建到该关系的快捷方式。单击是。将显示一个列表，其中列出了所有关系，这些关系的一端是模型对象，而另一端是另一个模型对象或到另一个模型对象的快捷方式。

If this is a valid Relationship, Framework manager wants to create a shortcut to the 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.

单击确定。

Click OK.

Create a Query Subject

查询主题定义为具有一对固有关系的查询项目集合。可以使用 Framework Manager 使用查询主题自定义他们检索的数据。

A query subject is defined as a set of query items that have an inherent relationship. A query subject can be used to customize the data they retrieve using a Framework Manager.

以下是 Framework Manager 中的查询主题类型 −

The following are the query subject types in a Framework Manager −

  1. Data Source Query Subject − These are based on the Relational metadata defined by the SQL statements and are automatically created for each table and view when you import metadata into model. Note − The data source query subject references the data from only one data source at a time, but you can directly edit the SQL that defines the retrieve data to edit the query subject.

  2. Model Query Subjects − They are not directly created from a data source but are based on the query items defined in other query subjects or dimensions. Using the model query subject, it allows you to create more abstract and business view of data source.

  3. Stored Procedure Query Subjects − They are created when a Procedure is imported from a Relational data source. IBM Cognos Framework Manager only supports user defined Stored Procedures and system stored procedures are not supported.

new querysubject

How to create a Data Source Query Subject?

来自 Actions Menu → Create → Query Subject 。

From Actions Menu → Create → Query Subject.

action menu

输入新查询主题的名称。

Enter the name of a new Query Subject.

单击 Data Source → OK to open new Query Subject wizard 。

Click on Data Source → OK to open new Query Subject wizard.

data source

按照步骤操作直到出现完成按钮 → 完成

Follow the steps till the Finish button appears → Finish

右键单击 Query Subject → Edit Definition 。单击 SQL tab → Available database objects box, drag objects to the SQL box 。

Right click on Query Subject → Edit Definition. Click on the SQL tab → Available database objects box, drag objects to the SQL box.

您还可以插入数据源引用、插入宏、嵌入计算并嵌入筛选器。

You can also insert a data source reference, insert a macro, embed a calculation and embed a filter.

从列表中选择操作，然后单击确定。

Select the actions from the list and click OK.

query information

Purpose

Action

Granularity Control

Determinants Tab

To test query subject

Test Tab

To view SQL

Query Information Tab

To view system tables from data source

Show System Objects check box

Edit SQL

当您编辑任何关系数据源，创建或查询关系数据库时，将在后台使用 SQL。您可以使用以下选项：

When you edit any Relation database source, create or query a Relation database, then SQL is used in the background. You can use the following options −

  1. Cognos SQL

  2. Native SQL

  3. Pass through SQL

要编辑模型查询主题的 SQL，请从查询信息选项卡复制代码，并粘贴到新的数据源查询主题。有可能将模型查询主题转换为数据源查询主题。

To edit SQL of the model query subject, copy SQL from query Information tab and paste to the new data source query subject. It is possible to convert a model query subject to data source query subject.

  1. Click Data Source query subject and Action menu → Edit Definition.

  2. Click on SQL button, drag objects or type in SQL you want.

  3. Click OK.

edit sql

Change Type of SQL

在定义数据源查询主题时，可以选择要使用的 SQL 类型。在考虑 SQL 类型时应考虑以下因素：

You can select the type of SQL to be used when you define data source query subject. These factors should be considered while considering type of SQL −

SQL Type

Advantage

Disadvantage

Cognos SQL

Improved performance Work on all supported database

Non Standard SQL not supported

Native SQL

Performance Optimized Specific to Database

SQL doesn’t work on different database. You can’t use SQL that data source doesn’t support for subqueries.

Pass Through SQL

Any SQL supported by database

No option for Framework Manager to optimize performance automatically

另请注意，不能更改基于 OLAP 数据源的查询主题的 SQL 类型。

Also note that it is not possible to change the type of SQL for query subjects based on the OLAP data sources.

要更改 SQL 类型，请转到要更改的查询主题。

To change SQL type, go to Query subject you want to change.

转到 Actions menu → Edit Definition and go to Query Information button 。

Go to Actions menu → Edit Definition and go to Query Information button.

sql settings tab

转到 Options → SQL Settings tab 。

Go to Options → SQL Settings tab.

要更改 SQL 类型，请单击“SQL 类型列表”。然后，单击“确定”。

To change the type of SQL, click on SQL Type List. Then, click OK.