5 OLAP DML Options

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". Table 5-1, "Characters for Specify the Contents of the Error Log for ALLOCATE" 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

ALLOCERRLOGFORMAT = '%8p %8y %8z %e (%n)'
SHOW ALLOCERRLOGFORMAT
%8p %8y %8z %e (%n) 

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

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

Dim      Source   Basis
%-8d %-8s %-8b Description 
-------- -------- -------- -----------

 
Dim      Source   Basis
Division Budget   Budget  Description 
-------- -------- -------- -----------
Accdiv   650000    NA      A deadlock occurred allocating data (5) 

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 that 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

AWWAITTIME = 60

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

DEFINE test PROGRAM
PROGRAM
VARIABLE myint1 INTEGER
VARIABLE myint2 INTEGER
myint1 = 0
myint2 = 250/myint1
END

ERROR: (MXXEQ01) A division by zero was attempted. Set DIVIDEBYZERO to
YES if you want NA to be returned as the result of division by zero.
In DEMO!TEST PROGRAM:
myint2 = 250/myint1

DEFINE test PROGRAM
PROGRAM
VARIABLE myint1 INTEGER
VARIABLE myint2 INTEGER
myint1 = 0
myint2 = 250/myint1
END

ERROR: (MXXEQ01) A division by zero was attempted.  (If you want NA to
be returned as the result of a division by zero, set the DIVIDEBYZERO
option to YES.)
 

ERROR: (MXXEQ01) A division by zero was attempted.  (If you want NA to
be returned as the result of a division by zero, set the DIVIDEBYZERO
option to YES.)
In TESTBAD PROGRAM:
myint2 = 250/myint1
In EDDE.RUNCMD PROGRAM:

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

BMARGIN = 5

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

DEFINE week dimension WEEK ENDING '18Jan97'
MAINTAIN week ADD '21Dec96' '25Jan97'
REPORT W 22 CONVERT(week DATE)

WEEK             CONVERT(WEEK DATE)
-------------- --------------------
w51.96         21Dec96
w52.96         28Dec96
w1.97          04Jan97
w2.97          11Jan97
w3.97          18Jan97
w4.97          25Jan97

CALENDARWEEK = NO
REPORT W 22 CONVERT(week date)

WEEK             CONVERT(WEEK DATE)
-------------- --------------------
w50.97         21Dec96
w51.97         28Dec96
w52.97         04Jan97
w53.97         11Jan97
w1.98          18Jan97
w2.98          25Jan97

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

LIMIT district TO 'Atlanta'
LIMIT month TO 'Oct95' TO 'Mar96'
COLWIDTH = 6
REPORT ACROSS month: units

DISTRICT: ATLANTA
               ------------------UNITS------------------
               ------------------MONTH------------------
PRODUCT        Oct95  Nov95  Dec95  Jan96  Feb96  Mar96
-------------- ------ ------ ------ ------ ------ ------
Tents             503    345    259    279    305    356
Canoes            317    282    267    281    309    386
Racquets        1,365  1,270  1,357  1,125  1,304  1,263
Sportswear      3,065  2,327  1,955  2,591  2,829  3,137
Footwear        3,445  3,247  2,831  3,089  3,282  3,475

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

COMMAS = NO
LIMIT line TO 'Cogs'
LIMIT month TO 'Jan96' 'Feb96'
REPORT DOWN division ACROSS month: DECIMAL 0 actual

LINE: COGS
               -----ACTUAL------
               ------MONTH------
DIVISION       Jan96      Feb96
-------------- -------- ----------
Camping          368044     385120
Sporting         287558     315299
Clothing         567767     610727 

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

COMPILEMESSAGE = NO

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

DO_REPORT is being automatically compiled. 

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>'.

Table 5-2, "DATEFORMAT Templates for Day", Table 5-3, "DATEFORMAT Templates for Week", Table 5-4, "DATEFORMAT Templates for Month", and Table 5-5, "DATEFORMAT Templates for Year" present the valid formats for each component. The tables provide two display examples, one for March 1, 1990 and another for November 12, 2051.

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

Table 5-3, "DATEFORMAT Templates for Week" 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.

Table 5-4, "DATEFORMAT Templates for Month" 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.

Table 5-5, "DATEFORMAT Templates for Year" 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 Table 2-5, "Default VNFs for DWMQY Dimensions".

Examples

DEFINE datevar VARIABLE DATE
datevar = '24Mar97'
DATEFORMAT = '<DD>/<MM>/<YY>'
SHOW datevar

24/03/97

DATEFORMAT = '<MTEXTL> <D>, <YYYY>'
SHOW DATEVAR

March 24, 1997

DATEFORMAT = '<WTEXTL> <MTEXTL> <D>, <YYYY>'
SHOW DATEVAR

Monday March 24, 1997

PUSH DATEFORMAT
DATEFORMAT = 'Hello.  Today is <wtextl>, the <dtl> -
OF <MTEXTL>.'
SHOW TODAY
POP DATEFORMAT

Hello.  Today is Friday, the 30th of May.

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

DEFINE datevar VARIABLE DATE
dATEFORMAT = '<MTXT> <D>, <YYYY>'
DATEORDER = 'MDY'
DATEVAR = '3 5 1997'
SHOW DATEVAR

MAR 5, 1997

DATEORDER = 'DMY'
SHOW DATEVAR

MAY 3, 1997

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

DAYABBRLEN = '1-5:1, 6-7:2'
DATEFORMAT = '<WTXT> <MTXT> <D>, <YYYY>'
SHOW CONVERT ('2 august 2005' DATE)

T AUGUST 2, 2005

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

DAYNAMES = 'dimanche\nlundi\n-
mardi\nmercredi\njeudi\nvendredi\nsamedi'
SHOW DAYNAMES

dimanche
lundi
mardi
mercredi
jeudi
vendredi
samedi

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

SHOW 1000000.0 ** 133

DECIMALOVERFLOW = YES
SHOW 1000000.0 ** 133

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 "Comparing 2 Decimal Places with Best Presentation Format".) The default is 2.

Examples

DECIMALS = 0
LIMIT line TO 'COGS'
LIMIT month TO 'Jan96' 'Feb96'
REPORT DOWN division ACROSS month: budget

LINE: COGS
               -------BUDGET--------
               --------MONTH--------
DIVISION         Jan96      Feb96
-------------- ---------- ----------
Camping           355,933    385,308
Sporting          279,773    323,982
Clothing          528,370    546,468

DECIMALS = 2
SHOW JOINCHARS(1.1 'A')

1.10A

DECIMALS = 255
SHOW JOINCHARS(1.1 'A')

1.1A

Syntax

DEFAULTAWSEGSIZE = n

Parameters

n

The number of bytes.

Examples

SHOW DEFAULTAWSIZE

DEFAULTAWSIZE = 536870910 

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

SHOW 100 / 0

DIVIDEBYZERO = YES
SHOW 100 / 0

NA

Data Type

DECIMAL

Syntax

DSECONDS

Examples

DEFINE prodsummary PROGRAM
PROGRAM
VARIABLE t1 DECIMAL
t1 = dseconds
LIMIT product TO ALL
BLANK
FOR product
DO
  ROW WIDTH 16 name.product ACROSS month Jun96: DECIMAL 0 LSET -
   '$'WIDTH 18 <RSET ' (Actual)' sales rset ' (Plan)' sales.plan>
DOEND
BLANK
ROW WIDTH 35 LSET 'The program took ' rset ' seconds.' -
 (dseconds - t1)
END

3-Person Tents     $95,121 (actual)     $80,138 (plan)
Aluminum Canoes   $157,762 (actual)    $132,931 (plan)
Tennis Racquets    $97,174 (actual)     $84,758 (plan)
Warm-up Suits      $79,630 (actual)     $73,569 (plan)
Running Shoes     $153,688 (actual)    $109,219 (plan)
 
      The program took .20 seconds.

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

ECHOPROMPT = YES
OUTFILE 'newcalc.dat' 

Data Type

INTEGER

Syntax

EIFBYTES

Examples

LIMIT name TO OBJ(TYPE) EQ 'DIMENSION'
EXPORT ALL TO EIF FILE 'myfile.eif'
SHOW EIFBYTES

2,038

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

EIFEXTENSIONPATH = 'eifext' 

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

EIFSHORTNAMES = YES

Data Type

TEXT

Syntax

EIFTYPES

Examples

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

SHOW EIFNAMES

DISTRICT
PRODUCT
MONTH
UNITS
PRODUCTSET

SHOW EIFTYPES

DIMENSION
DIMENSION
DIMENSION
VARIABLE
VALUESET 

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

EIFUPDBYTES = 500000
IMPORT ALL FROM EIF FILE 'finance.eif' UPDATE 

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

SHOW EVERSION

Module Mgr, Version: 6.2.0.0.0, Build: 60232
OES Kernel, Version: 6.2.0.0.0, Build: 60232

EIFVERSION = 60232
EXPORT ALL TO EIF FILE 'myeif.eif' 

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

VARIABLE myint INTEGER
myint = 35/0
SHOW ERRORTEXT

ERROR: (MXXEQ01) A division by zero was attempted.  (If you want NA to
  be returned as the result of a division by zero, set the DIVIDEBYZERO
  option to YES.)

ERROR: A division by zero was attempted.  (If you want NA to be
  returned as the result of a division by zero, set the DIVIDEBYZERO
  option to YES.)

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

DEFINE myreport PROGRAM
LD Monthly Report
PROGRAM
TRAP ON CLEANUP NOPRINT
PUSH month DECIMALS LSIZE PAGESIZE
LIMIT month TO LAST 1
   ...
POP month DECIMALS LSIZE PAGESIZE
RETURN
CLEANUP:
POP month DECIMALS LSIZE PAGESIZE
SIGNAL ERRORNAME ERRORTEXT
END

Data Type

TEXT

Syntax

ERRORTEXT

Examples

DEFINE myreport PROGRAM
LD Monthly Report
PROGRAM
TRAP ON CLEANUP NOPRINT
PUSH month DECIMALS LSIZE PAGESIZE
LIMIT month TO LAST 1
   ...
POP month DECIMALS LSIZE PAGESIZE
RETURN
CLEANUP:
POP month DECIMALS LSIZE PAGESIZE
SIGNAL ERRORNAME ERRORTEXT
END

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, "Converting Text Values to Escape Sequences".

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

PRGTRACE = YES
EXPTRACE = YES 

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

AW DETACH myaw

DBMS_AW.EXECUTE('INF_STOP_ON_ERR = NO '); 
DBMS_AW.EXECUTE('INFILE attachmyaw.inf'); 
 
The current directory is MYDIR. 
ERROR: (ORA-34344) Analytic workspace MYAW is not attached. 
ERROR: (ORA-34344) Analytic workspace MYAW is not attached. 

PL/SQL procedure successfully completed. 

DBMS_AW.EXECUTE('INF_STOP_ON_ERR = YES '); 
DBMS_AW.EXECUTE('INFILE attachmyaw.inf'); 
    
The current directory is MYSPL. 
DECLARE 
  * 
ERROR at line 1: 
ORA-35166: (ORA-34344) Analytic workspace MYAW is not attached. 
ORA-06512: at "SYS.DBMS_AW", line 27 
ORA-06512: at "SYS.DBMS_AW", line 115 
ORA-06512: at line 8 

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

LIMIT district TO 'Atlanta'
LIMIT month TO 'Oct95' TO 'Mar96'
LCOLWIDTH = 10
COLWIDTH = 6
REPORT ACROSS month: units

DISTRICT: ATLANTA
            ------------------UNITS------------------
            ------------------MONTH------------------
PRODUCT     Oct95  Nov95  Dec95  Jan96  Feb96  Mar96
---------- ------ ------ ------ ------ ------ ------
Tents         503    345    259    279    305    356
Canoes        317    282    267    281    309    386
Racquets    1,365  1,270  1,357  1,125  1,304  1,263
Sportswear  3,065  2,327  1,955  2,591  2,829  3,137
Footwear    3,445  3,247  2,831  3,089  3,282  3,475

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

LIKECASE = YES
SHOW 'oracle' LIKE 'Oracle%'

NO

SHOW 'ORACLE' LIKE '%orc%'

NO

LIKECASE = NO
SHOW 'ORACLE' like 'orc%'

YES

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

DEFINE prodstat TEXT <product>
prodstat(product 'Tents') = - 
'What are the results of the fabric testing?'
prodstat(product 'Canoes') = -
'How has the flooding affected distribution?'
prodstat(product 'Racquets') = -
'The best-selling model is Whack_it!'
prodstat(product 'Sportswear') = -
'90% of the stock is ready to ship.'
prodstat(product 'Footwear') = -
'When are the new styles going to be ready?'

ARGUMENT findstring TEXT
FOR product
   IF prodstat LIKE findstring
   THEN SHOW JOINCHARS(product ' - ' prodstat)

CALL findeschar('%?%') "Find any text that contains a question mark.

Tents - What are the results of the fabric testing?
Canoes - How has the flooding affected distribution?
Footwear - When are the new styles going to be ready?

LIKEESCAPE = '?'
CALL findeschar('%?%') "Find any text that ends with a percent character.

CALL findeschar('%?%%') "Find any text that contains a percent character.

Sportswear - 90% of the stock is ready to ship.

CALL findeschar('%?%') "Find any text that contains an underscore.

Racquets - The best-selling model is Whack_it!

CALL findeschar('%??%') "Find any text that contains a question mark.

Tents - What are the results of the fabric testing?
Canoes - How has the flooding affected distribution?
Footwear - When are the new styles going to be ready?

LIKEESCAPE = '?'
DEFINE my_textdim DIMENSION TEXT
DEFINE my_intdim DIMENSION INTEGER
LISTNAMES DIMENSION LIKE '%?%'

3 DIMENSIONs
----------------
MY_INTDIM
MY_TEXTDIM
_DE_LANGDIM

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

LIMIT.SORTREL = NO

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

DEFINE prod DIMENSION TEXT
DEFINE year DIMENSION TEXT
DEFINE sales VARIABLE INTEGER <prod year>
 
PROD
--------------
Radios
TVs
 
YEAR
--------------
2003
2004
 
               --------SALES--------
               --------PROD---------
YEAR             Radios      TVs
-------------- ---------- ----------
2003                2,459      3,534
2004                3,366      3,018
 

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

WHILE LINENUM LT 5
BLANK
ROW W LSIZE ROW CENTER '--------------------------------'

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

IF LINESLEFT EQ 3
   THEN DO
   BLANK 2
   ROW W 50 'Subject To Change Without Notice.'
   DOEND

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

DEFINE MYLANGS DIMENSION TEXT
PROPERTY '$DEFAULT_LANGUAGE' -
'AMERICAN'
 
MYLANGS
--------------
FRENCH
AMERICAN
 

REPORT mylangs

MYLANGS
--------------
AMERICAN
 

" Try to LIMIT mylangs
 
LIMIT  mylangs to 'FRENCH'
ORA-33558: The status or contents of the MYAW3!MYLANGS dimension cannot be changed while the LOCK_LANGUAGE_DIMS option is set to yes.
 
" Got an error
SHOW LOCK_LANGUAGE_DIMS
TRUE
 
" Got the error because LOCK_LANGUAGE_DIMS was TRUE
"Change LOCK_LANGUAGE_DIMS to FALSE
LOCK_LANGUAGE_DIMS = FALSE
 
" Try to LIMIT mylangs again
 
LIMIT mylangs TO 'FRENCH'
 
" Verify if the LIMIT worked. It did
REPORT mylangs
MYLANGS
--------------
FRENCH
 
" Then relock the language 
LOCK_LANGUAGE_DIMS = TRUE

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, "Centering a Heading".

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

PAGEPRG = 'stdhdr'
LSIZE = 50
PAGING = YES
PAGE
HEADING WIDTH LSIZE CENTER 'Quarterly Sales'

96/05/13 15:05:16                            PAGE 1
 
                  Quarterly Sales

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

MODTRACE = YES
MODSIMULTYPE = 'GAUSS'
LIMIT division TO 'Camping'
income.bud budget

(MOD= INCOME.BUD) BLOCK 1: SIMULTANEOUS 
(MOD= INCOME.BUD) ITERATION 1: EVALUATION
(MOD= INCOME.BUD) revenue = marketing * 300 - cogs
   ...
(MOD= INCOME.BUD) BUDGET (LINE REVENUE MONTH 'JAN97' ITER 35) =  368.650399101
   ...
(MOD= INCOME.BUD) BUDGET (LINE REVENUE MONTH 'JAN97' ITER 36) =  369.209604252
   ...
(MOD= INCOME.BUD) BUDGET (LINE REVENUE MONTH 'JAN97' ITER 37) =  368.718556135
   ...
(MOD= INCOME.BUD) BUDGET (LINE REVENUE MONTH 'JAN97' ITER 38) =  369.149674626
   ...
(MOD= INCOME.BUD) BUDGET (LINE REVENUE MONTH 'JAN97' ITER 39) =  368.771110244
   ...
(MOD= INCOME.BUD) BUDGET (LINE REVENUE MONTH 'JAN97' ITER 40) =  369.103479583
(MOD= INCOME.BUD) END BLOCK 1

MODDAMP = 0.2
income.bud budget

(MOD= INCOME.BUD) BLOCK 1: SIMULTANEOUS 
(MOD= INCOME.BUD) ITERATION 1: EVALUATION
(MOD= INCOME.BUD) revenue = marketing * 300 - cogs
(MOD= INCOME.BUD) BUDGET (LINE REVENUE MONTH 'JAN97' ITER 1) =  276.200000000
   ...
(MOD= INCOME.BUD) BUDGET (LINE REVENUE MONTH 'JAN97' ITER 2) =  416.187139753
    ...
(MOD= INCOME.BUD) BUDGET (LINE REVENUE MONTH 'JAN97' ITER 3) =  368.021098186
   ...
(MOD= INCOME.BUD) BUDGET (LINE REVENUE MONTH 'JAN97' ITER 4) =  367.209906847
   ...
(MOD= INCOME.BUD) BUDGET (LINE REVENUE MONTH 'JAN97' ITER 5) =  369.271224267
  ...
(MOD= INCOME.BUD) BUDGET (LINE REVENUE MONTH 'JAN97' ITER 6) =  368.965397407
(MOD= INCOME.BUD) END BLOCK 1

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

MODERROR = 'DEBUG'
myModel actual

SHOW division
Camping 

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

MODTRACE = YES
MODSIMULTYPE = 'GAUSS'
LIMIT division TO 'Camping'
income.bud budget

(MOD= INCOME.BUD) BLOCK 1: SIMULTANEOUS 
   ...
(MOD= INCOME.BUD) BUDGET (LINE NET.INCOME MONTH 'JAN97' ITER 16) = 0.026243533
     ...
(MOD= INCOME.BUD) BUDGET (LINE NET.INCOME MONTH 'JAN97' ITER 17) = 0.024054312
   ...
(MOD= INCOME.BUD) BUDGET (LINE NET.INCOME MONTH 'JAN97' ITER 18) = 0.025788293
   ...
(MOD= INCOME.BUD) BUDGET (LINE NET.INCOME MONTH 'JAN97' ITER 19) = 0.024390642
    ...
(MOD= INCOME.BUD) BUDGET (LINE NET.INCOME MONTH 'JAN97' ITER 20) = 0.025501664
    ...
(MOD= INCOME.BUD) BUDGET (LINE NET.INCOME MONTH 'JAN97' ITER 21) = 0.024608562

(0.024608562967 - 0.025501664970 = 0.00087) / (0.025501664970 + 1)

MODGAMMA = 2
income.bud budget

(0.025788293304 - 0.024054312748 = 0.00086) / (0.024054312748 + 2)

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

DEFINE income.calc MODEL
MODEL
DIMENSION line month
Net.Income = Opr.Income - Taxes
Opr.Income = Gross.Margin - TOTAL(Marketing + Selling + R.D)
Marketing = LAG(Opr.Income, 1, month)
Gross.Margin = Revenue - Cogs
END

COMPILE income.calc
MODEL.COMPRPT income.calc

MODEL INCOME.CALC <LINE MONTH>
             BLOCK 1 (SIMPLE)
INCOME.CALC 5: gross.margin = revenue - cogs
              BLOCK 2 (SIMULTANEOUS <MONTH>)
INCOME.CALC 4: marketing = lag(opr.income, 1, month)
INCOME.CALC 3: opr.income = gross.margin - total(marketing + selling + r.d)
              END BLOCK 2
INCOME.CALC 2: net.income = opr.income - taxes
             END BLOCK 1

MODINPUTORDER = YES
COMPILE income.calc
MODEL.COMPRPT income.calc

MODEL INCOME.CALC <LINE MONTH>
                BLOCK 1 (SIMPLE)
INCOME.CALC 5: gross.margin = revenue - cogs
                 BLOCK 2 (SIMULTANEOUS <MONTH>)
INCOME.CALC 3:  opr.income = gross.margin - total(marketing + selling + r.d)
INCOME.CALC 4:  marketing = lag(opr.income, 1, month)
                 END BLOCK 2
INCOME.CALC 2: net.income = opr.income - taxes
                END BLOCK 1

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

MODMAXITERS = 100
myModel actual 

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

MODTRACE = YES
LIMIT division TO 'Camping'
income.est budget

(MOD= INCOME.EST) BLOCK 1: SIMULTANEOUS 
(MOD= INCOME.EST) ITERATION 1: EVALUATION
(MOD= INCOME.EST) selling = marketing * 3
(MOD= INCOME.EST) BUDGET (LINE SELLING MONTH 'JAN97' ITER 1) = 3
  ...
(MOD= INCOME.EST) BUDGET (LINE SELLING MONTH 'JAN97' ITER 2) = -997
   ...
(MOD= INCOME.EST) BUDGET (LINE SELLING MONTH 'JAN97' ITER 3) = 6.00902708124
   ...
(MOD= INCOME.EST) BUDGET (LINE SELLING MONTH 'JAN97' ITER 49) = 34.2715693388
   ...
(MOD= INCOME.EST) BUDGET (LINE SELLING MONTH 'JAN97' ITER 50) = -7.22300601861

MODOVERFLOW = 2
income.est budget

(-997 - 3) / (3 + 1) = 250

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

MODSIMULTYPE = 'GAUSS'
budget98 budget

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

MODTRACE = YES
MODSIMULTYPE = 'GAUSS'
LIMIT division TO 'Camping'
income.plan budget

(MOD= INCOME.PLAN) BLOCK 1: SIMULTANEOUS 
(MOD= INCOME.PLAN) ITERATION 1: EVALUATION
(MOD= INCOME.PLAN) marketing = .15 * net.income
(MOD= INCOME.PLAN) BUDGET(LINE MARKETING MONTH 'JAN97' ITER 1) = 11887.403671736
  ...
(MOD= INCOME.PLAN) BUDGET(LINE MARKETING MONTH 'JAN97' ITER 6) = 73379.713232251
    ...
(MOD= INCOME.PLAN) BUDGET(LINE MARKETING MONTH 'JAN97' ITER 7) = 73474.784648631
    ...
(MOD= INCOME.PLAN) BUDGET(LINE MARKETING MONTH 'JAN97' ITER 8) = 73446.025848156
(MOD= INCOME.PLAN) END BLOCK 1

(73474.784648631100 - 73379.713232251300) / (73379.713232251300 + 1) = 0.0013

(73446.025848156700 - 73474.784648631100) /(73474.784648631100 + 1) = 0.0004

MODTOLERANCE = 2
income.plan budget

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

DEFINE income.budget MODEL
LD Model for estimating budget items
MODEL
DIMENSION line month
Opr.Income = Gross.Margin - Marketing
Gross.Margin = Revenue - Cogs
Revenue = LAG(Revenue, 1, month) * 1.02
Cogs = LAG(Cogs, 1, month) * 1.01
Marketing = LAG(Opr.Income, 1, month) * 0.20
END

COMPILE income.budget
MODEL.COMPRPT income.budget

MODEL INCOME.BUDGET <LINE MONTH>
                   BLOCK 1 (SIMPLE)
INCOME.BUDGET  4:  revenue = lag(revenue, 1, month) * 1.02
INCOME.BUDGET  5:  cogs = lag(cogs, 1, month) * 1.01
INCOME.BUDGET  3:  gross.margin = revenue - cogs
                    BLOCK 2 (STEP-FORWARD <MONTH>)
INCOME.BUDGET  6:   marketing = lag(opr.income, 1, month) * 0.20
INCOME.BUDGET  2:   opr.income = gross.margin - marketing
                    END BLOCK 2
                   END BLOCK 1

LIMIT month TO 'Jan97' TO 'Mar97'
LIMIT division TO 'Camping'
MODTRACE = YES
income.budget budget

(MOD= INCOME.BUDGET) BLOCK 1: SIMPLE 
(MOD= INCOME.BUDGET) revenue = lag(revenue, 1, month) * 1.02
(MOD= INCOME.BUDGET) BUDGET (LINE REVENUE MONTH 'JAN97') = 744491.1966
(MOD= INCOME.BUDGET) BUDGET (LINE REVENUE MONTH 'FEB97') = 759381.020532
(MOD= INCOME.BUDGET) BUDGET (LINE REVENUE MONTH 'MAR97') = 774568.64094264
(MOD= INCOME.BUDGET) cogs = lag(cogs, 1, month) * 1.01
(MOD= INCOME.BUDGET) BUDGET (LINE COGS MONTH 'JAN97') = 382386.2323
(MOD= INCOME.BUDGET) BUDGET (LINE COGS MONTH 'FEB97') = 386210.094623
(MOD= INCOME.BUDGET) BUDGET (LINE COGS MONTH 'MAR97') = 390072.19556923
(MOD= INCOME.BUDGET) gross.margin = revenue - cogs
(MOD= INCOME.BUDGET) BUDGET (LINE GROSS.MARGIN MONTH 'JAN97') = 362104.9643
(MOD= INCOME.BUDGET) BUDGET (LINE GROSS.MARGIN MONTH 'FEB97') = 373170.925909
(MOD= INCOME.BUDGET) BUDGET (LINE GROSS.MARGIN MONTH 'MAR97') = 384496.44537341
(MOD= INCOME.BUDGET) BLOCK 2 STEP-FORWARD <MONTH>
(MOD= INCOME.BUDGET) marketing = lag(opr.income, 1, month) * 0.20
(MOD= INCOME.BUDGET) BUDGET (LINE MARKETING MONTH 'JAN97') = 39938.192
(MOD= INCOME.BUDGET) opr.income = gross.margin - marketing
(MOD= INCOME.BUDGET) BUDGET (LINE OPR.INCOME MONTH 'JAN97') = 322166.7723
(MOD= INCOME.BUDGET) marketing = lag(opr.income, 1, month) * 0.20
(MOD= INCOME.BUDGET) BUDGET (LINE MARKETING MONTH 'FEB97') = 64433.35446
(MOD= INCOME.BUDGET) opr.income = gross.margin - marketing
(MOD= INCOME.BUDGET) BUDGET (LINE OPR.INCOME MONTH 'FEB97') = 308737.571449 
(MOD= INCOME.BUDGET) marketing = lag(opr.income, 1, month) * 0.20
(MOD= INCOME.BUDGET) BUDGET (LINE MARKETING MONTH 'MAR97') = 61747.5142898
(MOD= INCOME.BUDGET) opr.income = gross.margin - marketing
(MOD= INCOME.BUDGET) BUDGET (LINE OPR.INCOME MONTH 'MAR97') = 322748.93108361
(MOD= INCOME.BUDGET) END BLOCK 2
(MOD= INCOME.BUDGET) END BLOCK 1

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

MONTHABBRLEN = '1-10:1, 11-12:2'
SHOW CONVERT ('2 August 2005' DATE)

02A05

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

MONTHNAMES = -
'JANUARY -
...
DECEMBER -
janvier -
...
decembre'

DEFINE datevar DATE
datevar = '27feb98'
DATEFORMAT = '<MTEXT> <D>, <YYYY>'
SHOW datevar

FEBRUARY 27, 1998

DATEFORMAT = 'le <D> <MTEXTL> <YYYY>'
SHOW datevar

le 27 fevrier 1998

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, "Defining Multiple Paths in a Hierarchy".

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

DEFINE geog TEXT DIMENSION
DEFINE path INTEGER DIMENSION
DEFINE geog.geog RELATION geog <geog path>
MAINTAIN geog ADD 'A1' 'b1' 'b2' 'Top'
MAINTAIN path ADD 2
geog.geog(geog 'A1' path 1) = 'B1'
geog.geog(geog 'A1' path 2) = 'B2'
geog.geog(geog 'B1' path 1) = 'Top'
geog.geog(geog 'B2' path 1) = 'Top'

DEFINE myvar INTEGER VARIABLE <geog>
myvar(geog 'A1') = 1
DEFINE myvar.aggmap <geog>
AGGMAP 'RELATION geog.geog'

AGGREGATE myvar USING myvar.aggmap
ERROR: (XSHIERCK01) One or more loops have been detected 
in your hierarchy GEOG.GEOG over GEOG. The loops include 2 
items (UNDIRECTED: A1 and TOP).

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

ALLSTAT
NASKIP = YES
SHOW TOTAL(sales)

63,181,743.50

NASKIP = NO
SHOW TOTAL(sales)

NA

LIMIT district TO 'Seattle'
LIMIT month TO 'Jul96' TO 'Mar97'
REPORT DOWN month sales

DISTRICT: SEATTLE
      ------------------------SALES-------------------------
      -----------------------PRODUCT------------------------
MONTH   Tents      Canoes    Racquets  Sportswear  Footwear
----- ---------- ---------- ---------- ----------  ---------
Jul96 123,700.17 157,274.03  60,198.52  78,305.97  78,019.87
Aug96 120,650.72 128,660.89  45,046.71  66,853.26  83,347.55
Sep96  97,188.43 122,702.13  42,257.14  63,777.36  99,464.05
Oct96  91,578.77  79,925.93  39,729.25  55,021.85  83,537.58
Nov96  56,044.34  77,357.10  39,024.93  44,004.12  65,216.94
Dec96  41,576.26  67,609.36  36,156.10  40,575.34  62,113.72
Jan97         NA         NA         NA         NA         NA
Feb97         NA         NA         NA         NA         NA
Mar97         NA         NA         NA         NA         NA

NASKIP = YES
REPORT DOWN month MOVINGMIN(sales -2, 0, 1, month)

DISTRICT: SEATTLE
      -----------MOVINGMIN(SALES -2, 0, 1, MONTH)-----------
      ---------------------PRODUCT--------------------------
MONTH   Tents      Canoes    Racquets  Sportswear  Footwear
----- ---------- ---------- ---------- ----------  ---------
Jul96 108,663.59 125,823.37 57,666.37  57,713.27   73,085.88
Aug96 119,066.18 128,660.89 45,046.71  60,322.88   78,019.87
Sep96  97,188.43 122,702.13 42,257.14  63,777.36   78,019.87
Oct96  91,578.77  79,925.93 39,729.25  55,021.85   83,347.55
Nov96  56,044.34  77,357.10 39,024.93  44,004.12   65,216.94
Dec96  41,576.26  67,609.36 36,156.10  40,575.34   62,113.72
Jan97  41,576.26  67,609.36 36,156.10  40,575.34   62,113.72
Feb97  41,576.26  67,609.36 36,156.10  40,575.34   62,113.72
Mar97         NA         NA         NA         NA         NA

NASKIP = NO
REPORT DOWN month MOVINGMIN(sales -2, 0, 1, month)

DISTRICT: SEATTLE
      ----------MOVINGMIN(SALES -2, 0, 1, MONTH)-------------
      ------------------------PRODUCT------------------------
MONTH   Tents      Canoes    Racquets  Sportswear  Footwear
----- ---------- ---------- ---------- ----------  ----------
Jul96 108,663.59 125,823.37  57,666.37  57,713.27   73,085.88
Aug96 119,066.18 128,660.89  45,046.71  60,322.88   78,019.87
Sep96  97,188.43 122,702.13  42,257.14  63,777.36   78,019.87
Oct96  91,578.77  79,925.93  39,729.25  55,021.85   83,347.55
Nov96  56,044.34  77,357.10  39,024.93  44,004.12   65,216.94
Dec96  41,576.26  67,609.36  36,156.10  40,575.34   62,113.72
Jan97         NA         NA         NA         NA          NA
Feb97         NA         NA         NA         NA          NA
Mar97         NA         NA         NA         NA          NA

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, "Effects of NASKIP and NASKIP2 When an Expression in an Aggregation Function Contains a Negative Values".

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

SHOW current.month

NA

NASPELL = 'None'
SHOW current.month

None

Data Type

TEXT

Syntax

NLS_CALENDAR = option-value

Parameters

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

Examples

NLS_CALENDAR = 'THAI BUDDHA' 

Data Type

TEXT

Syntax

NLS_CURRENCY = option-value

Parameters

See 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 Oracle Database Globalization Support Guide for more information about NLS parameters, including valid values.

Examples

See Example 5-74, "Setting the Language for Dates".

Data Type

TEXT

Syntax

NLS_DATE_LANGUAGE = option-value

Parameters

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

Examples

NLS_DATE_LANGUAGE = 'SPANISH'
NLS_DATE_FORMAT = 'Month DD, YYYY'

Septiembre 08, 2000 

Data Type

TEXT

Syntax

NLS_DUAL_CURRENCY= option-value

Parameters

See 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 Oracle Database Globalization Support Guide for more information about NLS parameters, including valid values.

Data Type

TEXT

Syntax

NLS_LANG 

Parameters

See Oracle Database Globalization Support Guide for more information about NLS database parameters.

Examples

AMERICAN_AMERICA.WE8ISO8859P1 

Data Type

TEXT

Syntax

NLS_LANGUAGE = option-value

Parameters

See Oracle Database Globalization Support Guide for more information about NLS database parameters.

Examples

AMERICAN_AMERICA.WE8ISO8859P1 

NLS_LANGUAGE = 'FRENCH'

FRENCH_AMERICAN.WE8ISO8859P1

Data Type

TEXT

Syntax

NLS_NUMERIC_CHARACTERS = option-value

Parameters

See Oracle Database Globalization Support Guide for more information about NLS database parameters.

Examples

NLS_NUMERIC_CHARACTERS = ', '

show 1234.56

1 234,56 

Data Type

TEXT

Syntax

NLS_SORT = option-value

Parameters

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

Examples

cerveza, Colorado, cheremoya, llama, luna, lago

NLS_SORT = 'BINARY'
SORT words A words
STATUS words
The current status of WORDS is:
Colorado, cerveza, cheremoya, lago, llama, luna

NLS_SORT = 'SPANISH'
SORT words A words
STATUS words
The current status of WORDS is:
cerveza, cheremoya, Colorado, lago, llama, luna

NLS_SORT = 'XSPANISH'
SORT words A words
STATUS words
The current status of WORDS is:
cerveza TO cheremoya, lago TO llama 

Data Type

TEXT

Syntax

NLS_TERITORRY = option-value

Parameters

See Oracle Database Globalization Support Guide for more information about NLS database parameters.

Examples

AMERICAN_AMERICA.WE8ISO8859P1 

SHOW TO_NUMBER('12345')
12,345.00

NLS_TERRITORY = 'FRANCE'

AMERICAN_FRANCE.WE8ISO8859P1

SHOW TO_NUMBER('12345')
12.345,00 

Data Type

TEXT

Syntax

NOSPELL

Examples

SHOW boolvar

non

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

 ...
OKFORLIMIT = YES
FOR market
    DO
      LIMIT market TO CHILDREN USING market.market 
      REPORT market
    DOEND
 ...

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

OKNULLSTATUS = YES

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

FILESET filenum PAGEPRG FILEQUERY(OUTFILEUNIT PAGEPRG)

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

DEFINE report.head PROGRAM
PROGRAM
STDHDR
BLANK
PAGING = YES
HEADING WIDTH LSIZE CENTER 'Annual Sales Report'
BLANK
IF PAGENUM GT 1
   THEN HEADING WIDTH LSIZE CENTER '(Continued)'
BLANK
END

PAGEPRG = 'report.head'

15JAN95 15:05:16                                      Page  2
                       Annual Sales Report
 
                           (Continued)

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, "Creating a Custom Heading".

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, "Using Program Arguments".

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

DEFINE report.head PROGRAM
PROGRAM
STDHDR
BLANK
HEADING WIDTH LSIZE CENTER 'Annual Sales Report'
BLANK
IF PAGENUM GT 1
   THEN HEADING WIDTH LSIZE CENTER '(Continued)'
BLANK
END

PUSH PAGEPRG PAGING
PAGEPRG = 'report.head'
PAGING = YES
      ... (body of report program)
  POP PAGEPRG PAGING

15JAN98  15:05:16                                      Page 1
 
                       Annual Sales Report

PAGEPRG = 'CALL report.head(\'Annual Sales Report\')'

ARGUMENT titlevar TEXT
STDHDR
BLANK
HEADING WIDTH LSIZE CENTER titlevar

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

PAGESIZE = 84

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

OUTFILE 'repfile.txt'
PAGING = YES
PAGESIZE = 84
TMARGIN = 6 

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

LIMIT line TO 'Cogs'
LIMIT division TO 'Sporting'
LIMIT month TO 'Jan96' TO 'Jun96'
PARENS = YES
DECIMALS = 0
REPORT DOWN month budget actual budget-actual

DIVISION: SPORTING
               --------------LINE--------------
               --------------COGS--------------
                                     BUDGET-ACT
MONTH            BUDGET     ACTUAL      UAL
-------------- ---------- ---------- ----------
Jan96            279,773    287,558     (7,785)
Feb96            323,982    315,299      8,683
Mar96            302,178    326,185    (24,007)
Apr96            386,101    394,544     (8,443)
May96            433,998    449,862    (15,864)
Jun96            448,042    457,348     (9,305)

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

PERMITERROR = no
DESCRIBE price

DEFINE PRICE VARIABLE DECIMAL <MONTH PRODUCT>
LD Wholesale Unit Selling Price
PERMIT READ WHEN product eq 'Tents'

LIMIT month TO 'Dec96'
REPORT price

                  ----PRICE----
                  ----MONTH----
PRODUCT               DEC96      
----------------  -------------
Tents                  165.64
Canoes                     NA
Racquets                   NA
Sportswear                 NA
Footwear                   NA

PERMITERROR = yes
REPORT price

ERROR: You do not have permission to read this value of PRICE

                 ---PRICE---
                 ---MONTH---
PRODUCT             DEC96
---------------  -----------
Tents                165.64

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

DEFINE salesrep PROGRAM
PROGRAM
PUSH month division line
TRAP ON cleanup
LIMIT month TO &ARGS
LIMIT division TO ALL
LIMIT line TO FIRST 1
REPORT DOWN division across month: dec 0 budget
 
cleanup:
POP month division line
END

PRGTRACE = yes
salesrep FIRST 3

(PRG= SALESREP) push month division line
(PRG= SALESREP) trap on cleanup
(PRG: SALESREP) limit month to &args
(PRG= SALESREP) limit division to all
(PRG= SALESREP) limit line to first 1
(PRG= SALESREP) report down division across month: dec0 budget
LINE: REVENUE
               -------------BUDGET-------------
               -------------MONTH--------------
DIVISION         JAN95      FEB95      MAR95
-------------- ---------- ---------- ----------
CAMPING          679,149    707,945    780,994
SPORTING         482,771    517,387    525,368
CLOTHING         983,888  1,016,528    992,331
(PRG= SALESREP) cleanup:
(PRG= SALESREP) pop month division line

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, "Producing Random Numbers", 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

DEFINE id DIMENSION TEXT
DEFINE myvar VARIABLE INTEGER <id>
 

myvar = 0
myvar = RANDOM (10, 20)
REPORT myvar
 
ID               MYVAR
-------------- ----------
a1                     11
a2                     19
a3                     14
 
myvar = 0
myvar = RANDOM (10, 20)
REPORT myvar
 
ID               MYVAR
-------------- ----------
a1                     16
a2                     13
a3                     12
 

myvar = 0
RANDOM.SEED.1 = 5
RANDOM.SEED.2 = 3
        myvar = RANDOM (10, 20)
REPORT myvar
 
ID               MYVAR
-------------- ----------
a1                     10
a2                     16
a3                     13
 
myvar = 0
RANDOM.SEED.1 = 5
RANDOM.SEED.2 = 3
myvar = RANDOM (10, 20)
REPORT myvar
 
ID               MYVAR
-------------- ----------
a1                     10
a2                     16
a3                     13

myvar = 0RANDOM.SEED.1 = 5
RANDOM.SEED.2 = 3
myvar = RANDOM (10, 20)
REPORT myvar
 
ID               MYVAR
-------------- ----------
a1                     10
a2                     16
a3                     13

myvar = 0
        myvar = RANDOM (10, 20)
REPORT myvar
 
ID               MYVAR
-------------- ----------
a1                     11
a2                     16
a3                     20

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

SHOW ROLE

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

SHOW SQRT(testnumber)

ROOTOFNEGATIVE = YES
SHOW SQRT(testnumber)

NA

Data Type

INTEGER

Syntax

SECONDS

Examples

DEFINE prodsummary PROGRAM
PROGRAM
VARIABLE t1 INTEGER
t1 = seconds
LIMIT product TO ALL
BLANK
FOR product
DO
  ROW WIDTH 16 name.product ACROSS month Jun96: DECIMAL 0 LSET -
   '$'WIDTH 18 <RSET ' (actual)' sales RSET ' (plan)' sales.plan>
DOEND
BLANK
ROW WIDTH 35 LSET 'the program took ' RSET ' SECOND(s).' -
 (SECONDS-t1)
END

3-Person Tents     $95,121 (actual)     $80,138 (plan)
Aluminum Canoes   $157,762 (actual)    $132,931 (plan)
Tennis Racquets    $97,174 (actual)     $84,758 (plan)
Warm-up Suits      $79,630 (actual)     $73,569 (plan)
Running Shoes     $153,688 (actual)    $109,219 (plan)
 
      The program took 2 second(s).

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, "Changing NLS_LANGUAGE" and Example 5-102, "Changing NLS_LANGUAGE Without Changing the Language of the OLAP Session".

REPORT myaw3!mylangs
MYLANGS
--------------
AMERICAN
FRENCH
 
REPORT myaw4!mylangs
MYLANGS
--------------
AMERICAN
GERMAN
 

REPORT myaw3!mylangs
MYLANGS
--------------
AMERICAN

REPORT myaw4!mylangs
MYLANGS
--------------
AMERICAN

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

SPARSEINDEX = 'HASH'

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, "Defining a Cursor with SQLBLOCKMAX".

Examples

SQLBLOCKMAX = 100
SQL DECLARE CURSOR c1 FOR SELECT * FROM mydata
SQL OPEN c1

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

WHILE SQLCODE EQ 0
   SQL FETCH cursor1 INTO :employee, :title

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

SQL CREATE TABLE Products -
   (Prod_ID CHAR(8) -
   Prod_Name VARCHAR(30) -
   Suggested_Price DECIMAL(10,2))
IF SQLCODE NE 0
   SHOW SQLERRM

SQL CREATE TABLE Products

ORA-00906: Missing left parenthesis.

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

SHOW NLS_LANGUAGEFRENCH
AMERICAN
 
" Make the session language static
STATIC_SESSION_LANGUAGE = yes
 
"Change the value of NLS_LANGUAGE to FRENCH
SET NLS_LANGUAGE= 'FRENCH'
 
SHOW OBJ(PROPERTY '$DEFAULT_LANGUAGE' 'mylangs')
AMERICAN
SHOW NLS_LANGUAGE
FRENCH
SHOW SESSION_NLS_LANGUAGE
AMERICAN
SHOW LOCK_LANGUAGE_DIMS
oui
SHOW STATIC_SESSION_LANGUAGE
oui
 
REPORT mylangs
MYLANGS
--------------
AMERICAN
 
REPORT prod_desc
               ------PROD_DESC------
               ------PRODUCTS-------
MYLANGS          PROD01     PROD02
-------------- ---------- ----------
AMERICAN       Trousers   Skirts

Data Type

TEXT

Syntax

THIS_AW

Data Type

ID

Syntax

THOUSANDSCHAR

Examples

SHOW THOUSANDSCHAR 
SHOW DECIMALCHAR

63,181,743.50

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

TMARGIN = 1

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

DBGOUTFILE TRACEFILEUNIT 

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

DEFINE d1 INTEGER DIMENSION
MAINTAIN d1 ADD 2
DEFINE v1 DECIMAL <d1>
PROPERTY '$NATRIGGER' 'v2 + 1'
DEFINE v2 DECIMAL <d1>
PROPERTY '$NATRIGGER' 'v3 + 1'
DEFINE v3 DECIMAL <d1>
PROPERTY '$NATRIGGER' 'v4 + 1'
DEFINE v4 DECIMAL <d1>
v4(d1 1) = 333.3
RECURSIVE = YES
TRIGGERMAXDEPTH = 2
SHOW v1
 

ERROR: Depth of NA trigger calls exceeds allowable (maximum depth 2)

TRIGGERMAXDEPTH = 3
SHOW v1

336.3

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

DEFINE d1 INTEGER DIMENSION
MAINTAIN d1 ADD 3
DEFINE v1 DECIMAL <d1>
PROPERTY '$NATRIGGER' '500.0'
v1(d1 1) = 333.3
 
REPORT v1

D1            V1
--------- ----------
        1     333.30
        2     500.00
        3     500.00

CONSIDER v1
PROPERTY DELETE '$NATRIGGER'
REPORT v1

D1            V1
--------- ----------
        1     333.30
        2         NA
        3         NA

CONSIDER v1
PROPERTY '$NATRIGGER' '800.0'
TRIGGERSTOREOK = YES
PROPERTY 'STORETRIGGERVAL' YES
REPORT v1

D1                 V1
-------------- ----------
             1     333.30
             2     800.00
             3     800.00

CONSIDER v1
PROPERTY DELETE '$NATRIGGER'
REPORT v1

D1                 V1
-------------- ----------
             1     333.30
             2     800.00
             3     800.00

Data Type

TEXT

Syntax

USERID

Examples

SHOW USERID 

Data Type

BOOLEAN

Syntax

USETRIGGERS = {NO|YES}

Parameters

YES

(Default) Trigger programs execute.

NO

Trigger programs do not execute.

Examples

SHOW USETRIGGERS
yes
 

USETRIGGERS = NO
SHOW USETRIGGERS
no

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.

Important:

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

SHOW boolvar

si

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

DATEFORMAT = '<Mtextl> <d>, <yyyy>'
SHOW MAKEDATE(96 9 13)

September 13, 1996

YRABSTART = 2000
SHOW MAKEDATE(96 9 13)

September 13, 2096

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

ZEROROW = YES
LIMIT line TO 'Revenue'
LIMIT month TO 'Nov95' TO 'Feb96'
REPORT WIDTH 8 DOWN division ACROSS month: worstcase

LINE: REVENUE
         -----------------WORSTCASE-----------------
         -------------------MONTH-------------------
DIVISION   Nov95      Dec95      Jan96      Feb96
-------- ---------- ---------- ---------- ----------
Camping        0.00       0.00  45,500.00  47,400.00
Sporting       0.00       0.00  29,200.00  28,400.00
Clothing       0.00       0.00  15,200.00  14,900.00

REPORT DOWN month ACROSS division: worstcase

LINE: REVENUE
               -----------WORSTCASE------------
               ------------DIVISION------------
MONTH           Camping    Sporting   Clothing
-------------- ---------- ---------- ----------
Jan96           45,500.00  29,200.00  15,200.00
Feb96           47,400.00  28,400.00  14,900.00

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, "Showing Very Small Numbers".

Examples

testvar = 0.00
ROW testvar

          0.00

ZSPELL = 'NONE'
ROW testvar

          NONE

ZSPELL = 'OFF'
testvar = 0.004
ROW DECIMAL 3 testvar

          0.004

ZSPELL = 'NONE'
ROW DECIMAL 2 testvar

          NONE

ROW DECIMAL 3 testvar

          0.004