Cognos - Relationships in Metadata Model

Cognos – Relationships in Metadata Model

Relationships are used to create queries on a number of objects in a metadata mannequin. Relationships will be bidirectional and with out creating relationship, objects are particular person entities with no use in metadata mannequin.

Every object in metadata mannequin is related utilizing main or international key within the information supply. You possibly can create or take away relationships within the metadata mannequin to satisfy the enterprise necessities.

There are completely different relationships that are doable, a few of them are −

  • One to One − When an occasion of 1 question topic is said to a different occasion. For instance: Every buyer has one buyer id.
  • One to Many − This relationship happens when one occasion of question topic pertains to a number of cases. For instance: Every physician has many sufferers.
  • Many to Many − This relationship happens when many cases of a question topic pertains to a number of cases. For instance: Every affected person has many medical doctors.

Cardinality Idea

It’s outlined because the variety of associated rows for every of the 2 question topics. Cardinality is used within the following methods −

  • Loop Joins in Star schema
  • Optimized entry to information supply
  • Keep away from double counting reality information

Whereas utilizing the Relational database as a knowledge supply, Cardinality will be outlined contemplating the next guidelines −

  • Major and Overseas keys
  • Match question merchandise names characterize uniquely listed columns
  • Matching question merchandise names

The commonest methods to outline Cardinality is through the use of the first and international key. To view the important thing info that was imported, proper click on on the question topic → Edit Definition. You possibly can import many to many relationships, elective relationships, and outer joins from the information supply.

Relationship Notation in Metadata Mannequin

Within the Framework supervisor, a relation is represented by Merise notation. The primary a part of this notation represents the kind of be a part of for this relationship.

  • 0..1 represents zero or one match
  • 1..1 represents one to at least one match
  • 0..n represents Zero or no matches
  • 1..n represents A number of matches
  • 1 − An inside be a part of with all matching rows from each objects.
  • 0 − An Outer be a part of with all objects from each, together with the gadgets that don’t match.

Creating or Modifying the Relationships

To create a Relationship or to mix logically associated objects which aren’t joined in metadata import. You possibly can manually create relationship between objects or can mechanically outline relationship between objects primarily based on chosen standards.

To create a Relationship, use CTRL key to pick a number of question gadgets, topics or dimensions. Then go to Motion Menu → Create Relationship.

If it is a legitimate Relationship, the Framework supervisor needs to create a shortcut to the connection. You possibly can then click on on the OK button.

F_SALES

When you create a relationship after the metadata import, you may as well modify the connection or Cardinality within the Framework supervisor.

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

From the Relationship Expression tab → Choose Question gadgets, Cardinalities and Operators.

Relationship Defination

To create an extra Be a part of, go to the Relationship Expression tab → New Hyperlink and Outline New Relationship.

To check this Relationship, go to Relationship SQL tab → rows to be returned → Take a look at.

Relationship SQL tab

Click on on OK button.

Making a Relationship Shortcut

A Relationship shortcut is outlined because the pointer to an current relationship and to reuse the definition of an current relationship. While you make any change to the supply Relationship, they’re mechanically up to date in shortcuts. Relationship shortcuts are additionally used to resolve ambiguous relationship between question topics.

The Framework Supervisor asks whether or not you need to create a relationship shortcut everytime you create a relationship and each these circumstances are true.

  • At the least one finish for the brand new relationship is a shortcut.
  • A relationship exists between the unique objects.

Go to Motion Menu → Create Relationship.

If it is a legitimate Relationship, Framework supervisor needs to create a shortcut to the connection. Click on YES. An inventory seems of all relationships through which one finish is a mannequin object and the opposite finish is both one other mannequin object or a shortcut to a different mannequin object.

Click on OK.

Create a Question Topic

A question topic is outlined as a set of question gadgets which have an inherent relationship. A question topic can be utilized to customise the information they retrieve utilizing a Framework Supervisor.

The next are the question topic sorts in a Framework Supervisor −

  • Knowledge Supply Question Topic − These are primarily based on the Relational metadata outlined by the SQL statements and are mechanically created for every desk and consider whenever you import metadata into mannequin.

    Word − The info supply question topic references the information from just one information supply at a time, however you possibly can straight edit the SQL that defines the retrieve information to edit the question topic.

  • Mannequin Question Topics − They aren’t straight created from a knowledge supply however are primarily based on the question gadgets outlined in different question topics or dimensions. Utilizing the mannequin question topic, it lets you create extra summary and enterprise view of knowledge supply.
  • Saved Process Question Topics − They’re created when a Process is imported from a Relational information supply. IBM Cognos Framework Supervisor solely helps consumer outlined Saved Procedures and system saved procedures aren’t supported.

New QuerySubject

The way to create a Knowledge Supply Question Topic?

From Actions Menu → Create → Question Topic.

Action Menu

Enter the title of a brand new Question Topic.

Click on on Knowledge Supply → OK to open new Question Topic wizard.

Data Source

Observe the steps until the End button seems → End

Proper click on on Question Topic → Edit Definition. Click on on the SQL tab → Obtainable database objects field, drag objects to the SQL field.

It’s also possible to insert a knowledge supply reference, insert a macro, embed a calculation and embed a filter.

Choose the actions from the checklist and click on OK.

Query Information

Objective Motion
Granularity Management Determinants Tab
To check question topic Take a look at Tab
To view SQL Question Data Tab
To view system tables from information supply Present System Objects test field

Edit SQL

While you edit any Relation database supply, create or question a Relation database, then SQL is used within the background. You need to use the next choices −

  • Cognos SQL
  • Native SQL
  • Go via SQL

To edit SQL of the mannequin question topic, copy SQL from question Data tab and paste to the brand new information supply question topic. It’s doable to transform a mannequin question topic to information supply question topic.

  • Click on Knowledge Supply question topic and Motion menu → Edit Definition.
  • Click on on SQL button, drag objects or sort in SQL you need.
  • Click on OK.

Edit SQL

Change Kind of SQL

You possibly can choose the kind of SQL for use whenever you outline information supply question topic. These components ought to be thought-about whereas contemplating sort of SQL −

SQL Kind Benefit Drawback
Cognos SQL Improved efficiency

Work on all supported database

Non Commonplace SQL not supported
Native SQL Efficiency Optimized

Particular to Database

SQL doesn’t work on completely different database.

You possibly can’t use SQL that information supply doesn’t assist for subqueries.

Go By way of SQL Any SQL supported by database No choice for Framework

Supervisor to optimize

efficiency mechanically

Additionally observe that it’s not doable to alter the kind of SQL for question topics primarily based on the OLAP information sources.

To alter SQL sort, go to Question topic you need to change.

Go to Actions menu → Edit Definition and go to Question Data button.

SQL Settings Tab

Go to Choices → SQL Settings tab.

To alter the kind of SQL, click on on SQL Kind Record. Then, click on OK.