IBM Cognos Dynamic Cubes

Chapter 4. Modeling dynamic cubes 81

Creating a slowly changing dimension as a parent-child dimension

Complete the following steps:

1. Select the gosldw_sales cube.

2. Right-click create parent-child dimension.

3. Expand EMP_EMPLOYEE_DIM in the Data Source Explorer tree.

4. Drag EMPLOYEE_NAME, EMPLOYEE_KEY, EMPLOYEE_CODE, MANAGER_CODE,

and MANAGER_KEY into the dimension.

5. Set EMPLOYEE_NAME as the member caption.

6. Set MANAGER_KEY as the parent.

7. Set EMPLOYEE_KEY as the child.

8. Set the relationship between the dimension and the measure dimension to use

EMPLOYEE_KEY for the dimension and EMPLOYEE_KEY for the measure dimension.

9. Publish the cube.

10.Expand the member tree for the dimension in Report Studio.

4.6.4 Modeling a slowly changing dimension in a regular dimension

For a regular dimension, the business keys of all the higher levels in the hierarchy must be

included in a level. This approach has some performance implications, but, without it, the

members will not be uniquely identified. In certain cases, it is possible for the cube to start

and generate members, but it is an incorrect set.

The leaf level should have a unique key, which would exist in the database table. More

information for handling slowly changing dimensions in a data warehouse would be contained

in the standard reference sources for data warehouses, and extract, transform, and load

(ETL).

The surrogate key will need to be joined to the fact table. In this way, each record can track

the data for each instance of an entity that has changed.

Creating a slowly changing dimension regular dimension

Complete the following steps:

1. Create a regular dimension.

2. Create a hierarchy that has seven levels in it.

3. Select the first level and rename it to: Manager level 1

4. Drag MANAGER_CODE1 and MANAGER1 into the level.

5. Set MANAGER_CODE1 to be the business key and MANAGER1 to be the member

caption.

6. Select the second level and rename it to: Manager level 2

7. Drag MANAGER_CODE2 and MANAGER2 into the level.

8. Set MANAGER_CODE2 to be the business key and MANAGER2 to be the member

caption.

9. Select the third level and rename it to: Manager level 3

10.Drag MANAGER_CODE3 and MANAGER3 into the level.