IBM Cognos Dynamic Cubes

80 IBM Cognos Dynamic Cubes

4.6.2 Metadata discovery

Be aware of the nature of your dimension and whether it is or will be a slowly changing

dimension.

In the Cognos sample database GOSLDW, the table EMP_EMPLOYEE_DIM is structured so

that we can explore both the concept of the differences between parent-child hierarchies and

regular hierarchies and the concept of slowly changing dimensions.

Examine the EMP_EMPLOYEE_DIM table in GOSLDW. It is clear that MANAGER_CODE

and EMPLOYEE_CODE, which are the natural keys that identify these entities in the

dimension, are normally sufficient to be respectively the parent and child keys of this

dimension if you are modeling it as a parent-child dimension. Because of changes, however,

new records that document the changes are made and those keys no longer are adequate to

identify members.

Use the following steps:

1. Select the table EMP_EMPLOYEE_DIM in the data source explorer.

2. Right-click View data. The Tabular data tab opens. The first 30 records of the table are

displayed. If you want to see more data, click either the Next page or Bottom button.

3. Right-click the Tabular data tab and select Pin. Pinning causes the tab to remain open

until you close it.

4. Examine several of the records. The EMPLOYEE_NAME column is near the end of the

columns in the table so you must scroll over to it. The employee named

Björn Winkler has

two records.

Examine the EMPLOYEE_KEY, EMPLOYEE_CODE, MANAGER_CODE, and

MANAGER_KEY column values for the two records from Björn.

As you examine the records, you see that Björn changed managers, as Table 4-1 shows.

Table 4-1 Employee values

Other records in this data source document employees who change workplaces (Antoine

Dubois; his natural key is 10680). Enrico Marino, who is located in the member path of

Maximilian Saltzman/Bernard Simon/Nicolas Bichot/Tresa Seefelder, gets a promotion.

Daniel Hart and Isaac Brule become managers and acquire subordinates.

4.6.3 Slowly changing dimension as a parent-child dimension

To model a slowly changing dimension in a parent-child dimension, both the parent and child

need surrogate keys to uniquely identify the member. A member can have multiple parents so

a surrogate key must exist: not only for the key that defines the member, but for its position in

the hierarchy. Without that information, the hierarchy attempts to form as a network hierarchy

and thus fail.

EMPLOYEE_CODE EMPLOYEE_KEY MANAGER MANAGER_CODE MANAGER_KEY

10016 4006 Gretchen

Goetschy

10701 4403

10016 4007 Fritz Hirsch 10017 4161