Syntax

ALLOCERRLOGFORMAT = text

Parameters

text

Characters that determine the contents and formatting of the error log that you specify with an ERRORLOG statement in an ALLOCMAP command. By placing an INTEGER value before the formatting character, you can specify the number of characters that the object occupies in the error log. You can specify escape sequences as formatting characters. For valid escape sequences, see "Escape Sequences". The following table lists the characters that specify the contents of the error log. The default value of ALLOCERRLOGFORMAT is the following.

'%8p %8y %8z %e (%n)'

Table 5-1 Characters for Specify the Contents of the Error Log for ALLOCATE

Character	Output Specified
b	The basis object being processed.
c	The child node of the dimension being processed.
d	The name of the dimension being processed.
e	A description of the error encountered.
n	The error code of the error encountered.
p	The parent node of the dimension being processed.
r	The name of the relation being allocated down.
s	The source object being processed.
t	The target object being processed.
n	The basis value of the child cell receiving the allocation.
y	The source value of the parent cell being allocated.
z	The basis value of the parent cell being allocated.

Examples

Syntax

ALLOCERRLOGHEADER = text

Parameters

text

Characters that determine the content and formatting of the column headers that are the first line of the error log that you specify with the ALLOCATE command. (See ALLOCERRLOGFORMAT for a list of the characters you can use.)

When you specify NA as the value for this option, then ALLOCATE does not write any header to the error log. The following is the default value of ALLOCERRLOGHEADER.

'Dim      Source   Basis\n%-8d %-8v %-8b Description\n
-------- -------- -------- -----------'

Examples

Data Type

INTEGER

Syntax

AWWAITTIME = seconds

Parameters

seconds

The number of seconds to wait for an analytic workspace to be available. The default value is 20 seconds.

Usage Notes

Workspace Sharing

When your user ID has the appropriate access rights and no user has read/write exclusive access to the workspace, you can get read-only access to an analytic workspace, no matter how many other users are using it. When another user has read/write access and commits the workspace, your view of the workspace does not change; you must detach and reattach the workspace to see the changes.

Examples

Data Type

BOOLEAN

Syntax

BADLINE = {YES|NO}

Parameters

YES

When an error occurs during the execution of a program, model, or input file, Oracle OLAP records in the current outfile the name of the program, model, or file in which the error occurred and the line that caused the error. When an error message is included in the output, the BADLINE information appears immediately after the error message.

NO

(Default) When an error occurs in a program, model, or input file, Oracle OLAP does not record the error in the current outfile.

Examples

Data Type

INTEGER

Syntax

BMARGIN = n

Parameters

n

An INTEGER expression that specifies the number of lines to set aside for the bottom margin in a report. The default is 1.

Usage Notes

Setting BMARGIN for a File

To set BMARGIN for a file, first make the file your current outfile by specifying its name in an OUTFILE statement, then set BMARGIN to the desired value. The new value remains in effect until you reset it or until you use an OUTFILE statement to direct output to a different outfile. When you direct output to a different outfile, BMARGIN returns to its default value of 1 for the file.

When you set BMARGIN for the default outfile, the new value remains in effect until you reset it, regardless of intervening OUTFILE statements that send output to a file. That is, the value of BMARGIN is automatically saved for the default outfile

Examples

Data Type

BOOLEAN

Syntax

CALENDARWEEK = {YES|NO}

Parameters

YES

(Default) Specifies that weeks are aligned with the calendar year. For example, if you have defined a dimension of type WEEK, Oracle OLAP numbers its values so that the first week in the calendar year is week 1, the second week in the calendar year is week 2, and so on. Weeks are aligned with the calendar year regardless of any beginning or ending date specified in the WEEK dimension definition.

NO

Specifies that weeks are not aligned with the calendar year. Instead, weeks are numbered so that they are aligned with the date specified in the dimension definition. For example, if you have defined a dimension of type WEEK with a beginning or ending date, its values are numbered so that the week corresponding to the date in the dimension definition is week 1, the following week is week 2, and so on.

Usage Notes

Fiscal Years

Setting CALENDARWEEK to NO causes weeks to be numbered so that the number 1 is assigned to the week beginning or ending on the date specified in the DEFINE DIMENSION statement. This week is then assigned to a fiscal year, which is the calendar year of the first January 1 on or after the week's starting date. For example, if you define a dimension of type WEEK with a starting date of 02Jan1996 (or, equivalently, an ending date of 08Jan1996), the week starting 02Jan1996 is considered week 1 of fiscal year 1997. If, by contrast, you had given the dimension a starting date between 02Jan1995 and 01Jan1996, then the week starting on that date is week 1 of fiscal year 1996.

Examples

Data Type

INTEGER

Syntax

COLWIDTH = n

Parameters

n

An INTEGER expression that specifies the desired column width in number of characters. You can set COLWIDTH to any value from 1 to 4,000. The default is 10.

Note:

The maximum width of a line in a report is 4,000 characters. Therefore, the combined width of all the columns of a report cannot be greater than 4,000 characters.

Examples

Data Type

BOOLEAN

Syntax

COMMAS = {NO|YES}

Parameters

NO

Numeric output does not contain a character that separates thousands, millions, and so on.

YES

(Default) Numeric output contains a character that separates thousands, millions, and so on.

Examples

Data Type

BOOLEAN

Syntax

COMPILEMESSAGE = {YES|NO}

Parameters

YES

(Default) Indicates that Oracle OLAP should record non-irrecoverable error messages during execution of the COMPILE command.

NO

Indicates that Oracle OLAP should suppress non-irrecoverable error messages during execution of the COMPILE command.

Examples

Data Type

BOOLEAN

Syntax

COMPILEWARN = {YES|NO}

Parameters

YES

Oracle OLAP records a message warning you that a compilable object is being compiled automatically. The message explains why the compilation was necessary.

NO

(Default) Oracle OLAP does not record a message warning you that an object is being compiled automatically.

Examples

Data Type

TEXT

Syntax

DATEFORMAT = template

Parameters

template

A TEXT expression that specifies the template for displaying dates. Each component in the template must be preceded by a left angle bracket and followed by a right angle bracket. You can include additional text before, after, or between the components. The default template is '<DD><MTXT><YY>'.

The following tables present the valid formats for each component. The tables provide two display examples, one for March 1, 1990 and another for November 12, 2051.

The following table presents the valid formats for days.

Table 5-2 DATEFORMAT Templates for Day

Format	Meaning	March 1, 1990	November 12, 2051
<D>	One digit or two digits	1	12
<DD>	Two digits	01	12
<DS>	Space-padded, two digits	1	12
<DT>	Ordinal, uppercase	1ST	12TH
<DTL>	Ordinal, lowercase	1st	12th

The following table presents the valid formats for weeks. The table provides two display examples, one for March 1, 1990 and another for November 12, 2051.

Table 5-3 DATEFORMAT Templates for Week

Format	Meaning	March 1, 1990	November 12, 2051
<W>	Numeric	4	1
<WT>	First letter, uppercase	W	S
<WTXT>	First three letters, uppercase.	WED	SUN
<WTXTL>	First three letters, lowercase	Wed	Sun
<WTEXT>	Full name, uppercase	WEDNESDAY	SUNDAY
<WTEXTL>	Full name, lowercase	Wednesday	Sunday

Note that when you specify a format of <WTXT>, <WTXTL>, <WTEXT>, or <WTEXTL>, the case in which the value is specified in DAYNAMES affects the displayed value:

• When the name in DAYNAMES is entered as 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.

• When the name in DAYNAMES is entered as all uppercase, the second and subsequent letters are converted to lowercase. Otherwise, the entire name remains in the case specified in DAYNAMES.

The following table presents the valid formats for months. The table provides two display examples, one for March 1, 1990 and another for November 12, 2051.

Table 5-4 DATEFORMAT Templates for Month

Format	Meaning	March 1, 1990	November 12, 2051
<M>	One digit or two digits	1	11
<MM>	Two digits	03	11
<MS>	Space-padded, two digits	3	11
<MT>	First letter, uppercase	M	N
<MTXT>	First three letters, uppercase	MAR	NOV
<MTXTL>	First three letters, lowercase	Mar	Nov

Note that when you specify a format of <MTXT> or <MTXTL>, the case in which the value is specified in MONTHNAMES affects the displayed value:

• When the name in MONTHNAMES is entered as 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.

• When the name in MONTHNAMES is entered as all uppercase, the second and subsequent letters are converted to lowercase. Otherwise, the entire name remains in the case specified in MONTHNAMES.

The following table presents the valid formats for years. The table provides two display examples, one for March 1, 1990 and another for November 12, 2051.

Table 5-5 DATEFORMAT Templates for Year

Format	Meaning	March 1, 1990	November 12, 2051
<YY>	Two digits or four digits	90	2051
<YYYY>	Four digits	1990	2051

Usage Notes

Specifying Angle Brackets as Text in a DATEFORMAT Template

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

Month and Day Names

The names used in the month component for the MT, MTXT, MTXTL, MTEXT, and MTEXTL formats are drawn from the current setting of the MONTHNAMES option. The names used in the day-of-the-week component for the WT, WTXT, WTXTL, WTEXT, and WTEXTL formats are drawn from the current setting of the DAYNAMES option.

Specifying Abbreviations for Day and Month

You can set the DAYABBRLEN and MONTHABBRLEN options to use abbreviations of different lengths for day and month names.

Out-of-Range Years for DATEFORMAT

When you specify the YY format, and a year outside the range of 1950 to 2049 is to be displayed, the year is displayed in four digits.

Automatic Conversion of DATE-only Values to Text Values

When you use a value with DATE-only data type where a text data type is expected. Oracle OLAP also uses the date template in the DATEFORMAT option to automatically convert the date to a text value. When you want to override the current DATEFORMAT template, you can convert the date result to text by using the CONVERT function with a date-format argument.

Once a DATE-only value is stored in a text variable, the DATEFORMAT template is no longer used to format the display of the value, and subsequent changes to DATEFORMAT have no impact.

DATE-only Dimension Values

The DATEFORMAT option does not how Oracle OLAP displays DATE-only values of DAY, WEEK, MONTH, QUARTER, and YEAR dimensions. How these values are displayed is controlled by a VNF (value name format) attached to the dimension definition, or by default conventions for DAY, WEEK, MONTH, QUARTER, and YEAR dimensions as described in the Default VNFs for DWMQY Dimensions table in Date-only Dimension Values.

Examples

Data Type

ID

Syntax

DATEORDER = order

Parameters

order

One of the following text expressions: 'MDY', 'DMY', 'YMD', 'YDM', 'MYD', 'DYM'. Each letter represents a component of the date. M stands for the month, D for the day, and Y for the year. The default date order is 'MDY'.

Usage Notes

Ambiguous Dates

When you enter an unambiguous DATE-only value or convert a text value that has only one interpretation as a date, it is handled without consulting the DATEORDER option. For example, in 03-24-97 the 97 can only refer to the year. Considering what is left, the 24 cannot refer to the month, so it must be the day. Only 03 is left, so it must be the month. When, however, the interpretation is ambiguous, as in the value 3-5-97, the current value of DATEORDER is used to interpret the meaning of each component.

DATEORDER and TEXT-to-DATE-only Conversion

When you use a text value where a DATE-only value is expected, or when you store a text value in a DATE-only variable, the text value must conform to a style listed "Date-only Input Values". Oracle OLAP automatically converts the text value to a DATE-only value. When the meaning of the text value is ambiguous, the current setting of DATEORDER is used to interpret the value.

To override the current DATEORDER setting in converting a text value to a DATE-only value, use the CONVERT function with the date-order argument.

Essential Date Components

Suppose you want to assign a date value to a DAY, WEEK, MONTH, QUARTER, or YEAR dimension using a MAINTAIN statement or to a valueset using the LIMIT command. When you specify the value in the form of a DATE-only expression or a text literal, Oracle OLAP uses the DATEORDER option to interpret the value. When supplying a text literal, you can use any valid input style for dates. However, you must supply only the date components that are necessary for identifying a time period in the particular type of dimension or valueset you are using. For example, for a MONTH dimension or its valueset, you can specify a complete date, such as 30jun97, or you can provide only the essential components, such as jun97 or 0697.

DWMQY Dimension Phases

The DATEORDER option is used to interpret a phase argument to a DEFINE DIMENSION statement for DAY, WEEK, MONTH, QUARTER, and YEAR dimensions.

Examples

Data Type

TEXT

Syntax

DAYABBRLEN = specification [;|, specification]...

where specification is a text expression that has the following form:

     startpos [- endpos] : length

You can define many different groups of days, each with different abbreviation lengths. When you do so, separate the groups with a comma or a semicolon as shown in the syntax.

Parameters

startpos [- endpos]

Numbers that represent the first and last days whose abbreviation length is defined by length. These numeric positions apply to the corresponding lines of text in the DAYNAMES option. You can specify these ranges of values in reverse order, endpos [-startpos], when you prefer.

The DAYNAMES option can have more than seven lines, so you can specify startpos and endpos greater than seven in the setting of DAYABBRLEN. When you specify a range where neither startpos nor endpos has a corresponding text value in the DAYNAMES option, then Oracle OLAP has no text values to abbreviate for that range. When you later change your day names list so that startpos is valid, the specified abbreviation is applied.

length

A number that specifies the length in characters (not bytes) of abbreviated day names. When you do not specify an abbreviation length for a given position in the DAYNAMES option, or when you explicitly set a given position to zero, Oracle OLAP uses the default abbreviations of one character for <WT> and three characters for <WTXT> and <WTXTL>. Oracle OLAP never uses abbreviations when you have designated the full name specifications <WTEXT> and <WTEXTL>.

Usage Notes

Ambiguous Day Names

You can use DAYABBRLEN to interpret ambiguous names, for example, whether 'T' stands for Tuesday or Thursday. When the DAYABBRLEN for Tuesday was 1 and for Thursday was 2, then 'T' would always match Tuesday, and it would require at least 'Th' to match Thursday. This interpretation does not depend on the order of Tuesday and Thursday in the week; it would work the same way when the two days were reversed. If, on the other hand, the DAYABBRLEN for each of these was 2, then 'T' would not match either one, and you would have to enter at least 'Tu' or 'Th' to get a match.

Examples

Data Type

TEXT

Syntax

DAYNAMES = name-list

Parameters

name-list

A multiline text expression that lists the names of the seven days of the week. Each name occupies a separate line. Regardless of which day you are treating as the first day of the week, the list must begin with the name for Sunday. The default value is the list of English names for the days of the week, in uppercase. You can include multiple sets of seven names in your list. The eighth name is a synonym for the first name, the ninth name is a synonym for the second name, and so on.

Examples

Data Type

ID

Syntax

DECIMALCHAR

Examples

Data Type

BOOLEAN

Syntax

DECIMALOVERFLOW = YES|NO

Parameters

YES

Allows overflow. A calculation that generates overflow executes without error, and the results of the calculation are NA.

NO

(Default) Disallows overflow. A calculation involving overflow stops executing, and an error message is produced.

Examples

produce the following result.

NA

Data Type

INTEGER

Syntax

DECIMALS = n

Parameters

n

An INTEGER expression that specifies the number of decimal places to include in all output of DECIMAL and SHORTDECIMAL values; n can be any number in the range 0 to 40 or the number 255. (When you set DECIMALS to 255, you are specifying the formats for values of both SHORTDECIMAL and DECIMAL data types. See "Example 5-21".) The default is 2.

Examples

Syntax

DEFAULTAWSEGSIZE = n

Parameters

n

The number of bytes.

Examples

Data Type

BOOLEAN

Syntax

DIVIDEBYZERO = YES|NO

Parameters

YES

Allows division by zero. A statement involving division by zero executes without error; however, the result of the division by zero is NA. When you are dividing by a dimensioned variable or expression, setting DIVIDEBYZERO to YES enables you to get results for most of the expression's values when a few calculations might involve dividing by zero.

NO

(Default) Disallows division by zero. A statement involving division by zero stops executing and produces an error message.

Examples

Data Type

DECIMAL

Syntax

DSECONDS

Examples

Data Type

BOOLEAN

Syntax

ECHOPROMPT = {YES|NO}

Parameters

YES

Input lines and error messages are echoed to the current outfile or the debugging file specified by DBGOUTFILE.

NO

(Default) Input lines and error messages do not appear in the current outfile or in the debugging file.

Examples

Data Type

INTEGER

Syntax

EIFBYTES

Examples

Data Type

TEXT

Syntax

EIFEXTENSIONPATH = path-expression

Parameters

path-expression

A text expression that contains one or more directory object names. When you specify multiple aliases, you must enter each one on a separate line. Specify multiple aliases in the order in which they should be used for storing EIF extension files.

Usage Notes

When Extension Files Are Created

When the size of an EIF file grows beyond the size specified for EIF files by the FILESIZE argument to the EXPORT (EIF) command, or the current disk or location becomes full, an EIF extension file is created.

Before creating a new extension file, the location specified by EIFEXTENSIONPATH is checked for sufficient disk space. The required amount of disk space is the amount specified for FILESIZE in the EXPORT (EIF). When no value has been specified for FILESIZE, then a check is made for at least 80K of disk space (the minimum size allowed by FILESIZE). When there is insufficient disk space, checking continues through the list until a location with enough available disk space is found.

Multiple Paths in EIFEXTENSIONPATH

When EIFEXTENSIONPATH contains multiple directory objects, the first extension file is created in the first alias in the list. The second extension file is created in the second alias on the list, and so on. When the end of the list is reached, the process starts over again at the beginning. When EIFEXTENSIONPATH contains a single directory object, all extension files are created in that location.

Examples

Data Type

TEXT

Syntax

EIFNAMES

Examples

Checking What You Have Imported

Suppose you have exported the units variable and the productset valueset from the demo analytic workspace to a file called myfile.eif. After importing the contents of the file into a new workspace, you can use the EIFNAMES option to see the names of the objects you have just imported.

The following statements

AW CREATE mytest
IMPORT ALL FROM EIF FILE 'myfile.eif'
SHOW EIFNAMES

produce this output.

DISTRICT
PRODUCT
MONTH
UNITS
PRODUCTSET 

Data Type

BOOLEAN

Syntax

EIFSHORTNAMES = YES|NO

Parameters

YES

Sets the extension of EIF overflow (extension) file names to xx, where each x is an automatically assigned lowercase letter between a and z.

NO

(Default) Sets the extension of EIF overflow (extension) file names have the structure filename.ennn, where nnn is a three-digit number beginning with 001, to distinguish them from workspace extension file names. For example, when an EIF file is named export.eif, the extension files are named export.e001, export.e002, and so on,

Examples

Data Type

TEXT

Syntax

EIFTYPES

Examples

Data Type

INTEGER

Syntax

EIFUPDBYTES = n

Parameters

n

An INTEGER expression that specifies the minimum number of bytes to be read between updates, during an import. When EIFUPDBYTES has a value of 0, an update is triggered after each analytic workspace object is imported. When EIFUPDBYTES has a value greater than 0, an update is triggered each time the specified number of bytes is imported. The default is 0 (zero).

Examples

Syntax

EIFVERSION = n

Parameters

n

The internal version or build number of an Express Server or Oracle OLAP process which is the target into which you want the data imported.

By default, EIFVERSION is set to the internal version or build number of the current process.

Examples

Data Type

BOOLEAN

Syntax

ERRNAMES = {NO|YES}

Parameters

NO

ERRORTEXT contains only the text of the error message.

YES

(Default) ERRORTEXT contains the name and the text of the error message.

Examples

Data Type

TEXT

Syntax

ERRORNAME

Usage Notes

ERRORNAME and SIGNAL

You can create your own error conditions in a program with the SIGNAL command. SIGNAL sets ERRORNAME and ERRORTEXT to the values you specify.

You can use the special name PRGERR with the SIGNAL command to communicate to a calling program that an error has occurred. The command SIGNAL PRGERR sets ERRORNAME to a blank value and passes an error condition to the calling program without causing another error message to be displayed. For information on using SIGNAL to pass an Oracle OLAP error up a chain of nested programs, see the TRAP command.

Examples

Data Type

TEXT

Syntax

ERRORTEXT

Examples

Syntax

ESCAPEBASE = 'escape-type' 

Parameters

escape-type

Specify 'd' for decimal escape, 'x' for hexadecimal escape.

The default escape type is decimal, which produces the INTEGER value for a character in the following form.

 '\dnnn'

A hexadecimal escape is the INTEGER value for a character in the following form.

 '\xnn'

Examples

For an example of using ESCAPEBASE with CONVERT to convert a text value to an escape sequence, see Example 7-50.

Data Type

BOOLEAN

Syntax

EXPTRACE = {YES|NO}

Parameters

YES

All programs are traced, including OLAP DML programs provided as OLAP DML statements.

NO

(Default) OLAP DML programs provided as OLAP DML statements are not traced. Only other types of programs are traced.

Usage Notes

How to Identify OLAP DML Programs Provided as OLAP DML Statements

Some OLAP DML statements are implemented as OLAP DML programs. These programs are affected by EXPTRACE. To send to the current outfile a list of these programs, issue the following statement.

SHOW AW(PROGRAM 'express')

Examples

Syntax

INF_STOP_ON_ERROR = {YES|NO}

Parameters

YES

When an error occurs, report the error and stop reading from the file.

NO

When an error occurs, report the error and continue reading from the file.

Examples

Data Type

INTEGER

Syntax

LCOLWIDTH = n

Parameters

n

An INTEGER expression that specifies the desired column width in number of characters. You can set LCOLWIDTH to any value from 1 to 4000. The default is 14.

Note:

The maximum width of a line in a report is 4,000 characters. Therefore, the combined width of all the columns of a report cannot be greater than 4,000 characters

Examples

Data Type

BOOLEAN

Syntax

LIKECASE = {YES|NO}

Parameters

YES

(Default) Specifies that the LIKE operator is case sensitive.

NO

Specifies that the LIKE operator is not case sensitive.

Examples

Data Type

ID

Syntax

LIKEESCAPE = char

Parameters

char

A text expression that specifies the character to use as an escape character in a LIKE text comparison. The default is no escape character.

The LIKE escape character affects the LISTNAMES program, which accepts a LIKE argument that it uses in a LIKE text comparison.

Usage Notes

Using the Escape Character

The LIKE escape character lets you find text expressions that contain the LIKE operator wildcard characters, which are an underscore (_), which matches any single character, and a percent character (%), which matches any string of zero or more characters.

To include an underscore or percent character in a text comparison, first specify an escape character with the LIKEESCAPE option. Then, in your LIKE expression, precede the underscore or percent character with the LIKEESCAPE character you specified.

You might want to avoid using a backslash (\) as the LIKE escape character, because the backslash is the standard OLAP DML escape character. You would therefore need two backslashes to indicate that LIKEESCAPE should treat the second backslash as a literal character.

Examples

Data Type

BOOLEAN

Syntax

LIKENL = {YES|NO}

Parameters

YES

(Default) Specifies that the LIKE operator recognizes newline characters between lines of a text expression.

NO

Specifies that the LIKE operator ignores newline characters between lines of a text expression. Newline characters are ignored in both of the expressions being compared.

Examples

Data Type

BOOLEAN

Syntax

LIMIT.SORTREL = {YES|NO}

Parameters

YES

(Default) Oracle OLAP performs a sort when you limit a dimension to a related dimension.

NO

Oracle OLAP does not perform a sort when limiting to a related dimension.

Usage Notes

The Sorting Explained

Normally, when you limit a dimension to a related dimension, the values of the dimension being limited are arranged in the order of the related dimension. When there are multiple values of the first dimension related to a value of the related dimension, the values are sorted in the order of the default status of the first dimension. It is this sort that LIMIT.SORTREL suppresses.

Output Lists when LIMIT.SORTREL Is NO

When LIMIT.SORTREL is NO, the output for any given dimension may not list values in logical order.

Examples

Syntax

LIMITSTRICT = YES | NO

Parameters

YES

(Default) When a list of values in a LIMIT command, a LIMIT function, or a QDR contains a nonexistent value, Oracle OLAP stops executing the limit and issues an error.

NO

When a list of values in a LIMIT command, a LIMIT function, or a QDR contains a nonexistent value, Oracle OLAP processes the limit while treating the specified value as an NA.

Examples

Data Type

INTEGER

Syntax

LINENUM = n

Parameters

n

An INTEGER expression. Normally you do not want to set LINENUM explicitly, but just want to check its current value.

Usage Notes

Starting a New Page

When PAGING is set to YES, LINENUM increases by 1 after each line of output. When LINENUM equals PAGESIZE minus BMARGIN, a new page automatically begins.

At the beginning of each new page, LINENUM is automatically reset to 1.

LINENUM Compared to PAGESIZE

Because the lines in the bottom margin are included in PAGESIZE, LINENUM can never reach PAGESIZE when BMARGIN is set to a number greater than 0 (zero).

The Effect of PAGING on LINENUM

When PAGING is set to NO (its default), the value of the LINENUM option continues to increment as more output lines are produced. When you set PAGING to YES, LINENUM is set to 1 and it begins counting lines on the current page.

The Effect of OUTFILE on LINENUM

When you use an OUTFILE statement to direct output to a file, LINENUM is set to 1 for the file. When you use OUTFILE with the EOF keyword to redirect output to the default outfile, LINENUM contains the value that it last held for the default outfile.

Sending LINENUM in Output

When you produce output that contains the value of LINENUM, and a new page is created by this output, the value of LINENUM is recorded as 1 when your output consists of a single line. However, when the output is a multiline value, the value of LINENUM may be recorded as a value that is larger than PAGESIZE.

Examples

Data Type

INTEGER

Syntax

LINESLEFT

Usage Notes

Controlling Page Breaks

LINESLEFT is used primarily in report programs to check the number of lines left on a particular page. When the number of lines left is less than that required for a part of the report that you do not want interrupted by a page break, you can then use a PAGE statement to skip to a new page.

The Effect of PAGESIZE on LINESLEFT

When you change the value of PAGESIZE, the value of LINESLEFT is adjusted accordingly. First, LINESLEFT is subtracted from the old value of PAGESIZE, which gives the lines already used. This result is then subtracted from the new value of PAGESIZE which gives the new value of LINESLEFT. When LINESLEFT becomes less than 1, a new page is started at the next output line.

The Effect of PAGING on LINESLEFT

When you set PAGING to NO, LINESLEFT is set to the value of PAGESIZE, and it keeps this value until PAGING is set to YES. When you set PAGING to YES, LINESLEFT begins counting the lines on the current page.

The Effect of OUTFILE on LINESLEFT

When you use an OUTFILE statement to direct output to a file, LINESLEFT is set to 66 for the file, to match the default value of PAGESIZE. When you set PAGESIZE to a new value for the current outfile, LINESLEFT is adjusted accordingly. For example, assume that you direct output to a file and then set PAGESIZE to 40. In this case, Oracle OLAP sets LINESLEFT to 40 for the file which ensures that the first line of output to the file triggers a new page when PAGING is set to YES.

When you use an OUTFILE statement with the EOF keyword to redirect output to the default outfile, LINESLEFT contains the value that it last held for the default outfile.

Sending LINESLEFT in Output

When you produce output that contains the value of LINESLEFT, the lines that contain this value are never included in the value recorded for LINESLEFT.

Examples

Data Type

BOOLEAN

Syntax

LOCK_LANGUAGE_DIMS= TRUE | FALSE

Parameters

TRUE

Specifies that Oracle OLAP returns an error if a LIMIT statement tries to limit the status of a language dimension.

When a program changes the value the LOCK_LANGUAGE_DIMS option from FALSE to TRUE, Oracle OLAP resets the status of the language dimension in any attached analytic workspace according to the value of the SESSION_NLS_LANGUAGE option.

FALSE

Sets the status of the language dimension to ALL, and specifies that programs can modify the status of the language dimension using LIMIT.

When a program changes the value the LOCK_LANGUAGE_DIMS option from TRUE to FALSE, Oracle OLAP resets the status of the language dimension in any attached analytic workspace to ALL.

Examples

Data Type

INTEGER

Syntax

LSIZE = n

Parameters

n

An INTEGER expression that specifies the line size within which the STDHDR program centers the standard header, or the maximum line size for output from a HEADING statement. The default is 80 characters for a line.

The maximum width of any line in a report, including a heading line, is 4,000 characters. Therefore, it generally makes sense to set LSIZE to a value of 4000 or less.

Usage Notes

Centering Report Segments

Because STDHDR centers the running page heading within the width of LSIZE, you can use it with LSIZE to center parts of your report. (Start by setting LSIZE to the width of the longest line in your report.)

Creating Centered Headings

You can use LSIZE in centering your own headings for each page or at the beginning of a section. Start by setting LSIZE to the width of your line. Then use a HEADING statement with a WIDTH of LSIZE and the keyword CENTER before the text of your heading. See Example 5-49.

Setting LSIZE for a File

To set LSIZE for a file, first make the file your current outfile by specifying its name in an OUTFILE statement, then set LSIZE to the desired value. The new value remains in effect until you reset it or until you use an OUTFILE statement to direct output to a different outfile. When you direct output to a different outfile, LSIZE returns to its default value of 80 for the file.

When you set LSIZE for the default outfile, the new value remains in effect until you reset it, regardless of intervening OUTFILE commands that send output to a file. That is, the value of LSIZE is automatically saved for the default outfile.

Examples

Return Value

INTEGER

Syntax

MAXFETCH = integer-expression

Parameters

integer-expression

An INTEGER expression representing the maximum size in bytes of a data block generated by FETCH. The minimum value for MAXFETCH is 1K (approximately 1,000 bytes), and the maximum value is 2GB (approximately 2,000,000,000 bytes). The default value of MAXFETCH is 256K.

Usage Notes

Improving Performance of Queries Using OLAP_TABLE

The setting of MAXFETCH can effect the performance of queries using the OLAP_TABLE function. Large queries with joins of OLAP_TABLE function may run faster with higher settings. However, larger settings use more memory which can cause slower performance when there are multiple users. The setting of MAXFETCH does not affect a SELECT using only one OLAP_TABLE function.

MAXFETCH can cause a FETCH error

When FETCH cannot package a data block within the size limit set by MAXFETCH, it produces an error, and no data is returned to the client. By setting MAXFETCH, you can produce an error, rather than run out of memory, when you attempt to fetch too much data.

Examples

Limiting Data Blocks to 4K

The following statement limits the size of data blocks to 4K.

  MAXFETCH = 4096

Data Type

DECIMAL

Syntax

MODDAMP = {n|0.00}

Parameters

n

A decimal value, greater than or equal to zero and less than one, that specifies the weighting factor. The closer MODDAMP is to 0.00, the more weight is given to the value from the current iteration. The default value is 0.00, which gives full weight to the current iteration.

When MODDAMP is greater than zero, Oracle OLAP calculates the weighted average for the current iteration as follows.

calcvalue * (1 - MODDAMP) + weightavg

where:

• calcvalue is the value calculated from the model equation in the current iteration.

• weightavg is the weighted average calculated in the previous iteration.

See "Stored Weighted Average".

Usage Notes

Specifying the Solution Method

The MODDAMP option is used only with the Gauss-Seidel method for solving simultaneous equations. The MODSIMULTYPE option determines the solution method that is being used. The possible settings for MODSIMULTYPE are GAUSS, for the Gauss-Seidel method, and AITKENS, for the Aitkens delta-squared method.

Effect of MODDAMP on Convergence Speed

MODDAMP is used in calculating the results of all model equations in every simultaneous block, whether they oscillate between iterations or not. For equations that do not oscillate, convergence is slowed down when the value of MODDAMP is greater than zero. Therefore, when your model contains some equations that oscillate and some that do not, you might be able to speed up overall convergence by setting MODDAMP to a small nonzero value, such as 0.20. A small nonzero value slows down the convergence of non-oscillating equations only slightly, while speeding up the convergence of oscillating equations.

Stored Weighted Average

When the model equation does not converge or diverge on the current iteration, the weighted average calculated in the current iteration is stored. In the next iteration, Oracle OLAP uses this stored average as weightavg (that is, the weighted average calculated in the previous iteration) in the formula for the weighted average.

In the first iteration over a block, Oracle OLAP uses the starting value of the target variable (or dimension value) as the weightavg (that is, the weighted average calculated in the previous iteration).

Iteration Results Compared

In tests for convergence and divergence in each iteration, Oracle OLAP compares the results of the current iteration to the results from the previous iteration. When MODDAMP is greater than zero, Oracle OLAP tests a comparison value that is calculated as follows.

(weightavg - weightavg) / (weightavg PLUS MODGAMMA)

where weightavg is the weighted average calculated in the previous iteration

For an explanation of the test for convergence, see the MODTOLERANCE option. For an explanation of the test for divergence, see the MODOVERFLOW option.

Options to Control the Solution of Simultaneous Blocks

Altering the value of MODDAMP is just one step you can take in attempting to speed up or attain convergence of a simultaneous block. MODEL lists additional options that you can use to control the solution of simultaneous blocks and provides information on running and debugging models.

Examples

Data Type

ID

Syntax

MODERROR = {'STOP'|'CONTINUE'}

Parameters

'STOP'

(Default) Oracle OLAP sends an error message to the current outfile and stops executing the model.

'CONTINUE'

Oracle OLAP sends a warning message to the current outfile, stops executing the current block, and resumes execution at the next block in the model.

Usage Notes

Block-Solution Failure

When every equation in a simultaneous block passes a convergence test, the block is considered solved. When any equation diverges or fails to converge within a specified number of iterations, the solution of the block fails and an error occurs. MODERROR controls the action that Oracle OLAP takes when an error occurs.

Attaining Convergence for a Simultaneous Block in a Model

When an error occurs, you might be able to attain convergence for the block by changing the value of one or more options that control the solution of simultaneous blocks. For example, you can increase the number of iterations that is attempted or you can change the criteria used in testing for convergence and divergence.

Using 'STOP'

When MODERROR is set to STOP and execution of the model halts because of an error, you can run the MODEL.XEQRPT program to produce a report about the execution of the model. The report specifies the block where the solution failed and shows the values of the model options that were used in solving simultaneous blocks.

Using 'CONTINUE'

When MODERROR is set to CONTINUE and one block in the model is a simultaneous block that either diverges or fails to converge, Oracle OLAP executes any remaining blocks in the model.

Moreover, Oracle OLAP executes the model for the remaining values in the status of any additional dimensions of the solution variable that are not dimensions of the model. In this case, when you run the MODEL.XEQRPT program when Oracle OLAP finishes executing the model, you see a report on the solution for the final values of the additional dimensions.

When the simultaneous blocks in the model converged when the model was executed for the final values of the additional dimensions, then MODEL.XEQRPT reports that the blocks were solved, even though an earlier execution of the model for another dimension value might have failed. When you want to see the MODEL.XEQRPT for the dimension values where the failure occurred, you can set MODERROR to STOP and rerun the model.

Examples

Data Type

INTEGER

Syntax

MODGAMMA = {n|1}

Parameters

n

An INTEGER value to use in testing for convergence and divergence. As Oracle OLAP calculates each equation in a simultaneous block, it constructs a comparison value that is based on the results of the equation for the current iteration and the previous iteration. When the comparison value passes a tolerance test, the equation is considered to have converged. When the comparison value meets an overflow test, the equation is considered to have diverged.

The comparison value that is tested is as follows.

(thisResult - prevResult) DIVIDED BY (prevResult PLUS MODGAMMA)

where thisResult is the result of this iteration and prevResult is the result of the previous iteration.

Oracle OLAP calculates the absolute value of the enclosed expression. The default value of MODGAMMA is 1.

Usage Notes

Testing Convergence

In the test for convergence, the MODTOLERANCE option determines how closely the results of an equation must match between successive iterations. With the default value of 3 for MODTOLERANCE, the equation is considered to have converged when the comparison value is less than 0.001.

Testing Divergence

In the test for divergence, the MODOVERFLOW option determines how dissimilar the results of an equation must be in successive iterations. With the default value of 3 for MODOVERFLOW, the equation is considered to have diverged when the comparison value is greater than 1000.

Comparison Value

The comparison value that Oracle OLAP evaluates in tests of convergence and divergence is fundamentally a proportional value. It expresses the change between iterations as a proportion of the previous results. In the test for convergence, the change between iterations must be small in proportion to the previous results. In the test for divergence, the change between iterations must be large in proportion to the previous results. By testing a proportional value, rather than testing the absolute amount of change, Oracle OLAP can apply the same test criteria to all equations, regardless of the magnitude of the equation results.

However, the comparison value that Oracle OLAP tests is not strictly proportional. When the results of an equation are very close to zero, the denominator of a strictly proportional comparison value would also be very close to zero, and thus the comparison value itself would generally be large. Therefore, the test for convergence would be difficult to satisfy, while the test for divergence would be easy to meet. To solve this problem, Oracle OLAP adds the value of MODGAMMA to the denominator of the comparison value. When the default value of 1 is used for MODGAMMA, the effect of MODGAMMA is as follows:

• When the equation results are close to zero, the denominator is close to one and the test is essentially a test of the absolute change between iterations.

• When the equation results are very large, the effect of adding MODGAMMA to the denominator is negligible, and the test is close to being a strictly proportional test.

Controlling Test Sensitivity

For equation values close to zero, you can control the sensitivity to the tests for convergence and divergence by changing the value of MODGAMMA. When equation values are very small, you essentially scale the changes in model values between iterations when you change the value of MODGAMMA. For example, when you change MODGAMMA from 1 to 2, the comparison value is essentially cut in half. As a consequence, you reduce the likelihood that divergence occurs.

Ways to Increase Speed of Convergence of Model Equations

The default value of MODGAMMA is appropriate in most situations. When you increase the value of MODGAMMA, the model equations converge more quickly, but the results are less precise. The smaller the equation value, the more pronounced is the effect of increasing MODGAMMA; convergence is attained relatively more quickly for small model values, while more precision is lost.

You can also force the simultaneous blocks of a model to converge more quickly by decreasing the value of MODTOLERANCE and thereby relaxing the test for convergence. However, when you do this, you sacrifice the precision of all the results, not just the results of equations with small values.

Therefore, when a model contains some equations with large values and some equations with very small values, it might be preferable to increase MODGAMMA rather than decreasing MODTOLERANCE. By increasing MODGAMMA, you might be able to force equations with small values to converge more quickly while retaining the precision of equations with large values.

Examples

Data Type

BOOLEAN

Syntax

MODINPUTORDER = {YES|NO}

Parameters

YES

The equations in a simultaneous block of a model are executed in the order in which they appear in the model.

NO

(Default) The equations in a simultaneous block are executed in an order determined by the model compiler.

Examples

Data Type

INTEGER

Syntax

MODMAXITERS = {n|50}

Parameters

n

A positive INTEGER value that indicates the maximum number of iterations Oracle OLAP should perform in attempting to solve a simultaneous block. The default is 50.

Usage Notes

Reporting Model Execution Results

When any equation in a simultaneous block diverges or fails to converge within the number of iterations specified by MODMAXITERS, the solution of the block fails and an error occurs. You can use the MODEL.XEQRPT program to produce a report on the results of the model's execution. The report indicates whether a simultaneous block diverged or failed to converge. When a block failed to converge, you can experiment with increasing the value of MODMAXITERS to see if convergence can be attained.

Examples

Data Type

INTEGER

Syntax

MODOVERFLOW = {n|3}

Parameters

n

An INTEGER value to use in testing for divergence. As Oracle OLAP calculates each equation in a simultaneous block, it constructs a comparison value that is based on the results of the equation for the current iteration and the previous iteration. When the comparison value meets a divergence test, the equation is considered to have diverged.

The comparison value that is tested is as follows.

(thisResult - prevResult) / (prevResult + MODGAMMA)

where thisResult is the result of this iteration and prevResult is the result of the previous iteration

In the preceding calculation, MODGAMMA is an INTEGER option that controls the degree to which the comparison value represents the absolute amount of change between iterations versus the proportional change. The default value of MODGAMMA is 1.

In the divergence test, Oracle OLAP tests whether the comparison value is greater than 10 to the power of MODOVERFLOW. The calculation for this test is as follows.

Comparison value  >  10**MODOVERFLOW

For the equation to be considered to have diverged, the comparison value must meet the test described earlier. The default value of MODOVERFLOW is 3. With this default, the comparison value meets the test when it is greater than 1000.

Usage Notes

Equation Divergence

When an equation diverges, an error occurs. The MODERROR option controls the action that Oracle OLAP takes when an error occurs.

Faster Divergence During Development

While you are developing a model, you can sometimes save time by using a small value for MODOVERFLOW. When Oracle OLAP is performing many iterations over a particular simultaneous block, a smaller value of MODOVERFLOW can cause rapid divergence of that block. When you set the MODOVERFLOW option to CONTINUE, execution of the model continues when the divergence occurs, and you can concentrate on debugging the other blocks in the model. After you have debugged the model, you can use a larger value for MODOVERFLOW.

Examples

Data Type

ID

Syntax

MODSIMULTYPE = {'AITKENS'|'GAUSS'}

Parameters

'AITKENS'

(Default) Oracle OLAP uses the Aitkens delta-squared solution method. In the first two of every three iterations over a block of simultaneous equations, the equations are solved using the values from the previous iteration, and the results are tested for convergence and divergence. In every third iteration, the results are obtained not by solving the equations, but by making a next-guess calculation. This calculation uses the results of the previous three iterations. The results of the guesses are not tested for convergence and divergence, and the solution always continues to the next iteration.

'GAUSS'

Oracle OLAP uses the Gauss-Seidel solution method. Equations in a simultaneous block are solved in each iteration over the block. The results are tested for convergence and divergence in each iteration.

Usage Notes

Solving Simultaneous Blocks

Oracle OLAP uses an iterative method to solve the equations in a simultaneous block. In each iteration, except the next-guess iterations in an Aitkens solution, a comparison value is calculated from the result of the current iteration and the result of the previous iteration. When the comparison value falls within a specified tolerance (see the MODTOLERANCE option), the equation is considered to have converged. When the comparison value is too great (see the MODOVERFLOW option), the equation is considered to have diverged and solution of the block ends.

When all equations in a block converge, the block is considered solved. When any equation diverges or when any equation fails to converge after a specified number of iterations (see the MODMAXITERS option), solution of the block (and of the model) fails and Oracle OLAP generates an error.

Next-Guess Calculation

The Aitkens method requires three values to perform a next-guess calculation. Therefore, in the first three iterations over a simultaneous block, Oracle OLAP solves the equations. The fourth iteration is a next-guess iteration that uses the results from the first three iterations in its calculation.

Thereafter, every third iteration is a next-guess iteration that calculates results by using the previous guess and the equation results from the intervening two iterations. For example, the seventh iteration makes a next-guess calculation that is based on the guess from the fourth iteration and the equation results from the fifth and sixth iterations.

Memory Required

The Aitkens method usually speeds convergence, and it generally produces more accurate results than the Gauss-Seidel method. However, the Aitkens method requires more memory because the results of three previous iterations are stored.

In general, use the Aitkens method. Use the Gauss-Seidel method only when limited memory is a problem on your system.

Handling NA Values When Solving Simultaneous Blocks in a Model

In calculating equation results and making next-guess calculations, Oracle OLAP observes the setting of the NASKIP2 option. NASKIP2 controls how NA values are handled when + (plus) and - (minus) operations are performed. The setting of NASKIP2 is important when you specify a solution variable that contains NA values. Because the values in the solution variable are used as the initial values in the first iteration over a simultaneous block, the results of the equations might be NA when there are NA values in the solution variable. An NA result in the first iteration might also produce NA results in later iterations. Therefore, to avoid obtaining NA for the results, you can ensure that the solution variable does not contain NA values or you can set NASKIP2 to YES before running the model.

Data Type Problems

A simultaneous equation might fail to converge when it assigns data to a variable with an INTEGER data type or when you specify a solution variable with an INTEGER data type for a dimension-based model. Oracle OLAP converts the data to decimal values when it calculates the equation in each iteration, but the results are stored in the INTEGER variable between iterations which has the effect of rounding the values and thereby interfering with a progression toward convergence.

Function Problems

A simultaneous equation might fail to converge when it contains a function that produces rounded values (such as INSTRB or ROUND) or when it contains a function that introduces discontinuities in the data (such as MAX or MIN).

Starting-Value Problems

The solution of a simultaneous block is sensitive to starting values. For example, when a model has a proportional relationship between two model values, then starting values close to zero inhibits convergence. Consequently, attempt to use starting values that are reasonable for the equations being solved.

Order of Equations

The solution of a simultaneous block is also sensitive to the order of the equations. When you compile a model, the model compiler determines an optimal equation order that is based on the dependencies among the equations.

To force the equations in a simultaneous block to be solved in a particular order, you can write the equations in the desired order and set the MODINPUTORDER option to YES before compiling the model. When MODINPUTORDER is YES, the model compiler leaves the equations in a simultaneous block in the order in which they appear in the model.

By placing simultaneous equations in a particular order and setting MODINPUTORDER to YES before compiling the model, you might be able to encourage convergence in some models. In general, however, it is preferable to rely on the model compiler to order the equations.

Producing an Execution Report

After running a model, you can use the MODEL.XEQRPT program to produce a report about the execution of the model.

Examples

Data Type

INTEGER

Syntax

MODTOLERANCE = {n|3}

Parameters

n

An INTEGER value to use in testing for convergence. As Oracle OLAP calculates each equation in a simultaneous block, it constructs a comparison value that is based on the results of the equation for the current iteration and the previous iteration. When the comparison value passes a tolerance test, the equation is considered to have converged.

The comparison value that is tested is as follows.

(thisResult - prevResult) / (prevResult+ MODGAMMA)

where thisResult is the result of this iteration and prevResult is the result of the previous iteration

In the preceding calculation, MODGAMMA is an INTEGER option that controls the degree to which the comparison value represents the absolute amount of change between iterations versus the proportional change. The default value of MODGAMMA is 1.

In the tolerance test, Oracle OLAP tests whether the comparison value is less than 10 to the negative power of MODTOLERANCE. The calculation for this test is as follows.

Comparison value  <  10**-MODTOLERANCE

An equivalent way of writing this calculation is as follows.

Comparison value  < (1 / (10**MODTOLERANCE))

For the equation to be considered to have converged, the comparison value must meet the test described earlier. The default value of MODTOLERANCE is 3. With this default, the comparison value meets the test when it is less than 0.001.

Usage Notes

Failure to Converge

When an equation fails to converge after a specified number of iterations, an error occurs. The MODMAXITERS option controls the maximum number of iterations that are attempted. The MODERROR option controls the action that Oracle OLAP takes when an error occurs.

Precision of Results

Because MODTOLERANCE controls how closely results of an equation must match between iterations, it therefore controls the precision of the results of the solution. A small value of MODTOLERANCE results in less precision, while a large value provides more precision.

Large and Small Values

When a model contains some equations with large values and some equations with very small values, it might be preferable to increase the value of the MODGAMMA option rather than decreasing MODTOLERANCE. By increasing MODGAMMA, you might be able to force equations with small values to converge more quickly while retaining the precision of equations with large values.

Faster Convergence During Development

While you are developing a model, you might want to use a small value for MODTOLERANCE. While this gives less precise results, the model equations converges more quickly. After you have debugged the model, you can increase the value of MODTOLERANCE and thereby increase the precision of the final results.

Options for Controlling the Solution of Simultaneous Blocks

For a list of all the options that you can use to control the solution of simultaneous blocks, see "Model Options".

Examples

Data Type

BOOLEAN

Syntax

MODTRACE = {YES|NO}

Parameters

YES

Oracle OLAP sends the text of each model equation to the current outfile before calculating the model equation, and then sends the results of the calculation to the current outfile.

When you have used a DBGOUTFILE statement to specify a debugging file, Oracle OLAP sends MODTRACE output to the debugging file instead of the current outfile.

NO

(Default) Oracle OLAP does not send the text of model equations and results to a file while a model executes.

Usage Notes

Previewing the Solution Order

MODTRACE sends the equations of a model to the current outfile in the order in which they are being solved. Before you run the model, you might want to use the MODEL.COMPRPT program to get a preview of the solution order. A preview can be especially helpful when the model is large and complex. The MODEL.COMPRPT program, which you can run after compiling a model, produces a report that shows how the compiler has organized the model equations into blocks and the order in which the blocks and equations are solved.

Understanding Trace Information

MODTRACE shows the name of the current model on each line of the trace. The trace includes the following types of lines.

• Block. A block line gives the block number and block type of the block that is about to be executed. The type of block can be simple, step-forward, step-backward, or simultaneous. For a step-forward or step-backward block, the block line specifies the dimension being stepped over. For a simultaneous block with a cross-dimensional dependency, the block line specifies the dimensions involved in the dependency. See MODEL command for information on blocks in a model.

• Iteration. These lines occur in simultaneous blocks and specify the number of the iteration that is about to be performed for the current block. When you are using the Aitkens solution method, the next-guess iterations are identified. (The MODSIMULTYPE option determines the solution method being used.)

• Equation. The equation that is about to be calculated.

• Results. A results line follows each equation line and shows the results assigned by the equation. It shows the variable to which the results were assigned and the current value of each model dimension. In a simultaneous block, it also shows the current iteration number. For example, when actual is the solution variable and the model dimensions are line and month, a results line in a simultaneous block might look like the following one.

  (MOD= INCOME.CALC) ACTUAL (LINE OPR.INCOME MONTH 'JAN96'
     ITER 1) = 108.9600000

Using MODTRACE with Dimension-Based Equations

When you run a model that contains dimension-based equations, Oracle OLAP automatically loops over all the dimensions of the solution variable. In the trace, the results lines show the current value of each dimension listed in a DIMENSION statement, but they do not show the current values of extra dimensions that are not listed in DIMENSION statement. See DIMENSION (in models) for more information about using DIMENSION statements.

Thus, when the model dimensions are line and month, and when the solution variable is dimensioned by line, month, and division, the current value of division is not shown in the results lines. Oracle OLAP executes the entire model for the first value in the status of division, then for the second value in the status, and so on.

When you run a model that assigns values to variables, Oracle OLAP automatically loops over all the dimensions (or bases of a composite) of those variables. In this case, the current value of each of the variable's dimensions is shown in the trace.

Examples

Data Type

TEXT

Syntax

MONTHABBRLEN = specification [;|, specification]... 

where specification is a text expression that has the following form:

     startpos [ - endpos] : length

Parameters

startpos [-endpos]

Numbers that represent the first and last months whose abbreviation length is defined by length. These numeric positions apply to the corresponding lines of text in the MONTHNAMES option. You can specify these ranges of values in reverse order, endpos [-startpos], if you prefer.

The MONTHNAMES option can have more than 12 lines, so you can specify startpos and endpos greater than 12 in the setting of MONTHABBRLEN. When you specify a range where neither startpos nor endpos has a corresponding text value in the MONTHNAMES option, MONTHABBRLEN has no text values to abbreviate for that range. When you later change your month names list so that startpos is valid, the specified abbreviation is applied.

length

A number that specifies the length in characters (not bytes) of abbreviated month names. When you do not specify an abbreviation length for a given position in the MONTHNAMES option, or when you explicitly set a given position to zero, the default abbreviation is used. The default abbreviations are one character for <MT> and three characters for <MTXT> and <MTXTL>. Abbreviations are never used when you have designated the full name specifications <MTEXT> and <MTEXTL>.

Usage Notes

Ambiguous Month Names

You can use MONTHABBRLEN to interpret ambiguous names, for example, whether A stands for April or August. When the MONTHABBRLEN for April was 1 and for August was 2, then A would always match April, and it would require at least Au to match August. This interpretation does not depend on the order of April and August in the year; it would work the same way when the two months were reversed. If, on the other hand, the MONTHABBRLEN for each of these was 2, then A would not match either one, and you would have to enter at least Ap or Au to get a match.

Examples

Data Type

TEXT

Syntax

MONTHNAMES = name-list

Parameters

name-list

A multiline text expression that lists the names of the 12 months of the year. Each month name occupies a separate line. Regardless of which month you are treating as the first month of the year, the list must begin with the name for January. The default value is the list of English month names, all in capital letters.

You can include more than 1 set of 12 names in your list. Any name in the list is considered a valid name for input. The thirteenth name is a synonym for the first name, the fourteenth name is a synonym for the second name, and so on.

Examples

Data Type

BOOLEAN

Syntax

MULTIPATHHIER = {YES|NO}

Parameters

YES

Allows a detail data cell to aggregate in multiple paths to the same ancestor cell.

NO

(Default) Disallows a detail data cell to aggregate in multiple paths to the same ancestor cell.

Usage Notes

When to Use MULTIPATHHIER

The only time you set the MULTIPATHHIER option to YES is when a calculation requires the use of multiple paths.

Interpreting an XSHIERCK01 Error Message

When you use the AGGREGATE command, dimension hierarchies are automatically checked for circularity. When MULTIPATHHIER is set to NO, or when the default of NO has not been changed, then the following error message is displayed when a detail data cell uses multiple paths to the same aggregate data cell.

ERROR: (XSHIERCK01) One or more loops have been detected 
in your hierarchy n over N. The loops include 2 items 
(UNDIRECTED: X and Y).

In the preceding error message, X is the name of the detail data cell, and Y is the name of the ancestor cell into which the detail data cell takes multiple paths to aggregate. For more information, see Example 5-68.

This error message is displayed because the multiple paths taken by the detail data cell have been interpreted as a circular hierarchy. When this is a mistake and you did not intend to create multiple paths, then change the hierarchy. Otherwise, set the MULTIPATHHIER option to YES.

Examples

Data Type

BOOLEAN

Syntax

NASKIP = NO|YES

Parameters

NO

(Default) NA values are considered by aggregation functions. When any of the values being considered are NA, the function returns NA for that value.

YES

NA values are ignored by aggregation functions. Only expressions with actual values are used in calculations.

Usage Notes

Statements Affected by NASKIP

The following OLAP DML statements are affected by NASKIP.

• AGGREGATE command
• AGGREGATE function
• ANY
• AVERAGE
• COUNT
• CUMSUM
• DEPRDECL
• DEPRDECLSW
• DEPRSL
• DEPRSOYD
• EVERY
• FINTSCHED
• FPMTSCHED
• IRR
• LARGEST
• MEDIAN
• MOVINGAVERAGE
• MOVINGMAX
• MOVINGMIN
• MOVINGTOTAL
• NONE
• NPV
• SMALLEST
• STDDEV
• TCONVERT
• TOTAL
• VINTSCHED
• VPMTSCHED

Other statements are not affected by the setting of NASKIP, they always ignore NA values.

Examples

Data Type

BOOLEAN

Syntax

NASKIP2 = YES|NO

Parameters

YES

Zeroes are substituted for NA values in arithmetic operations using the + (plus) and - (minus) operators. The two special cases of NA + NA and NA - NA both result in NA.

NO

(Default) NA values are treated as NAs in arithmetic operations using the + (plus) and - (minus) operators. When any of the operands being considered is NA, the arithmetic operation evaluates to NA.

Usage Notes

Operators in Function Arguments

NASKIP2 is independent of NASKIP. NASKIP2 applies only to arithmetic operations with the + (plus) and - (minus) operators. NASKIP applies only to aggregation functions. However, when an expression argument to an aggregation function contains a+ (plus) and - (minus) operator, the results of the calculation depend on both NASKIP and NASKIP2. See Example 5-71.

How NASKIP2 Works

The following four lines show four steps in the evaluation of a complex expression that contains NAs when NASKIP2 is set to YES.

3 * (NA + NA) - 5 * (NA + 3)
   3 * NA     -    5 *  3
     NA       -      15
             -15

Examples

Data Type

TEXT

Syntax

NASPELL = {'text'|'NA'}

Parameters

text

The spelling to use for any NA value in output. When you specify an expression rather than a text literal, you can omit the single quotes. The default is NA.

Usage Notes

Setting NASPELL to "0"

Setting NASPELL to the text character 0 (zero) causes NA values to appear as 0. However, they are still treated as NAs in calculations.

Assigning NA Values

NASPELL affects only Oracle OLAP output; it does not affect the way you assign an NA value. For example, even when you have set NASPELL to NONE, you assign an NA value as follows.

var1 = NA

$NATRIGGER Takes Precedence over NASPELL

Oracle OLAP evaluates an $NATRIGGER property expression before applying the NASPELL option. When the $NATRIGGER expression is NA, then the NASPELL option has an effect.

Examples

Data Type

TEXT

Syntax

NLS_CALENDAR = option-value

Parameters

See Setting NLS Parameters in Oracle Database Globalization Support Guide for more information about NLS parameters, including valid values.

Examples

Data Type

TEXT

Syntax

NLS_CURRENCY = option-value

Parameters

See Setting NLS Parameters in Oracle Database Globalization Support Guide for more information about NLS parameters, including valid values.

Data Type

TEXT

Syntax

NLS_DATE_FORMAT = option-value

Parameters

See Setting NLS Parameters in Oracle Database Globalization Support Guide for more information about NLS parameters, including valid values.

Examples

See Example 5-74.

Data Type

TEXT

Syntax

NLS_DATE_LANGUAGE = option-value

Parameters

See Setting NLS Parameters in Oracle Database Globalization Support Guide for more information about NLS parameters, including valid values.

Examples

Data Type

TEXT

Syntax

NLS_DUAL_CURRENCY= option-value

Parameters

See Setting NLS Parameters in Oracle Database Globalization Support Guide for more information about NLS parameters, including valid values.

Data Type

TEXT

Syntax

NLS_ISO_CURRENCY = option-value

Parameters

See Setting NLS Parameters in Oracle Database Globalization Support Guide for more information about NLS parameters, including valid values.

Data Type

TEXT

Syntax

NLS_LANG 

Parameters

See Setting NLS Parameters in Oracle Database Globalization Support Guide for more information about NLS parameters, including valid values.

Examples

Data Type

TEXT

Syntax

NLS_LANGUAGE = option-value

Parameters

See Setting NLS Parameters in Oracle Database Globalization Support Guide for more information about NLS parameters, including valid values.

Examples

Data Type

TEXT

Syntax

NLS_NUMERIC_CHARACTERS = option-value

Parameters

See Setting NLS Parameters in Oracle Database Globalization Support Guide for more information about NLS parameters, including valid values.

Examples

Data Type

TEXT

Syntax

NLS_SORT = option-value

Parameters

See NLS_SORT in Oracle Database Globalization Support Guide for more information about the NLS_SORT parameter.

Examples

Data Type

TEXT

Syntax

NLS_TERITORRY = option-value

Parameters

See NLS_TERRITORY in Oracle Database Globalization Support Guide for information about NLS_TERRITORY parameters.

Examples

Data Type

TEXT

Syntax

NOSPELL

Examples

Data Type

BOOLEAN

Syntax

OKFORLIMIT = {NO|YES}

Parameters

NO

(Default) You cannot limit the dimension you are looping over within an explicit FOR loop.

YES

You can limit the dimension you are looping over within an explicit FOR loop.

Examples

Data Type

BOOLEAN

Syntax

OKNULLSTATUS = {YES|NO}

Parameters

YES

Indicates that null status lists are allowed. With this setting, when you execute a LIMIT command (without the IFNONE argument) that results in a dimension status list being null, the status list is set to null, and no error message is produced.

NO

(Default) Indicates that null status lists are not allowed. With this setting, when you execute a LIMIT command (without the IFNONE argument and without the NULL keyword) that would result in a dimension status list being null, the status list is not changed and an error message is produced.

Usage Notes

Conditions When OKNULLSTATUS Has No Effect

The value of OKNULLSTATUS has no effect in the following situations.

• When a LIMIT command includes an IFNONE argument.

• When a LIMIT command uses the NULL keyword to set a dimension status list to null.

• When a LIMIT command sets a valueset to null (unless the IFNONE argument is used). The valueset is set to null, and no error message is produced, even when OKNULLSTATUS is NO.

• When a LIMIT function is specified to return a null dimension status list. The value returned is NA, and no error message is produced, even when OKNULLSTATUS is NO.

See the LIMIT command for more information about using null status in dimensions and valuesets.

Examples

Data Type

INTEGER

Syntax

OUTFILEUNIT

Usage Notes

OUTFILE and OUTFILEUNIT

You automatically change the setting of OUTFILEUNIT whenever you specify a different file with an OUTFILE statement. For example, after the statement OUTFILE myfilename, the value of OUTFILEUNIT is the file unit number assigned to myfilename.

Examples

Data Type

INTEGER

Syntax

PAGENUM = n

Parameters

n

An INTEGER expression that specifies the page number to use for the next page of output. The default is 1.

Usage Notes

Starting with Page 1

When you are sending output to the default outfile, set both PAGENUM and LINENUM to 1 whenever you want to produce a report starting on page 1. You can set these options in the initialization section of your report program. When you use an OUTFILE statement to send output to a file, PAGENUM is automatically set to 1.

Setting PAGENUM in Mid-Page

The value of PAGENUM is incremented automatically when the last line of output has been generated on a page. When you set PAGENUM when an output page is only partially full, the value of PAGENUM is incremented by 1 before the next page is produced. Consequently, you usually have to set PAGENUM to a value of one less than the number you want to show on the following page.

The Effect of PAGING on PAGENUM

When you set PAGING to NO, PAGENUM stops counting and keeps its last value. When you reset PAGING to YES, PAGENUM resumes counting at the page number where it left off.

The Effect of OUTFILE on PAGENUM

When you use an OUTFILE statement to direct output to a file, PAGENUM is set to 1 for the file. When you use an OUTFILE statement with the EOF keyword to redirect output to the default outfile, PAGENUM contains the number that it last held for the default outfile.

Examples

Data Type

TEXT

Syntax

PAGEPRG = {'program'|'statement'|'NONE'|'STDHDR'}

Parameters

program

The name of a program to be executed after every page break. When you specify the program name as a text expression, you can omit the single quotes.

statement

The text of a statement to be executed after every page break. When you specify the statement as a text expression, you can omit the single quotes.

NONE

Indicates that no statement or program is executed automatically after a page break.

STDHDR

(Default) Makes STDHDR the program name that PAGEPRG stores. You can also set PAGEPRG to 'DEFAULT' to make STDHDR the program name that PAGEPRG stores. STDHDR produces a heading with the date and time on the left and the page number on the right.

Usage Notes

Using a STDHDR Program in a PAGPRG Program

When you create a PAGEPRG program, you can include the STDHDR program as a line in the program. Generally, you place STDHDR before the other statements that produces the custom heading. See Example 5-85.

Keeping Header Information Current

You can use Oracle OLAP functions such as TODAY, TOD, and PAGENUM in a program that is specified by the PAGEPRG option. You can also have a header program that accepts arguments, such as the title for a particular report. In this case you would set the PAGEPRG option to a text expression that invokes the report header program with arguments. See Example 5-86.

Setting PAGEPRG for a File

To set PAGEPRG for a file, first make the file your current outfile by specifying its name in an OUTFILE statement, then set PAGEPRG to the desired value. The new value remains in effect until you reset it or until you use an OUTFILE statement to direct output to a different outfile. When you direct output to a different outfile, PAGEPRG returns to its default value of 'STDHDR' for the file.

When you set PAGEPRG for the default outfile, the new value remains in effect until you reset it, regardless of intervening OUTFILE commands that send output to a file. That is, the value of PAGEPRG is automatically saved for the default outfile.

Examples

Data Type

INTEGER

Syntax

PAGESIZE = n

Parameters

n

An INTEGER expression that specifies the number of output lines on a page; n includes the top and bottom margins (controlled by the TMARGIN and BMARGIN options). The default is 66 lines, which is suitable for printing report output on 8 1/2" by 11" paper.

Usage Notes

Usable Output Lines with Standard Heading and Default Settings

When you use the standard heading and the default settings for the PAGESIZE, TMARGIN, and BMARGIN options, the total number of usable output lines is 61.

                              Output Lines
Lines from PAGESIZE                     66
Lines for TMARGIN                      - 2
Lines for the standard heading         - 2
Lines for BMARGIN                      - 1
Lines available for output              61

Eliminating Headings and Page Breaks

You can produce pages with no headings by using the statement PAGEPRG='NONE' or suppress page breaks entirely by using the statement PAGING = NO.

Setting PAGESIZE for a File

To set PAGESIZE for a file, first make the file your current outfile by specifying its name in an OUTFILE statement, then set PAGESIZE to the desired value. The new value remains in effect until you reset it or until you use an OUTFILE statement to direct output to a different outfile. When you direct output to a different outfile, PAGESIZE returns to its default value of 66 for the file.

When you set PAGESIZE for the default outfile, the new value remains in effect until you reset it, regardless of intervening OUTFILE commands that send output to a file. That is, the value of PAGESIZE is automatically saved for the default outfile.

Examples

Data Type

BOOLEAN

Syntax

PAGING = {YES|NO}

Parameters

YES

Produces output with page breaks, top and bottom margins, and page headings.

NO

(Default) Produces output that contains no page breaks, top and bottom margins, or page headings. Output is continuous, one line after another.

Usage Notes

Setting PAGING for a File

To set PAGING for a file, first make the file your current outfile by specifying its name in an OUTFILE statement, then set PAGING to the desired value. The new value remains in effect until you reset it or until you use an OUTFILE statement to direct output to a different outfile. When you direct output to a different outfile, PAGING returns to its default value of NO for the file.

When you set PAGING for the default outfile, the new value remains in effect until you reset it, regardless of intervening OUTFILE commands that send output to a file. That is, the value of PAGING is automatically saved for the default outfile.

Paging-Related Options

Oracle OLAP uses default values for page length, page headings, and top and bottom margins. You can change these values by setting the PAGESIZE, PAGEPRG, TMARGIN, and BMARGIN options. Other paging options that become meaningful when PAGING is set to YES are LINENUM, LINELEFT, and PAGENUM.

The value of PAGING for the current outfile determines whether the paging-related options are used. You must set PAGING to YES for the current outfile to make the paging options take effect.

Toggling PAGING On and Off

Toggling PAGING on and off, has the following effect on paging options:

• When you toggle PAGING from on (YES) to off (NO):

  • The value of the LINENUM option continues to increment as more output lines are produced.

  • The LINELEFT option is set to PAGESIZE.

  • The PAGENUM option stops counting and retains its current value

• When you toggle PAGING from off (NO) to on (YES):

  • LINENUM is set to 1 and it begins counting lines on the current page.

  • LINELEFT begins counting the lines left on the current page.

  • PAGENUM resumes counting at the page number where it left off.

Changing Outfiles

When you use an OUTFILE statement to direct output to a file, all the paging-related options are set to their default values for the file. When you use an OUTFILE statement with the EOF keyword to redirect output to the default outfile, the paging-related options contain the values that they last held for the default outfile.

Examples

Data Type

BOOLEAN

Syntax

PARENS = {YES|NO}

Parameters

YES

Encloses negative values in parentheses, instead of using a minus sign.

NO

(Default) Uses a minus sign to represent negative values.

Usage Notes

Overriding PARENS

The setting of the PARENS option is overridden by a PAREN or NOPAREN attribute in a HEADING, REPORT, or ROW command. The PAREN attribute specifies the use of parentheses; the NOPAREN attribute specifies the use of a minus sign.

Allowing Space for Parentheses

When you use parentheses to represent negative values in a report, Oracle OLAP lines up the positive and negative values in the column. To do this, it reserves the right-most character in each numeric column for the closing parenthesis. The column is always reserved, even when there are no negative values in the output. Consequently, each value requires more space than when you use the minus sign, and you might have to increase your column width to accommodate your data.

Examples

Data Type

BOOLEAN

Syntax

PERMITERROR = NO | YES

Parameters

NO

When you set PERMITERROR to NO, an error condition is not created on attempted access of a variable for which read or write permission is denied with a PERMIT statement. Values for which you do not have read permission are displayed as NAs. When you try to change a value for which you do not have write permission, the request is ignored.

YES

(Default) When PERMITERROR is YES, an error is signaled upon attempted access of a variable for which read or write permission is denied with a PERMIT statement. The error, which can be trapped, terminates the Oracle OLAP operation that initiated the illegal access.

Usage Notes

PERMITERROR With Non-Data Objects

The setting of PERMITERROR is ignored for violations of permission for non-data objects such as programs, models, and valuesets. Attempted access of variables and relations with permission, whether or not they have dimensionality, is always affected by the setting of PERMITERROR.

Maintaining Dimensions

The setting of PERMITERROR is ignored for violations of maintain and permit permission. Attempted violations of permission to maintain dimensions and to change permission are always treated as errors. Attempted violations of read or write permission for dimensions are, similarly, always treated as errors.

Obtaining Data Without Full Permission

When PERMITERROR is YES and you attempt to fetch a dimensioned variable that contains values that do not have read permission, an error condition is created when the first of those values is encountered. You can avoid creating an error condition by limiting the dimensions in advance so that only permissible values are in status, or by setting PERMITERROR to NO, before doing the report.

Examples

Data Type

BOOLEAN

Syntax

PERMITREADERROR = NO | YES

Parameters

NO

(Default) When the value ofPERMITREADERROR is YES, an error condition is not created on attempted access of a variable, valueset, formula, or relation for which read or write permission is denied with a PERMIT statement. Values for which you do not have read permission are displayed as NAs. When you try to change a value for which you do not have write permission, the request is ignored.

YES

When PERMITERROR is YES , an error is signaled upon attempted to read a variable, valueset, formula, or relation for which read or write permission is denied with a PERMIT statement. The error, which can be trapped, terminates the Oracle OLAP operation that initiated the illegal access.

Data Type

BOOLEAN

Syntax

PRGTRACE = {YES|NO}

Parameters

YES

Oracle OLAP records each line in a program before it is executed.

NO

(Default) Oracle OLAP does not record each line in a program.

Usage Notes

PRGTRACE Output

PRGTRACE records the name of the current program at the beginning of each program line. It includes an equals sign to indicate a compiled line.

(PRG= SALESREP) . . .

It includes a colon to indicate an uncompiled line.

(PRG: SALESREP) . . .

A compiled line is a line that has been translated into an efficient internal form, whereas an uncompiled line has not. Oracle OLAP ordinarily stores lines in compiled form to make programs work more efficiently, especially programs that contain loops.

Uncompiled Program Lines

Oracle OLAP compiles a program before running it. Therefore, the only lines that are marked as uncompiled in the PRGTRACE output are lines that cannot be compiled, such as lines that include ampersand substitution.

Examples

Data Type

INTEGER

Syntax

RANDOM.SEED.1|RANDOM.SEED.2 = n

Parameters

n

An INTEGER expression that specifies the value to use when generating random numbers. The default is for RANDOM.SEED.1 is 12345 and RANDOM.SEED.2 is 1073.

Usage Notes

Reproducing a Random Sequence

As illustrated in Example 8-64, when you want to reproduce the same sequence of random numbers when you are developing and debugging your application programs, set RANDOM.SEED.1 and RANDOM.SEED.2 to some specific values just before using RANDOM. To duplicate the sequence, set these options to the same values just before using RANDOM again. Then changes in the behavior of your programs are caused by your changes to the programs and not by differing sequences of random numbers.

Examples

Syntax

RECURSIVE = {YES|NO}

Parameters

YES

Specifying YES allows a formula or $NATRIGGER expression to call itself. Set this option to YES when you define a formula or an expression for the $NATRIGGER property that uses a recursive method of computation.

NO

(Default) Specifying NO prevents a formula or $NATRIGGER expression from calling itself. When you attempt to evaluate a recursive formula or $NATRIGGER expression, then Oracle OLAP displays an error message, which states that the RECURSIVE option is currently set to NO. Until the workspace contains a recursive formula or $NATRIGGER expression, keep this option set to NO to detect errors that could result in infinite looping behavior.

Usage Notes

For Formulas and $NATRIGGER Expressions Only

When you set RECURSIVE to YES, only formulas and $NATRIGGER property expressions are affected. This option does not affect programs; that is, a program can be recursive regardless of the setting of the RECURSIVE option unless the program is a $NATRIGGER expression. A $NATRIGGER expression cannot call itself unless the RECURSIVE option is YES.

Limiting $NATRIGGER Recursion

You can limit the depth of recursion for $NATRIGGER property expressions with the TRIGGERMAXDEPTH option, which sets the maximum number of $NATRIGGER expressions that Oracle OLAP executes simultaneously.

Data Type

TEXT

Syntax

ROLE

Examples

Data Type

BOOLEAN

Syntax

ROOTOFNEGATIVE = YES|NO

Parameters

YES

Allows any attempt to obtain a root of a negative number. Consequently, a statement that attempts to obtain a root of a negative number executes without an error; however, the result of the attempt to obtain the root is NA. When you are working with a dimensioned variable or expression, setting ROOTOFNEGATIVE to YES enables you to obtain the root of most of the expression's values when a few of the values might be negative.

NO

(Default) Disallows any attempt to obtain a root of a negative number. Any statement that attempts to obtain a root of a negative number stops executing and an error message is produced.

Usage Notes

Raising to a Noninteger Power

Raising a number to a noninteger power (for example, 5 ** 0.3 or 14 ** 2.7) is an attempt to obtain a root.

Examples

Data Type

INTEGER

Syntax

SECONDS

Examples

Data Type

BOOLEAN

Syntax

SESSCACHE = {YES|NO}

Parameters

YES

The session cache is created to hold the data described in "What is an Oracle OLAP Session Cache".

NO

Oracle OLAP does not read or write to the session cache. When you specify NO, caching does not occur even when you have specified caching by coding a CACHE SESSION statement in the specification for one or more aggmap objects, by setting one or more $VARCACHE properties to SESSION, or by setting the VARCACHE option to SESSION.

Usage Notes

What is an Oracle OLAP Session Cache?

An Oracle OLAP session cache is a special place in memory used to hold:

• All data that was calculated on the fly when an AGGREGATE function executed in the following situations:

  • The specification for the aggregation included a CACHE SESSION.

  • The specification for the aggregation did not include a CACHE SESSION statement, but the variable being aggregated had a $VARCACHE property with the value of SESSION.

  • The specification for the aggregation did not include a CACHE SESSION statement and the variable being aggregated did not have a $VARCACHE property, but the VARCACHE option was set to SESSION.

• The NA values (only) that were calculated when an AGGREGATE function executed and the specification for the aggregation included a CACHE NA statement.

• All data that was calculated when a $NATRIGGER expression executed in the following situations:

  • The variable with the $NATRIGGER property also had a $VARCACHE property with the value of SESSION.

  • The variable with the $NATRIGGER property did not have a $VARCACHE property, but the VARCACHE option was set to SESSION.

There is one internal cache for a session. Cached data is ignored by UPDATE and COMMIT statements. However, once data is cached, Oracle OLAP uses the values in the cache for all calculations unless an AGGREGATE function with the FORCECALC keyword executes. In this case, the FORCECALC keyword specifies that Oracle OLAP recalculate the values.

When a session is terminated, its cache is cleared. To clear the session cache without terminating the session, issue a CLEAR statement.

The effectiveness of a session cache is tracked in the V$AW_CALC dynamic performance view.

Data Type

TEXT

Syntax

SESSION_NLS_LANGUAGE

Examples

For examples of retrieving how the value of SESSION_NLS_LANGUAGE is impacted by changes in the value of NLS_LANGUAGE and STATIC_SESSION_LANGUAGE, see Example 4-9 and Example 5-102.

Data Type

TEXT

Syntax

SPARSEINDEX = {'BTREE'|'HASH'}

Parameters

BTREE

A standard indexing method that is recommended for composites. Use BTREE unless you are an advanced user. BTREE tends to group similar values together, which results in better locality of access. BTREE is the default algorithm.

HASH

A standard indexing method that should only be used when a composite has only two or three base dimensions. HASH is generally not recommended for composites because using HASH results in a very large index table, which can be too large to fit into memory.

Examples

Data Type

INTEGER

Syntax

SQLBLOCKMAX = records

Parameters

records

An INTEGER that identifies the number of records you want fetched at one time. While you can set SQLBLOCKMAX to any INTEGER, no appreciable change in performance results in setting it over 100. The default is 10 records.

Usage Notes

Opening Cursors

Only cursors opened after SQLBLOCKMAX is reset use the new block size.

Number of Records

When a program typically opens a cursor, reads one record, and closes the cursor, set SQLBLOCKMAX to 1. Otherwise, the SQL FETCH statement retrieves 10 records and discards 9 of them. The same is true for other routine fetches of less than 10 records.

Block Size

When your program is fetching small records, you can increase SQLBLOCKMAX to reduce the number of blocks required for the fetch. Oracle OLAP fetches the data into a 64K buffer. The block size in bytes is the number of records multiplied by the size of the records. When the block size exceeds the 64K limit imposed by the buffer, Oracle OLAP automatically reduces the number of records fetched. See Example 5-98.

Examples

Return Value

INTEGER. 0 after a successful operation, -1 after an error, or 100 after all requested rows have been fetched.

Syntax

SQLCODE

Usage Notes

Handling SQL Errors

Oracle OLAP does not signal an error when SQLCODE becomes nonzero. Therefore, your program must test the value of SQLCODE and take the appropriate action. Because each SQL operation sets SQLCODE, you must test for errors after each operation to avoid missing an error condition.

You can write programs that look for a specific error code. For example, the most common warning code is 100, which indicates that the cursor reached the end of its table selection and the FETCH statement is complete.

Examples

Data Type

TEXT

Syntax

SQLERRM

Usage Notes

Oracle Relational Manager

You can set the SQLMESSAGES option to YES to send the value of SQLERRM to the current output file automatically.

Examples

Data Type

BOOLEAN

Syntax

SQLMESSAGES = {YES|NO}

Parameters

YES

Error messages are sent to the current output file.

NO

(Default) Error messages are only stored as values of SQLERRM.

Usage Notes

Typical Usage

You want to set SQLMESSAGES to YES while you are developing an application so that you can diagnose errors quickly. When your application is in use, you probably want it to capture and handle errors in a different manner with SQLMESSAGES set to NO.

Data Type

BOOLEAN

Syntax

STATIC_SESSION_LANGUAGE = NO | YES

Parameters

NO

Specifies that whenever the value of the NLS_LANGUAGE option changes, Oracle OLAP changes the value of SESSION_NLS_LANGUAGE to the value of the NLS_LANGUAGE option. (Default)

YES

Specifies that the value of SESSION_NLS_LANGUAGE does not change when the value of NLS_LANGUAGE changes.

Examples

Data Type

TEXT

Syntax

THIS_AW

Data Type

ID

Syntax

THOUSANDSCHAR

Examples

Data Type

INTEGER

Syntax

TMARGIN = n

Parameters

n

An INTEGER expression that specifies the number of lines to set aside for the top margin in a report. The default is 2.

Usage Notes

Setting TMARGIN for a File

To set TMARGIN for a file, first make the file your current outfile by specifying its name in an OUTFILE statement, then set TMARGIN to the desired value. The new value remains in effect until you reset it or until you use an OUTFILE statement to direct output to a different outfile. When you direct output to a different outfile, TMARGIN returns to its default value of 2 for the file.

When you set TMARGIN for the default outfile, the new value remains in effect until you reset it, regardless of intervening OUTFILE commands that send output to a file. That is, the value of TMARGIN is automatically saved for the default outfile.

Examples

Syntax

TRACEFILEUNIT

Usage Notes

Use of the TRACEFILEUNIT Value

With the OUTFILE or DBGOUTFILE commands, you can specify the unit number stored in the TRACEFILEUNIT option to send the output to the Oracle trace file.

Examples

Data Type

INTEGER

Syntax

TRIGGERMAXDEPTH = n

Parameters

n

An INTEGER expression that specifies the maximum number of $NATRIGGER property expressions that can execute simultaneously. The default value is 50.

Usage Notes

About the $NATRIGGER Property

The TRIGGERMAXDEPTH option works with the $NATRIGGER property of a variable.

Recursive Triggers

While a $NATRIGGER expression is executing, it cannot be invoked again by a formula, program, or other $NATRIGGER expression that it invokes unless the RECURSIVE option is set to YES. The TRIGGERMAXDEPTH option governs the depth of recursion of $NATRIGGER expressions and prevents infinite recursions or excessively deep recursions, which can cause Oracle OLAP to malfunction.

Examples

Data Type

BOOLEAN

Syntax

TRIGGERSTOREOK = {NO|YES}

Parameters

NO

(Default) NA values are not permanently replaced with the $NATRIGGER property expression that is set for a variable.

YES

NA values are permanently replaced with the $NATRIGGER property expression that is set for a variable. The default value is NO.

For Oracle OLAP to permanently replace NA values for a variable with the valid $NATRIGGER property expression that is set for the variable, you must set both the TRIGGERSTOREOK option and the $STORETRIGGERVAL property for the variable to YES.

Usage Notes

About the $NATRIGGER and STORETRIGGERVAL Properties

The TRIGGERSTOREOK option works with the $NATRIGGER and $STORETRIGGERVAL properties of a variable.

Examples

Data Type

TEXT

Syntax

USERID

Examples

Data Type

BOOLEAN

Syntax

USETRIGGERS = {NO|YES}

Parameters

YES

(Default) Trigger programs execute.

NO

Trigger programs do not execute.

Examples

Syntax

VARCACHE = {VARIABLE | SESSION | NONE}

Parameters

VARIABLE

Specifies that Oracle OLAP stores the data in the variable in the database. When you specify this option, the results of the calculation are permanently stored in the variable when the analytic workspace is updated and committed.

SESSION

Specifies that Oracle OLAP caches the calculated data in the session cache (See "What is an Oracle OLAP Session Cache?"). When you specify this option, the results of the calculation are ignored during updates and commits and are discarded after the session.

Note:

When SESSCACHE is set to NO, Oracle OLAP does not cache the data even when you specify SESSION. In this case, specifying SESSION is the same as specifying NONE.

NONE

For data that is calculated on the fly using the AGGREGATE function, specifies that Oracle OLAP calculates the data each time the AGGREGATE function executes; Oracle OLAP does not store or cache the data calculated by the AGGREGATE function

 

Usage Notes

The VARCACHE Option Can Affect All Variables

When you set the VARCACHE option, its setting can affect all variables. When you have not set the $VARCACHE property on a variable and there is no CACHE statement in the aggmaps that you use with the AGGREGATE function to calculate data on the fly, then it is the VARCACHE option that determines how or if that data is stored.

Data Type

INTEGER

Syntax

WEEKDAYSNEWYEAR = days

Parameters

days

An INTEGER expression in the range 1 through 7 that indicates how many days in the year must be present in week 1 of that year. The default value for days is 1.

Examples

The Effect of WEEKDAYSNEWYEAR

The following statements send a list of weeks with the associated ending dates for each of those weeks to the current outfile.

DEFINE week DIMENSION WEEK
MAINTAIN week ADD '12 18 99' '1 15 00'
weekdaysnewyear = 2
REPORT W 22 CONVERT(week date)

These statements produce the following output.

WEEK             CONVERT(WEEK DATE)
-------------- --------------------
W51.99         18DEC99
W52.99         25DEC99
W53.99         01JAN00
W1.00          08JAN00
W2.00          15JAN00

January 1, 2000, is a Saturday, so setting WEEKDAYSNEWYEAR to 2 causes the week from January 2 through January 8 to appear as W1.00.

Data Type

BOOLEAN

Syntax

WRAPERRORS = NO | YES

Parameters

NO

Error messages are not wrapped. (Default)

YES

Error message are wrapped. Oracle OLAP inserts a line break after each group of 72 characters.

Usage Notes

Change in Default Behavior as of Oracle OLAP 10.2

In pre 10.2 releases of Oracle OLAP, long error messages are always wrapped.

Data Type

TEXT

Syntax

YESSPELL

Examples

Data Type

INTEGER

Syntax

YRABSTART = year

Parameters

year

A four-digit INTEGER expression that indicates the year at which the 100-year period begins. You can specify any value in the range 1000 to 9999. However, when you specify a value greater than 9900 for year, requests to read or display two-digit year values that correspond to a year later than 9999 result in a return value of NA. The default is 1950; two-digit year abbreviations are interpreted as being in the range 1950 to 2049 unless a different range is set through YRABSTART.

Examples

Data Type

BOOLEAN

Syntax

ZEROROW = {YES|NO}

Parameters

YES

Suppresses report rows that contain any numeric values when all the numeric values would be shown either as zeros or NAs.

NO

(Default) Produces all rows of the report, regardless of the values they contain.

Usage Notes

Non-Numeric Data

Even when a row contains non-numeric data, such as TEXT, ID, or BOOLEAN values, along with numeric values, the row is suppressed when ZEROROW is YES and all the numeric values would be shown either as zeros or NAs.

The Effect of NASPELL and ZSPELL

The value of NASPELL does not affect the way ZEROROW handles NA values. The value of ZSPELL does not affect the functioning of ZEROROW; numeric zero values are treated as zeros regardless of their spelling in output.

Examples

Data Type

TEXT

Syntax

ZSPELL = {'text'|'OFF'}

Parameters

text

The spelling to use as the default spelling for numeric zero values. When you specify an expression rather than a text literal, you can omit the single quotes.

OFF

(Default) Shows a zero (0) with the appropriate number of decimal places (determined by a DECIMAL attribute) for each numeric zero value.

Usage Notes

Assigning Zero Values

ZSPELL affects output only; it does not affect the way you assign a zero value. For example, even when you have set ZSPELL to NONE, you still assign a zero value as follows.

var1 = 0

Showing Decimal Places

The default of OFF means that a zero value is shown as 0 (zero), with the number of decimal places indicated by a DECIMAL attribute (for example, 0.00). When you set ZSPELL to the text character 0, zero values are shown as a 0 with no decimal places, regardless of any DECIMAL specification.

Effect of ZSPELL on Values Close to Zero

When your output includes a small number, such as 0.004, the number of decimal places shown affects whether ZSPELL treats the number as zero. See Example 5-116.

Examples