82 IBM Cognos Dynamic Cubes
11.Set MANAGER_CODE3 to be the business key and MANAGER3 to be the member
caption.
12.Select the fourth level and rename it to: Manager level 4
13.Drag MANAGER_CODE4 and MANAGER4 into the level.
14.Set MANAGER_CODE4 to be the business key and MANAGER4 to be the member
caption.
15.Select the fifth level and rename it to: Manager level 5
16.Drag MANAGER_CODE5 and MANAGER5 into the level.
17.Set MANAGER_CODE5 to be the business key and MANAGER5 to be the member
caption.
18.Select the sixth level and rename it to: Manager level 6
19.Drag MANAGER_CODE6 and MANAGER6 into the level.
20.Set MANAGER_CODE6 to be the business key and MANAGER6 to be the member
caption.
21.Select the lowest level and rename it to: Employee
22.Drag EMPLOYEE_KEY, EMPLOYEE_CODE, and EMPLOYEE_NAME into the level.
23.Set EMPLOYEE_KEY as the level unique key.
24.Set EMPLOYEE_NAME as the member caption.
25.Open each level in turn and add the level keys of each level above it.
For example, Manager level 2 will have MANAGER_CODE1 added to it from Manager level 1.
Manager level 3 will have MANAGER_CODE1 added to it from Manager level 1, and
MANAGER_CODE2 added from Manager level 2.
4.7 Role-playing dimensions
A role-playing dimension is a dimension that has more than one possible join between it and
a fact table. Each join represents an aspect of the relationship between the dimension and the
fact table and how the aspects, or roles, give context to the fact. The goal for modeling
role-playing dimensions is to make the relational queries be generated in a predictable
fashion.
To do that task, you must separate each of the relationships so that only one relationship
exists between an entity and the fact table. You will need to make a duplicate of the dimension
for each additional relationship that you want to have. Each of these entities is referred to as a
role-playing dimension. They exist as aliases to the dimension table. Each role-playing
dimension uses one of the keys to the fact table.
For example, a Sales fact table can have a relationship to the time dimension based on both
the sales date and the product shipping date. You need a way to plan the query so that, if you
want to know the quantity that was shipped in a particular month, the intended keys are used
in the query.
With a role-playing dimension, you know that the query will generate in the way that you want
it to. A role-playing dimension enables a query to be generated in a deterministic way. If you
use an object from the Sales date dimension in a report, you can be assured that the query
will be using the sales date key. If you use an object from the Product shipping date time
dimension, you can be assured that the query will be using the shipping date key.
Chapter 4. Modeling dynamic cubes 83
The sample Cognos Cube Designer model has examples of role-playing dimensions. The
following steps are the procedure for discovering them. You see three time dimensions. Each
has a relationship to the fact table in the measure dimension. The key in the time dimensions
is the same, but each relationship uses a different key in the fact table.
1. Select the gosldw_sales cube.
2. Right-click Open Editor. A list of the dimensions that exist in the cube is displayed.
3. Click the Relationship Edit link for the
Time dimension.
The Join to Measure dialog has a table with three columns:
– One displays the name of the dimension, Time. The key DAY_KEY displays under it.
– The middle column displays the relationship operator.
– Column three has the key being used by the measure dimension, ORDER_DAY_KEY.
4. Click the DAY_KEY field and examine the list that is displayed. It contains the columns of
the GO_TIME_DIM table. If your dimension was sourced from tables in a snowflake
structure, you see all the columns from each of the tables in the snowflake.
5. Click the ORDER_DAY_KEY field and examine the list that is displayed. It has the
columns of the SLS_SALES_FACT table.
6. Click Cancel.
7. Click the Edit Relationship link for the Time (close date) dimension.
The Join to Measure dialog has a table with three columns:
– One displays the Time (close date) dimension name. The key DAY_KEY displays
under it.
– The middle column displays the relationship operator.
– Column three has the key being used by the measure dimension, CLOSE_DAY_KEY.
8. Click the DAY_KEY field and examine the list that is displayed. It contains the columns of
the GO_TIME_DIM table.
9. Click the CLOSE_DAY_KEY field and examine the list that is displayed. It has the columns
of the SLS_SALES_FACT table.
10.Click Cancel.
11.Click the Edit Relationship link for the Time (ship date) dimension.
The Join to Measure dialog has a table with three columns.
– One displays the Time (ship date) dimension name. The key DAY_KEY displays
under it.
– The middle column displays the relationship operator.
– Column three has the key being used by the measure dimension, SHIP_DAY_KEY.
12.Click the DAY_KEY field and examine the list that is displayed. It contains the columns of
the GO_TIME_DIM table.
13.Click the SHIP_DAY_KEY field and examine the list that is displayed. It has the columns of
the SLS_SALES_FACT table.
14.Click Cancel.
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.