Model relative time dimensions
You use IBM Cognos Cube Designer to create a fixed set of relative time members in a time hierarchy, and create custom relative time calculated members (if required). A report author can then create reports relative to the current period. These reports can be run at any time and remain valid based on the value of the current period at the report execution time.
- Current Period
- Prior Period
- Current Period to Date
- Prior Period to Date
- Current Period to Date Change
- Current Period to Date % Growth
- Next Period
- Next Period to Date
- Next Period to Date Change
- Next Period to Date % Growth
For more information about next period members, see Next period relative time members.
You can also create the custom relative time members. For more information, see Custom relative time members.
You can create further calculated members that are based on predefined or custom members. A report author can then create expressions that are based on these members. Calculated member expressions are resolved when a cube is started or member cache refreshed.
Levels
Levels must appear in order in a hierarchy. The order in which they can be used is reflected in the level type list in Cognos Cube Designer.
The level type is used to construct the name of predefined relative time members. For example, Current Semester (1/2 of a time interval) .
- Holidays and seasons can be assigned in any order to any level, and can be used multiple times in the same hierarchy.
- Semesters (1/2 of a time interval) and trimesters (1/3 of a time interval) cannot be used in the same hierarchy.
- Trimesters (1/3 of a time interval) and quarters cannot be used in the same hierarchy.
- Semesters (1/2 of a time interval) and quarters can be used in the same hierarchy.
Current period
Each level includes a Current Period property. The current period property of a level is used to filter members by their level key value to identify the single leaf member that is the current period member in the hierarchy. This is the basis for defining the current member at each level in the hierarchy. If a current period expression is defined, it is used to filter members at that level by the value of the level key for that level. The current period value should map to the business key value of the member you want to be the current period member. The expression can be static, based on a current date/time value, or based on a value in the relational database that is typically populated by the ETL process.
When defining the current period based on a value in a database that is created during the ETL process, use this approach. Create a single-row table with one or more columns containing the key values that correspond to the levels of the time dimension at which you want to define the current period. The table must also contain a column with a single arbitrary value, such as an integer with the value of 1. The time dimension table must contain a corresponding column with the same single value that can be used in the dimension implementation editor to define a join between the single- row table and the time dimension table. The one or more columns in the single-row table must be added as hidden attributes of one of the levels of the hierarchy, typically the highest level. These attributes can now be referenced in the current period expression to define the current period. During ETL, the values required for the current period are applied to the single-row table so that when a dynamic cube is started, the current period value is obtained from the values in the single-row table.
The levels in a relative time dimension are not required to have a current period. If no current period expressions are defined, the current period that is used is the rightmost, most recent leaf level member of the hierarchy.
The combination of level current period expressions is used to identify a specific leaf member. You can determine which member is used as the current period by examining the levels of the hierarchy in a top-down manner. If there are levels with no current period expression defined, the member that is chosen at each level is the rightmost, most recent child of the member that is selected from the previous, higher level. As soon as a level is encountered where a current period expression is defined, the default selection of members at the higher levels is ignored and the member at that level which determines the path to the leaf level current period begins with the member defined by the expression. It is possible to define the current period of a hierarchy by providing a current period at the leaf level.
Where current period expressions are defined for all levels in a relative time dimension, the member captions shown in the hierarchy reflect these expressions. Where there are no current period expressions defined, the captions use the rightmost, recent member as the current period for that level.
Calculated member behavior
The following relative time members have the same behavioral characteristics:
- Current Period
- Prior Period
- Current Period to Date
- Prior Period to Date
- Next Period
- Next Period to Date
- Custom single period
- Custom period-to-date
The following relative time members have the same behavioral characteristics:
- Period to Date Change
- Period to Date % Growth
- Next Period to Date Change
- Next Period to Date % Growth
- Custom N-period running total
For more information, see Relative time calculated members in reports.
Virtual cubes
All relative time definitions (members and auto-generation options) are inherited from the single source cube that supplies the current period.
Security
A relative time dimension cannot include members with non-default security rules.