Previous
Previous
 
Next
Next

DEFINE DIMENSION (DWMQY)

The DEFINE DIMENSION (DWMQY) command defines a DWMQY dimension (that is a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR) whose values represent time periods. After defining a DWMQY dimension, you can use a VNF statement to add a value name format to the dimension's definition. The VNF command controls the format for entering dimension values and the format for showing them in output.


Note:

When you want to aggregate over time do not define the time dimension as a DWMQY dimension since you cannot aggregate over dimensions of this type. Instead, define the time dimension as a hierarchical dimension of type TEXT or NTEXT.

Syntax

DEFINE name DIMENSION dwmqy [TEMP] [AW workspace] [SESSION]

where dwmqy is the time period of the dimension. The valid types for dwmqy are DAY, WEEK, MONTH, QUARTER, and YEAR. Each type indicates the span of the time period represented by the individual dimension values of the dimension. The syntax of dwmqy varies depending on the type:

     DAY 
     [multiple] WEEK [BEGINNING phase ] [ ENDING phase ] 
     [multiple] MONTH [BEGINNING phase ] [ ENDING phase ] 
     QUARTER [BEGINNING phase ] [ ENDING phase ] 
     YEAR [BEGINNING phase ] [ ENDING phase ] 

Parameters

name

The name of the object you are defining. For general information about this argument, see the main entry for the DEFINE command.

DIMENSION

The object type when you are defining a dimension.

multiple

For the WEEK and MONTH types, specifies time periods that span a multiple number of weeks or months. With the WEEK keyword, multiple can be an INTEGER from 2 to 52. With the MONTH keyword, multiple can be 2, 3, 4, or 6.

BEGINNING phase
ENDING phase

Specifies the beginning or ending phase of a WEEK, MONTH, QUARTER, or YEAR dimension:

When you specify phase as a date, you give the month, day, and year, enclosed in single quotes, using any of the input styles that are valid for variable values with a data type of DATE. When you specify a date with an ambiguous meaning (such as '03 05 97'), the date is interpreted according to the current setting of the DATEORDER option.


Note:

When you define a multiple-period dimension of type WEEK but you do not specify a BEGINNING or an ENDING argument, DEFINE automatically supplies a phase that begins with the date '31DEC1899'.

TEMP

Indicates that the dimension's values are only temporary and only for the current session. The dimension has a definition in the current workspace and can contain values during the current session. However, when you update and commit, only the definition of the dimension is saved. When you leave end your session or switch to another workspace, the data values are discarded. Each time you start the workspace, the values of a temporary dimension are NA.

AW workspace

The name of an attached analytic workspace in which you want to define the dimension. Any objects dimensioned by the dimension must be defined in the same workspace. For general information about this argument, see the main entry for the DEFINE command.

SESSION

Specifies that the object exists only in the current session. When you close the current session, the object no longer exists.

Usage Notes

Implicit Relations Between DWMQY Dimensions

When you define two or more dimensions of type DAY, WEEK, MONTH, QUARTER, or YEAR, Oracle OLAP automatically defines implicit relations between the values of the dimensions. For example, when you define a dimension of type MONTH and a dimension of type YEAR, Oracle OLAP automatically defines a relation that associates all the MONTH values that fall within a particular year with the corresponding value of the YEAR dimension.

Using BEGINNING or ENDING Phase to Organize Data by Fiscal Calendar

For dimensions of type MONTH, QUARTER, and YEAR, the BEGINNING phase or ENDING phase argument is especially useful for data organized on a fiscal-year calendar.

By specifying a phase for a dimension of type MONTH or QUARTER, you identify the time period that is the first or last period within a year. For example, when you define a dimension of type MONTH with an ending phase of June, then June is identified as the twelfth month of the year. When a dimension of type QUARTER has an ending phase of June, the quarter ending in June is identified as the fourth quarter of the year. When you give a dimension a VNF that includes a period code, you can enter or report dimension values according to their period within the year.

By default, the single or multiple weeks in a dimension of type WEEK end on Saturday. The BEGINNING phase or ENDING phase argument lets you specify the day of the week on which each period begins or ends. For multiple-week periods, the phase argument also controls the starting or ending date for grouping the weeks into periods. By default, the starting point for grouping multiple weeks is December 31, 1899 (a Sunday).

However, the phase argument does not determine the period that is counted as the first period within a year. For dimensions of type WEEK, Period 1 in a given calendar year is always the first period that ends in that year. For example, suppose you specify a dimension of type WEEK with a four-week period ending on June 7, 1997. DEFINE works backward and forward from this date, forming weeks into four-week periods. For 1997, Period 1 is the period beginning on December 22, 1996 and ending on January 18, 1997.

Examples

Defining a YEAR Dimension

The following statement defines a dimension of type YEAR that holds values for fiscal years that end on June 30.

DEFINE fyear DIMENSION YEAR ENDING june

After defining the dimension, you can give it a description and a VNF (value name format). You can use a MAINTAIN statement to give values to the dimension.

LD Fiscal years ending June 30
VNF 'FY<ff>'
MAINTAIN fyear ADD 'FY97' 'FY00' 

Using the Default Phrase for Date in an ENDING Phrase

This example illustrates how DEFINE automatically supplies a phase that begins with the date '31DEC1899' when you define a multiple-period dimension of type WEEK but you do not specify a BEGINNING phase or an ENDING phase argument. Assume that you issue the following statements

DEFINE twoweek DIMENSION 2 WEEK
DESCRIBE TWOWEEK

When you issue a DESCRIBE statement for twoweek, the following output is produced.

DEFINE twoweek DIMENSION 2 WEEK ENDING '13Jan1900'