Date Dimensions have the additional characteristic of relative dates. This functionality allows you to group information for certain time periods in date dimensions.
They are especially useful for time analysis, where the standard date dimension may not be enough.
You can use relative dates to analyze a single time period such as the last 12 months, or to compare this year's elapsed time (year-to-date) to the same period for previous years, or to compare the current month to the same month in all previous years.
An important feature of relative dates is that they can define periods that are not fixed in time and that vary according to the cube's current data. With this feature, you can have aggregations such as "the year-to-date" or "the last 12 months" whose contents change according to the new data reaching the cube.
You can also make aggregations in higher and lower levels of detail for the relative dates. For example, if the periods are quarters, you can have a hierarchy that aggregates them for years or takes them to a lower level of detail, months.
Relative dates are defined as part of the Date Dimension in the definition of a model in O3 Designer. This means you must first define a Date Dimension, and then add a relative date.
Note
Relative dates have three levels of granularity: years, quarters, and months.
You can define more than one relative date for a date dimension.
The following relative dates examples may clarify the concept of relative date usage.
All examples are based on simulation data with a September 1998 reference.
Example I
Name |
Last 12 Months |
|---|---|
Objective |
View the evolution of measures included in the cube during the last 12 months. Because this time period is not confined to a specific year, you cannot use a standard date dimension hierarchy. |
Reference |
Most Recent Date |
Compare |
Comparison level = MonthRemove Intermediate Levels = OnEnable Refinement = Off |
Dec / 98 |
|
Nov / 98 |
|
Oct / 98 |
|
Set / 98 |
|
Aug / 98 |
|
Jul / 98 |
|
Jun / 98 |
|
May / 98 |
|
Apr / 98 |
|
Mar / 98 |
|
Feb / 98 |
|
Jan / 98 |
|
Dec / 97 |
|
Sep / 97 |
|
Example II
Name |
Year-to-date |
|---|---|
Objective |
View the aggregation of the measures for this year-to-date period compared to the same period for the previous years. Thus, information for the last three years is included. To select this year-to-date period, there is a Month refinement level using From Beginning. This is used to select all the months from the beginning of the year up to the current month, inclusive. |
Reference |
Most Recent Date |
Compare |
Comparison Level = YearRemove Intermediate Levels = OnEnable Refinement = On |
Refinement |
Refinement Level = MonthFrom Beginning = On |
1998 |
|
1997 |
|
1996 |
|
1995 |
|
1994 |
|
1993 |
|
Dec / 98 |
|
Nov / 98 |
|
Oct / 98 |
|
Sep / 98 |
|
Aug / 98 |
|
Jul / 98 |
|
Jun / 98 |
|
May / 98 |
|
Apr / 98 |
|
Mar / 98 |
|
Feb / 98 |
|
Jan / 98 |
|
Example III
Name |
Previous Month |
|---|---|
Objective |
View the aggregation of measures for the month previous to the current one. This month can vary according to the cube's new data. |
Reference |
Most Recent Date |
Compare |
Comparison Level = MonthRemove Intermediate Levels = OnEnable Refinement = Off |
Oct / 98 |
|
Sep / 98 |
|
Aug / 98 |
|
Jul / 98 |
|
Jun / 98 |
|
May / 98 |
|
Apr / 98 |
|
Example IV
Name |
Current Month |
|---|---|
Objective |
View the aggregation of the measures in the current month compared to the same months in the three previous years. |
Reference |
Most Recent Date |
Compare |
Comparison Level = MonthRemove Intermediate Levels = OnEnable Refinement = Off |
Dec / 98 |
|
Nov / 98 |
|
Oct / 98 |
|
Sep / 98 |
|
Aug / 98 |
|
Jul / 98 |
|
. . . |
|
Oct / 97 |
|
Sep / 97 |
|
Aug / 97 |
|
. . . |
|
Oct / 96 |
|
Sep / 96 |
|
Aug / 96 |
|
. . . |
|
Oct / 95 |
|
Sep / 95 |
|
Aug / 95 |
|
Example V
Name |
Rotating 12-month Years |
|---|---|
Objective |
Compare the last 12 months to similar periods in the past. One-year periods are compared, but they don't necessarily coincide with the calendar year because they are determined by the current date. |
Reference |
Most Recent Date |
Compare |
Comparison Level = YearRemove Intermediate Levels = OnEnable Refinement = On |
Refinement |
Refinement Level = MonthLast N = On |
1998 |
|
1997 |
|
1996 |
|
1995 |
|
1994 |
|
1993 |
|
Dec / 98 |
|
Nov / 98 |
|
Oct / 98 |
|
Sep / 98 |
|
Aug / 98 |
|
Jul / 98 |
|
Jun / 98 |
|
May / 98 |
|
Apr / 98 |
|
Mar / 98 |
|
Feb / 98 |
|
Jan / 98 |
|
Dec / 97 |
|
Nov / 97 |
|
Oct / 97 |
|
Sep / 97 |
|
Aug / 97 |
|