112 IBM Cognos Dynamic Cubes
4.14 Creating a time dimension
Use the following steps to create a time dimension:
1. Select the TIME dimension in the project explorer.
2. Set the Dimension type value in the Property panel to: Time
3. Select the Time hierarchy.
4. Set the Show relative time members value in the property viewer to be true.
5. Select the GO_TIME_DIM table in the Data Source Explorer.
6. Right-click View Data.
7. View the data in the CURRENT_YEAR column. CURRENT_YEAR is the business key of
the Year level of the Time dimension. The values in CURRENT_YEAR will generate
members in the Cognos Dynamic Cube.
8. Choose a year to set as the current period.
9. Select the year level in the Time dimension.
10.Set the level type property to: year
For the other levels, set the level type to match their roles.
11.Click the Current period field in the Property panel.
12.Enter the value that you choose to be the current period. You have now set the current
period value for the year level.
13.View the CURRENT_QUARTER column in the Tabular Data tab.
The data is in the form of 20121, 20122, 20123, and 20124.
14.Decide on the current quarter period that you want.
15.Select the Quarter level in the Time dimension.
16.Click the Current period field in the Property panel.
17.Enter the value that you choose to be the current period.
18.Repeat these steps to determine the current month and current day.
4.15 Modeling for multi-grain scenarios
Use the following steps to model multi-grain scenarios.
1. Launch Cognos Cube Designer.
2. Open the model that you are creating as you progress through this book.
3. Select the TIME dimension in the project explorer.
4. Expand the dimension. It has four levels: Year, Quarter, Month, and day.
5. Select the model namespace.
6. Right-click Create new dimension.
7. Rename it to: Time-Month
Chapter 4. Modeling dynamic cubes 113
8. Model it with the metadata that is listed in Table 4-34 through Table 4-36.
Table 4-34 Year
Table 4-35 Quarter
Table 4-36 Month
9. Create a new cube. Rename it to: gosldw_target
10.Put the SLS_SALES_TARGET_FACT.SALES_TARGET into its measure dimension.
11.Drag Time-Month and Employee by Region into the cube.
12.Open the Cube Editor.
13.Open the relationship between Employee by Region and the measure dimension.
14.Confirm that the relationship uses EMPLOYEE_KEY on both sides of the relationship.
15.Open the relationship between Time-Month and the measure dimension.
16.Confirm that nothing appears on either side of the relationship, because MONTH_KEY is
neither a Primary key (PK) nor a Foreign key (FK).
17.Click the drop-down menu for Time-Month and select MONTH_KEY.
18.Click the drop-down menu for Measures and select MONTH_KEY.
19.Click OK.
Attributes Source column
Level key GO_TIME_DIM.CURRENT_YEAR
Member Caption GO_TIME_DIM.CURRENT_YEAR
Other attributes
Member sort
Attributes Source column
Level key GO_TIME_DIM.QUARTER_KEY
Member Caption GO_TIME_DIM.QUARTER_EN
Other attributes GO_TIME_DIM.CURRENT_QUARTER
Member sort QUARTER_KEY, Ascending
Attributes Source column
Level key GO_TIME_DIM.MONTH_KE
Member Caption GO_TIME_DIM.MONTH_EN
Other attributes GO_TIME_DIM.CURRENT_MONTH,
GO_TIME_DIM.MONTH_NUMBER,
GO_TIME_DIM.DAYS_IN_MONTH
Member sort MONTH_KEY, Ascending
114 IBM Cognos Dynamic Cubes
Get IBM Cognos Dynamic Cubes now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
