IBM Cognos Dynamic Cubes

Chapter 4. Modeling dynamic cubes 71

4.3.1 Relationships

You must specify a relationship between the measure dimension and its participating

dimensions. These relationships form the basis of the relational query join statements.

In addition, you must specify a relationship between objects within a dimension if they do not

have one. For example, when you create the Product dimension in 4.13, “Creating a sample

cube model by using GOSLDW” on page 95, you will need to create a join between

VIEW_PRODUCT_NAME and SLS_PRODUCT_DIM. There are two instances:

򐂰 One is creating a relationship within a level, such as with a lookup table. Lookup tables are

usually one to one.

򐂰 The other is between one level and another. If your data base does not have primary

key-foreign key constraints you need to do this instance.

Relationships between levels are implicitly one-to-many. In a snowflake schema, this

cardinality usually indicates that the tables that are related in that way are part of a dimension.

Although it is not necessarily the case, each table in the snowflake can be a level in the

dimension. The flow of relationship cardinality should flow down: from the highest level in a

hierarchy to the lowest level.

In a dimension table, you need to renormalize the attributes that you want in a hierarchy.

The cardinality flow from each dimension to the measure dimension should be one-to-many.

A relationship does not need to be used with a column that has referential integrity. In the

gosldw_target cube, which we create in 4.8, “Multi-grain fact scenarios” on page 84, the

relationship between the target measure dimension and the Time.Month dimension would

use MONTH_KEY. If you look at GO_TIME_DIM in the Data Source Explorer, you will see

that MONTH_KEY is neither a primary key (PK) nor foreign key (FK). For more information,

see 4.15, “Modeling for multi-grain scenarios” on page 112.

The grain of the fact can be higher than the grain of the dimension. For example, the grain of

the Time dimension in gosldw_target is month. The reason is because the target facts are

abstracted to the month level. The Time dimension’s grain is day. To model this, create a copy

of the Time dimension, terminating at the month level, and put it into the target cube. For

more information see 4.8, “Multi-grain fact scenarios” on page 84.

You can create a relationship by using many pairs of attributes. The set of attributes that

define a relationship must be confined to defining only one relationship. If your measure

dimension has more than one possible relationship to a dimension, you must create

role-playing dimensions to have unambiguous queries. If the dimension has more than one

relationship between it and a fact table, you need to verify what key is being used for any

relationship. That key defines part of the relational query and is important.

Because dimensions can have PK-FK constraints with the fact table in the database,

relationships can automatically be created by Cognos Cube Designer.

4.3.2 Modeling levels

The level editor contains the list of attributes that define the level, its characteristics, and the

attributes that are associated with members in the level.

With the level key editor, you can define the level key. The level key editor has a list of

attributes that you use to define the level key. The first attribute in the level key list is deemed

to be the business key. The level key editor has a list of attributes that are used in the other