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 −

• One to One − When an instance of one query subject is related to another instance. For example: Each customer has one customer id.
• One to Many − This relationship occurs when one instance of query subject relates to multiple instances. For example: Each doctor has many patients.
• Many to Many − This relationship occurs when many instances of a query subject relate 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 −

• Loop Joins in Star schema
• Optimized access to data source
• Avoid double counting fact data

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

• Primary and Foreign keys
• Match query item names represent uniquely indexed columns
• 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

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.

• 0..1 represents zero or one match
• 1..1 represents one to one match
• 0..n represents Zero or no matches
• 1..n represents One or more matches
• 1 − An inner join with all matching rows from both objects.
• 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.

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

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.

Cognos - Relationships in Metadata Model — Cognos – Relationships in Metadata Model

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

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

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

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

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

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.
The Framework Manager asks whether you want to create a relationship shortcut whenever you create a relationship and both these conditions are true.

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

• A relationship exists between the original objects.

Go to Action Menu → Create Relationship.
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

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.

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

• 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.

• 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.

• 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.

How to create a Data Source Query Subject?

From Actions Menu → Create → Query Subject.

Enter the name of a new Query Subject.

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

Follow the steps till the Finish button appears → Finish

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.

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

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 −

• Cognos SQL
• Native SQL
• Pass through 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.

• Click Data Source query subject and Action menu → Edit Definition.
• Click on SQL button, drag objects or type in SQL you want.
• Click OK.

Change Type of 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
Also note that it is not possible to change the type of SQL for query subjects based on the OLAP data sources.

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

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

Go to Options → SQL Settings tab.

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

So, this brings us to the end of blog. This Tecklearn ‘Cognos – Relationships in Metadata Model’ blog helps you with commonly asked questions if you are looking out for a job in Cognos BI. If you wish to learn Cognos Analytics and build a career in Business Intelligence domain, then check out our interactive, IBM Cognos Analytics Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

IBM Cognos Analytics 11 Training

IBM Cognos Analytics 11 Training

About the Course

IBM Cognos 11 training provides in-detailed knowledge on how to build complex and advanced reports and dashboards using IBM Cognos reporting tool, and this will enable you to become a Cognos developer. Major topics of this Cognos reports and dashboard training include Cognos architecture, the components of Cognos, Analysis Studio, OLTP system, Query Studio, advanced report designing, Cognos Framework Manager and Cognos Administration. By the end of this training, you will get an insight into working with IBM Cognos reporting tool by implementing real-time Cognos projects, and you will be able to use this knowledge in real-time environments.

Why Should you take IBM Cognos Analytics 11 Training?

• The average Cognos developer salary is $78417 or an equivalent hourly rate of $38-Erieri.com.
• Cognos has a market share of around 9% globally.
• Cognos Analytics V11 enhances the efficiency and capabilities of business users, report authors, and administrators alike through a simplification of and the graduated nature of its user experience.

What you will Learn in this Course?

Overview of IBM Cognos BI and Cognos BI Architecture

• Overview of IBM Cognos Analytics 11
• IBM Cognos Framework Manager Modelling
• New UI features of Cognos Analytics 11

Cognos Analytics 11: Data Modules

• Overview of Cognos Analytics 11 Data Modules
• Creation of Data Server Connection
• Using Data Modules to modulate data without using the Framework Manager
• Data Discovery through intent-based modelling
• Creating Data modules with inputs from various sources

Working with Dashboards

• Process of Dashboarding in IBM Cognos
• Creating a Dashboard
• Filter Data in Dashboard
• Widget-to-Widget Communication
• Sorting, Grouping and Calculating Data
• Storytelling

Cognos Environment

• Active Report
• New Visualization Types
• Sharing and Embedding Contents

Cognos Reports

• Cognos Report Studio
• Reporting using Filters, Formatting, Charts
• Advance Report Building Techniques
• Cognos Active Reports
• Geo Spatial Analytics

Cognos Administration

• Deep Dive into Cognos Administration
• Analysing and Administering Queries

Dimension Modelling

• New Features in Smart Modelling
• Transformer Cube and Drill through
• Dynamic Cube and Drill Though

526