New custom relative time members in IBM Cognos Cube Designer 10.2.1 Fix Pack 3

Content

When you create a hierarchy for a time-based dimension, you can now create the following types of custom relative time members:

• custom single period
• custom period-to-date
• custom N-period running total

Custom single period

Use this to define a relative time member that corresponds to a single member at the same level as a current period member, but offset by a defined period.

The relative position is specified by a target period with offset, and a context period with offset.

For example, to define a relative time member "same month, last quarter" you specify:

• target period: month
• target period offset: 0
• context period: quarter
• context offset: -1
  

Here is an example of how the properties for a custom single period would look:



The custom single period is displayed in the Project Explorer Members browser folder, in the member tree. Because the Target Period is Month, the custom single period member is at the Month level, as a child of the Current Quarter relative time member.

Use a positive offset for a future period. For example, to define a relative time member "next month, next year", you specify:

• target period: month
• target period offset: 1
• context period: year
• context offset: 1
  
  

Similarly, the “Next Month, Next Year” custom single period member would also be a child under the Current Quarter relative time member because its Target Period is Month.

Custom period-to-date

Use this to define a relative time member that is an aggregation from the beginning of a time period to an endpoint within the period.

You must specify whether the period is life-to-date or for a specific to-date period. You then specify target period with offset, and context period with offset.

Life-to-date aggregates data for all time periods to a defined endpoint.

The endpoint is defined by the target and context properties.

The target period that you specify affects the granularity of the period-to-date calculation. The calculation ends at the 'close' of the target period, where the close is the last sibling among the descendants. For example, if the current day is January 10, and day is the leaf level, quarter-to-date aggregates January 1 to January 10, if target period is day. If target period is month, quarter-to-date includes all days in the month, January 1 to January 31.

For example, assume a hierarchy with All-Year-Quarter-Month levels. To define a relative time member "quarter to date, last year" you specify:

• life-to-date: false
• to-date period: quarter
• target period: month
• target period offset: 0
• context period: year
• context offset: -1
  

Because the To-date Period level for this period-to-date custom member is Quarter, the custom period-to- date is a child member under the Year to Date relative time member.

In this example, if the current month is February, and the quarter ends in March, the defined endpoint is February because the target period is month.

To define a relative time member "life to date (target = quarter)", you specify:

• life-to-date: true
• to-date period: n/a
• target period: quarter
• target period offset: 0
• context period: year
• context offset: 0

In this example, if the current month is February, and the quarter ends in March, the defined endpoint is March because the target period is quarter.

Note: For life-to-date, the sub tree of reference members is not generated in the studios and Cube Designer member browser.

Custom N-period running total

Use this to define a relative time member that is an aggregation of a defined number of consecutive periods.

You must specify the number of periods, target period with offset, and context period with offset. The endpoint is defined by the target and context properties.

Note: You cannot select the highest level (for example Year if levels are All-Year-Quarter-Month) as the target period.

For example, to define a relative time member for "trailing 6 months, next year" you specify:

• number of periods: 6
• target period: months
• target period offset: -1
• context period: year
• context offset: 1

Here is an example of how the properties for a custom N-period running total would look:



Custom N-period Running Total members are at the same level as other relative time members in the members tree:

Using the target and context properties

The target and context properties operate in similar fashion to the parallel period function, allowing selection of any member relative to a current period member. Start with the current period member at the target level. Find the ancestor at the context level, then find the sibling of ancestor using the context offset. Locate the member parallel to current period among the descendants of sibling at target level. Then apply the target offset.

Property validation rules

IBM Cognos Cube Designer validates custom member property values as follows:

• Offsets must be an integer value (-n, 0, +n).
• The context period must be higher than the target period. If the target period is set to the highest level, the context period must be blank.
• For period-to-date, if life-to-date is false, the target period must be lower than the to-date period. If life-to-date is true, target period can be the highest level.
• For n-period running total, the target period cannot be the highest level, and number of periods must be an integer greater than or equal to 1.

Parent of a custom relative member in a hierarchy

The parent of a custom relative time member in a hierarchy is assigned automatically by the server:

• custom single period - the parent is the predefined current period member at the level above the target period. For example, Current Quarter is the parent of 'Same Month Last Year'.
• custom period-to-date - the parent is the predefined period-to-date member at the level above the to-date period. For example, Year to Date is the parent of 'Quarter to Date Last Year'.
• life-to-date - the parent is the All member for a single root hierarchy, or the member is located at the root level for a multi-root hierarchy.
• n-period running total - the parent is the All member for a single root hierarchy, or the member is located at the root level for a multi-root hierarchy.

Custom relative members in virtual cubes

All relative time definitions (members and auto-generation options) are inherited from the single source cube that supplies the current period.

Limitations

• When browsing relative time members in Cognos Cube Designer, the endpoint member (normally shown in parenthesis) is not displayed for custom members.
  This only applies to source cube, not virtual cubes.
• When browsing relative time members in Cube Designer, the sub tree of reference members is not displayed for custom members.
  This only applies to source cube, not virtual cubes.
• For the life-to-date custom member, there is no sub tree of relative time members available in IBM Cognos Cube Designer or the IBM Cognos studios.
• Values returned by custom and predefined relative time members for a retail calendar or Gregorian calendar with a week level differ from values returned by IBM Cognos Transformer/PowerPlay.

Invalid Members

If the target and context properties are such that the corresponding member is outside the bounds of the hierarchy, the custom member will be dropped at cube start time, and an event will be logged in the xqelog log file.

Relative time calculated members

The Custom single period and Custom period-to-date relative time members behave the same as the Current Period, Prior Period, Current Period, to Date and Prior Period to Date members.

The Custom N-period running total relative time member behaves the same as the Period to Date Change and Period to Date Growth relative time calculated members.

Steps to define the new custom relative time members:

1. From the Project Explorer tree, for a time dimension, right-click the hierarchy that you want to work with, and click Open Editor.

2. Click the Relative Time tab.

3. In the Relative time section, click one of the following icons to create a custom relative time member:

- New Custom Single Period Definition

- New Custom Period To Date Definition

- New Custom N-Period Running Total Definition

4. On the Properties tab, complete the definition of each custom relative time member by specifying the required properties.