Skip Headers
Oracle® OLAP DML Reference
11g Release 2 (11.2)

Part Number E17122-05
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

VNF

The VNF command assigns a value name format (VNF) to the definition of a object with DATE -only data type, including dimensions of type DAY, WEEK, MONTH, QUARTER, or YEAR. A VNF is a template that controls the input and display format for DATE -only values. The template can include format specifications for any of the components that identify a time period (day, month, calendar year, fiscal year, and period within a fiscal year).

Important:

You can only use this statement with dimensions that have a data type of DATE (that is, dimensions of type DAY, WEEK, MONTH, QUARTER, or YEAR).You cannot use this statement for time dimensions that have a datetime data type that corresponds to a SQL datetime data type or dimensions that are implemented as hierarchical dimensions of type TEXT.

To assign a VNF to a definition, the definition must be the one most recently defined or considered during the current session. When it is not, you must first use a CONSIDER statement to make it the current definition.

Syntax

VNF [template]

Parameters

template

A text expression that specifies the format for entering and displaying the values of the current dimension. When template is omitted, any existing VNF for the current definition is deleted and the default VNF is used (see Table 2-5, "Default VNFs for DWMQY Dimensions").

Note:

When you enter a dimension value that does not conform to the VNF, Oracle OLAP attempts to interpret the value as a date. See "Entering Dimension Values as Dates"

A template contains a code for each component that you use to describe a time period in the current dimension. The code for each component must be preceded by a left angle bracket and followed by a right angle bracket. Basic information about coding a template is provided in Table 10-12, "Basic Codes for Components in VNF Templates", Table 10-13, "Component Combinations Allowed in VNF Templates", and Table 10-14, "Format Styles for Day Available in VNF Templates".

Table 10-12, "Basic Codes for Components in VNF Templates" lists the basic codes for the components of time periods. It uses a sample dimension called MYQTR, which is a QUARTER dimension that ends in June. The examples are from the quarter July 1, 1995 through September 30, 1995. The period code (P) specifies the numeric position of a time period within a fiscal year. You can use the P code with any dimension, but only when you use it along with the FF or FFB code. The B code specifies the beginning period.

Table 10-12 Basic Codes for Components in VNF Templates

Code Meaning Sample Values

<D>

Day of the month on which the period ends

30

<M>

Month in which the period end

9

<YY>

Calendar year in which the period ends

95

<FF>

Fiscal year that contains the period; the fiscal year is identified by the calendar year in which the fiscal year ends

96

<DB>

Day of the month on which the period begins

1

<MB>

Month in which the period begins

7

<YYB>

Calendar year in which the period begins

95

<FFB>

Fiscal year that contains the period; the fiscal year is identified by the calendar year in which the fiscal year begins

95

<P>

The period's numeric position within the fiscal year

1

<NAME>

Name of the dimension 

MYQTR


Table 10-13, "Component Combinations Allowed in VNF Templates" lists the component combinations you can combine in a VNF for each type of dimensions of type DAY, WEEK, MONTH, QUARTER, or YEAR. Notice that you can use the fiscal year codes (FF or FFB) in a template for any dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR. However, the fiscal year codes have a special meaning for WEEK dimensions and for phased MONTH, QUARTER, and YEAR dimensions. For other dimensions, the fiscal year is identical to the calendar year. See "Fiscal Years for a Dimension of Type WEEK", "Fiscal Years for Dimensions of Type MONTH, QUARTER, or YEAR", and "Fiscal Years for Dimensions of Type DAY".

Table 10-13 Component Combinations Allowed in VNF Templates

Type of Dimension Component Combinations Sample Values

DAY, WEEK, MONTH, QUARTER, YEAR

<D> <M> <YY>

<DB> <MB> <YYB>

<P> <FF>

<P> <FFB>

31 3 96

1 4 95

1 96

1 95

MONTH, QUARTER, YEAR

<M> <YY>

<MB> <YYB>

<M> <FF>

<M> <FFB>

<MB> <FF>

<MB <FFB>

3 96

4 95

3 96

3 95

4 96

4 95

YEAR

<YY>

<FF>

<FFB>

96

96

95


Notice that instead of the basic codes listed in Table 10-13, "Component Combinations Allowed in VNF Templates", you can substitute any of the format styles listed in Table 10-14, "Format Styles for Day Available in VNF Templates". You can also include the <NAME> component with any of the component combinations listed in Table 10-13.

You cannot specify a template that includes too few or too many components. The VNF must allow you to input dimension values without ambiguity. See "Coding VNFs to Prevent Ambiguity".

However, if you include only the component combinations that are allowed for a particular type of dimension, and if the VNF permits unambiguous interpretation of input, you have considerable flexibility in specifying a VNF template:

  • You can specify the components in any order.

  • You can include text before, after, and between the components.

Instead of the basic codes for the day, month, calendar year, fiscal year, and period that were listed in Table 10-13, "Component Combinations Allowed in VNF Templates", you can substitute the format styles listed in Table 10-14, "Format Styles for Day Available in VNF Templates", Table 10-15, "Format Styles for Month Available in VNF Templates", Table 10-16, "Format Styles for Year Available in VNF Templates", and Table 10-17, "Format Styles for Period Available in VNF Templates".

Table 10-14 Format Styles for Day Available in VNF Templates

Format Meaning Jan 3, 1995 Nov 12, 2051

<D>

One digit or two digits

3

12

<DD>

Two digits

03

12

<DS>

Space-padded, two digits

3

12


Table 10-15 Format Styles for Month Available in VNF Templates

Format Meaning Jan 3, 1995 Nov 12, 2051
<M> One digit or two digits 1 11
<MM> Two digits 01 11
<MS> Space-padded, two digits 1 11
<MTXT> First three letters, uppercase JAN NOV
<MTXTL> First three letters, lowercase jan nov
<MTEXT> Full name, uppercase JANUARY NOVEMBER

<MTEXTL>

Full name, lowercase

january

november


Note that for MTXT, MTXTL, MTEXT, and MTEXTL, the actual value displayed depends on the value specified for the MONTHNAMES option:

  • For MTXT and MTEXT, when the name in the MONTHNAMES option is all lowercase, the entire name is converted to uppercase. Otherwise, the first letter is converted to uppercase and the second and subsequent letters remain in their original case.

  • For MTXTL and MTEXTL, when the name in the MONTHNAMES option is all uppercase, the entire name is converted to lowercase. Otherwise the first letter is converted to lowercase and the second and subsequent letters remain in their original case.

Table 10-16 Format Styles for Year Available in VNF Templates

Format Meaning Jan 3, 1995 Nov 12, 2051

<YY>

Two digits or four digits

95

2051

<YYYY>

Four digits

1995

2051

<FF>

Two digits or four digits

95

2051

<FFFF>

Four digits

1995

2051


Table 10-17 Format Styles for Period Available in VNF Templates

Format Meaning Jan 3, 1995 Nov 12, 2051

<P>

One, two, or three digits

3

316

<PP>

Two or three digits

03

316

<PS>

Space-padded, two or three digits

3

316

<PPP>

Three digits

003

316

<PPS>

Space-padded, three digits

3

316


Usage Notes

Discarding a VNF

When you want to discard a VNF for a dimension and return to using the default VNF, use a CONSIDER statement to make the dimension's definition the current one, and then use a VNF statement with no argument.

Specifying Angle Brackets as Text in a VNF Template

To include an angle bracket as additional text in a template, specify two additional angle brackets for each angle bracket to be included as text (for example, to display the entire value in angle brackets, specify <<<D> <M> <YY>>>).

Month Names

The names used in the month component for the MTXT, MTXTL, MTEXT, and MTEXTL formats are drawn from the current setting of the MONTHNAMES option.

Fiscal Year Codes

You can use a fiscal year code (FF or FFB) in a template for any dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR.

Fiscal Years for a Dimension of Type WEEK

For a dimension of type WEEK, a fiscal year starts on the beginning date of the first period (single-week or multiple-week) that ends in a new calendar year. The fiscal year ends on the final date of the final period that is wholly contained in the calendar year.

This definition holds true, regardless of any beginning or ending date you specify for a WEEK dimension when you define it. However, the fiscal year does take into account the beginning or ending day of the week that you specify (either as a day of the week or as a date).

For example, suppose you define a dimension of type WEEK, named myweek, with single-week periods ending on June 2, 1995 (a Friday). The fiscal year that contains June 2, 1995 begins on December 31, 1994 (a Saturday) and ends on December 29, 1995 (a Friday). When the VNF for myweek has the FF code, this fiscal year is identified as 1995. When the VNF has the FFB code, the fiscal year is identified as 1994.

Fiscal Years for Dimensions of Type MONTH, QUARTER, or YEAR

For a dimension of type MONTH, QUERTER, or YEAR with no beginning or ending phase, the fiscal year is identical to the calendar year.

For a MONTH, QUARTER, or YEAR dimension with a beginning or ending phase, each fiscal year for that dimension begins with the beginning month of the phase and ends with the ending month of the phase.

For example, assume you define a dimension of type MONTH, mymonth, with four-month periods ending in March, each fiscal year begins on April 1 and ends on March 31. When you use the FF code in a VNF for MYMONTH, the fiscal year that starts on April 1, 1995 and ends on March 31, 1996 is identified as 1996. When you use the FFB code, this fiscal year is identified as 1995.

Fiscal Years for Dimensions of Type DAY

For a dimension of type DAY, the fiscal year is identical to the calendar year.

Out-of-Range Years in a VNF

When a VNF specifies a YY, YYB, FF, or FFB format, and a year outside the range of 1950 to 2049 is to be displayed, the year is displayed in four digits. You must also supply all four digits when you enter the year as input.

Coding VNFs to Prevent Ambiguity

A VNF template must allow you to input dimension values unambiguously. To prevent ambiguity, you must observe the following restrictions when you code a VNF template:

Coding VNFs for Model Dimensions

When you define a model that contains equations based on a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR, the VNF for the that dimension must specify dimension values with these format characteristics: the value must start with a letter, and it can contain only letters, digits, underscores, and periods.

Entering Dimension Values

Once you have assigned a VNF to a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR, you cannot use the default VNF for entering values for that dimension. You must enter values in the format of your VNF or as dates.

Entering Dimension Values in VNF Format

When you enter dimension values in a VNF format, you have the following flexibility:

Entering Dimension Values as Dates

When you enter a value of a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR as a date, you can use any of the input styles listed in the DATEORDER entry. When you specify a full date, Oracle OLAP uses the DATEORDER option to resolve any ambiguities. However, you must specify only the date components that are relevant for the type of dimension you are using:

Overriding a VNF

For additional flexibility in displaying the values of a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR, you can override the dimension's VNF (and the default VNF when the dimension has no VNF of its own) by using the CONVERT function with a VNF argument.

The VNF argument to CONVERT enables you to include all the template codes that are permitted in the template for a VNF statement, but it does not prevent you from specifying too few components or more components than are necessary for identifying a value. In addition, the VNF argument enables you to use additional codes that are not allowed in the VNF template.

Examples

Example 10-173 Assigning a VNF for a Dimension of Type MONTH

The following statements provide a VNF for the existing dimension of type MONTH named month.

CONSIDER month
VNF <mtextl>, <yyyy>

Example 10-174 Adding Values to a Dimension of Type Month

The following statements add dimension values in the style of the new VNF, using just enough letters to distinguish the month names rather than the full names that the <MTEXTL> code in the VNF specifies.

MAINTAIN month ADD 'JA, 1995' 'MAR, 1995'
Limit month TO LAST 3
REPORT month

These statements produce the following output.

MONTH
--------------
January, 1995
February, 1995
March, 1995

Note that Oracle OLAP automatically adds the time periods between the ones you specify in the MAINTAIN statement.

Example 10-175 Assigning a VNF for WEEK

The following statements define a dimension of type WEEK named week, add a VNF to the week definition, and add values to the week dimension.

DEFINE week DIMENSION WEEK
VNF Week <p>.<ff>
MAINTAIN week ADD '01JAN95' '30JAN95'
REPORT week

These statements produce the following output.

WEEK
--------------
Week 1.95
Week 2.95
Week 3.95
Week 4.95
Week 5.95

When you use a MAINTAIN statement to add values to the week dimension, you can specify the new values as dates rather than as values that conform to the VNF. However, the VNF is used for displaying output in the desired format.