| Oracle® OLAP DML Reference 10g Release 1 (10.1) Part Number B10339-02 |
|
|
View PDF |
| Oracle® OLAP DML Reference 10g Release 1 (10.1) Part Number B10339-02 |
|
|
View PDF |
The VNF command assigns a value name format (VNF) to the definition of a dimension with a type of DAY, WEEK, MONTH, QUARTER, or YEAR. A VNF is a template that controls the input and display format for values of DAY, WEEK, MONTH, QUARTER, and YEAR dimensions. 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 function with dimensions of type DAY, WEEK, MONTH, QUARTER, or YEAR.You cannot use this function for time dimensions that are implemented as hierarchical dimensions of type TEXT. |
In order 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 command to make it the current definition.
Syntax
VNF [template]
Arguments
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 24-15, "Default VNFs for DAY, WEEK, MONTH, QUARTER and YEAR 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 24-9, "Basic Codes for Components in VNF Templates", Table 24-10, "Component Combinations Allowed in VNF Templates", and Table 24-11, "Format Styles for Day Available in VNF Templates".
Table 24-9, "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 24-9 Basic Codes for Components in VNF Templates
| Code | Meaning | Sample Values |
|---|---|---|
<D>
|
Day of the month on which the period ends
Month in which the period ends Calendar year in which the period ends |
30
|
<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
Month in which the period begins Calendar year in which the period begins |
1
|
<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 24-10, "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 24-10 Component Combinations Allowed in VNF Templates
| Type of Dimension | Component Combinations | Sample Values |
|---|---|---|
| DAY, WEEK, MONTH, QUARTER, YEAR | <D> <M> <YY>
|
31 3 96
|
| MONTH, QUARTER, YEAR | <M> <YY>
|
3 96
|
| YEAR | <YY>
|
96
|
Notice that in place of the basic codes listed in Table 24-10, "Component Combinations Allowed in VNF Templates", you can substitute any of the format styles listed in Table 24-11, "Format Styles for Day Available in VNF Templates". You can also include the <NAME> component with any of the component combinations listed in Table 24-10.
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.
In place of the basic codes for the day, month, calendar year, fiscal year, and period that were listed in Table 24-10, "Component Combinations Allowed in VNF Templates", you can substitute the format styles listed in Table 24-11, "Format Styles for Day Available in VNF Templates", Table 24-12, "Format Styles for Month Available in VNF Templates", Table 24-13, "Format Styles for Year Available in VNF Templates", and Table 24-14, "Format Styles for Period Available in VNF Templates".
Table 24-11 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 24-12 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 24-13 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 24-14 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 |
When you do not provide a VNF for DAY, WEEK, MONTH, QUARTER, and YEAR dimensions, Oracle OLAP uses a default VNF that is suited to the type of dimension ash shown in Table 24-15, "Default VNFs for DAY, WEEK, MONTH, QUARTER and YEAR Dimensions".
You can append the B code to any of the format styles except.
Table 24-15 Default VNFs for DAY, WEEK, MONTH, QUARTER and YEAR Dimensions
| Type of Dimension | Default VNF | Example |
|---|---|---|
| DAY | <DD><MTXT><YY> | 01JAN95 |
| WEEK | W<P>.<FF> | W1.95 |
| Multiple WEEK | <NAME><P>.<FF> | MYWEEK1.95 |
| MONTH | <MTXT><YY> | JAN95 |
| Multiple MONTH | <NAME><P>.<FF> | MYMONTH1.95 |
| QUARTER | Q<P>.<FF> | Q1.95 |
| YEAR | YR<YY> | YR95 |
Notes
When you want to discard a VNF for a dimension and return to using the default VNF, use the CONSIDER command to make the dimension's definition the current one, and then use a VNF command with no argument.
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>>>).
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.
You can use a fiscal year code (FF or FFB) in a template for any dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR.
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.
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.
For a dimension of type DAY, the fiscal year is identical to the calendar year.
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.
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:
You cannot place a letter (either in a component code or in literal text) immediately after a text component of unspecified length (for example, <MTEXT>, which specifies a full month name of any length).
You cannot place a digit (either in a component code or in literal text) immediately after a numeric component of unspecified length (for example, <M>, which can be one digit or two digits, or <YY>, which can be two digits or four digits).
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.
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.
When you enter dimension values in a VNF format, you have the following flexibility:
Letters (either in a component or in literal text) can be either uppercase or lowercase, rather than matching the exact capitalization indicated by the VNF.
When the template specifies <MTXT> or <MTXTL>, which indicate the first three letters of the month name, you can include as much of the month name as you want, from the first three letters to the full month name. When the template specifies <MTEXT> or <MTEXTL>, which indicate a month name of indeterminate length, you can include as much of the name as you want, from the first letter to the full month name. In all cases, however, you must provide enough letters to uniquely match a name in the MONTHNAMES option. For example, to distinguish April from August, you must type at least the first two letters of these names.
You can include as many or as few spaces as you want between components or between text elements in a dimension value.
When the template contains date components that are not essential for identifying a time period for a particular dimension, you can specify any date that falls within the desired time period. For example, the <DD> component of the template <DD><MTXT><YY> is not essential for identifying a period in a MONTH dimension. Therefore, for June 1995 you can specify any date from 01JUN95 through 30JUN95.
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 need to specify only the date components that are relevant for the type of dimension you are using:
For a DAY or WEEK dimension, you must enter all the components (day, month, and year).
For a MONTH or QUARTER dimension, you only need to enter the month and year components. When you enter an ambiguous value, such as '0106', Oracle OLAP uses the first two characters of the DATEORDER option to resolve the ambiguity. Therefore, the DATEORDER option must be MYD or YMD in this situation.
For a YEAR dimension, you only need to enter the year.
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 the VNF command, 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 24-36 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 24-37 Adding Values to a Dimension of Type MONTHi
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 command.
Example 24-38 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 the MAINTAIN command 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.
|
 Copyright © 2003 Oracle Corporation All Rights Reserved. |
|
