Syntax

HEADING [attribs] {expression1|SKIP}, [attribs] {expressionN|SKIP}

Parameters

attribs

The attributes that specify the format for each column. (See the ROW command for a list and detailed explanation of the available attributes.)

expression

The text to be used as a column heading. To use literal text for a column heading, enclose the text in single quotes. (See the ROW command for more information on using expressions, attributes, and ACROSS groups to produce columns.)

SKIP

Used instead of an expression to indicate that the column is to be left blank.

Usage Notes

The notes for the ROW command also apply to the HEADING command (except for the note on row and column arithmetic in ROW).

Creating Titles Using Heading

To create a title or subtitle in a report, use HEADING to produce a single "column" with a width equal to the setting of the LSIZE option. You can then center your text within this "column" to produce a centered title.

Maximum Heading Width

The maximum width of any line in a report, including a heading line, is 4,000 characters.

Examples

Syntax

HIDE prog-name seed-exp

Parameters

prog-name

The name of the program whose text you want to hide. Do not enclose the program name in quotes.

seed-exp

A single-line text expression to be used as a seed value when encoding of the program text. Do not specify NA for this value.

Keep a record of this seed expression, so that you can use it later with an UNHIDE statement. The seed expression you specify in the UNHIDE statement must be byte-for-byte the same value as you used in this HIDE statement. Also, the seed expression is case-sensitive, so record uppercase and lowercase characters carefully.

Usage Notes

Exporting and Importing with the Seed

When you export and import a hidden program, the text remains hidden in the analytic workspace in which it is imported. It retains the same seed expression for use with the UNHIDE command.

Examples

Syntax

HIERDEPTH parent-rel [(qdr-list)] INTO depth-rel [INHIERARCHY inh-valueset ]

Parameters

parent-rel

A child-parent self-relation for the hierarchical dimension. See "Parentrel Relation" for more information.

qdr-list

A list of QDRs that limits the values of parentrel. Specify the QDRs as described in "Syntax of a Qualified Data Reference". When you do not specify a value for qdrlist, HIERHEIGHT uses the values of parentrel that are in current status.

depth-rel

A previously -defined relation that the HIERDEPTH command populates when it executes. This relation can have multiple dimensions; however, it must be dimensioned by the dimensions of parentrel and one other NUMBER dimension that represents the depth of the hierarchical dimension.

For example, assuming that parent-rel has two dimensions, dim1 and dim2, the following lines of code define a NUMBER dimension named numdim with 3 values and, then, define a depth relation named depthrel which is dimensioned by dim1 and dim2 that has as its values the values of numdim.

DEFINE numdim dimension NUMBER(3)
DEFINE depthrel RELATION numdim <dim1 dim2>

inh-valueset

The name of a valueset object whose values are the hierarchical dimension values to be considered when creating grouping ids. Values not included in the valueset are ignored. See "Inhier Valueset or Variable" for more information.

Examples

Syntax

HIERHEIGHT parentrelation [(qdrlist)] INTO{familyrel-        [USING level-relation | A | D] [INHIERARCHY { inh-variable | inh-valueset}]

Parameters

parentrel

A child-parent self-relation for the hierarchical dimension. See "Parentrel Relation" for more information.

qdrlist

A list of QDRs that limits the values of parentrel. Specify the QDRs as described in "Syntax of a Qualified Data Reference". When you do not specify a value for qdrlist, HIERHEIGHT uses the values of parentrel that are in current status.

familyrel

A previously -defined relation that the HIERHEIGHT command populates when it executes. This relation can have multiple dimensions; however, it must be dimensioned by the dimensions of parentrel and one other dimension that represents the levels of the hierarchical dimension. The actual construct of the dimension that represents the levels of the hierarchical dimension varies depending on whether or not the HIERHEIGHT statement includes the USING phrase:

• When the statement includes the USING phrase, the dimension that represents the levels of the hierarchical dimension is a dimension that contains the names of the levels.

• When the statement does not include the USING phrase, the dimension that represents the levels of the hierarchical dimension is an INTEGER dimension that has as values the depth of the level.

When familyrel is populated before a HIERHEIGHT statement executes, the HIERHEIGHT statement depopulates it before computing new values.

USING levelrel

Specifies that the relation is populated using the specified levelrel. See "Levelrel Relation" for more information.

A

Specifies that the relation is populated in ascending order.

D

(Default) Specifies that the relation is populated in descending order.

inh-variable

A BOOLEAN variable that is dimensioned by the hierarchical dimension and, when the hierarchical dimension is a multi-hierarchical dimension, by a dimension that is the names of the hierarchies. The values of the variable are TRUE when the dimension value is in a hierarchy and FALSE when it is not.

inh-valueset

The name of a valueset object whose values are the hierarchical dimension values to be considered when creating grouping ids. Values not included in the valueset are ignored. See "Inhier Valueset or Variable" for more information.

Usage Notes

HIERHEIGHT with the OLAP_TABLE Function

Typically, you use a HIERHEIGHT statement when you are preparing an analytic workspace for access using the OLAP_TABLE function.

Examples

Syntax

IF boolean-expression    THEN statement1    [ELSE statement2]

Parameters

boolean-expression

Any valid Boolean expression that returns either TRUE or FALSE.

THEN statement1

Oracle OLAP executes the statement1 argument when the Boolean expression is TRUE. The statement1 must be on the same line as THEN.

ELSE statement2

Oracle OLAP executes the statement2 argument when the Boolean expression is FALSE. The statement2 must be on the same line as ELSE. When you omit the ELSE phrase, execution continues with the statement after the whole IF...THEN... statement in the program.

Usage Notes

IF with DO

You can use an IF statement for conditional execution of two or more statements by following the THEN or ELSE (or both) keywords with a DO ... DOEND sequence. See Example 10-5.

Single or Multiple Lines

When IF is used as an expression, the THEN and ELSE keywords must be on the same line as IF. When IF is used as a command, THEN and ELSE must be on separate lines.

Examples

Syntax

INFILE {file-id|EOF} [NOW] [NLS_CHARSET charset-exp] 

Parameters

file-id

The name of a file from which to read input. File-id is a text expression that represents the name of the file. The name must be in a standard format for a file identifier.

The input file must contain only OLAP DML statements, along with appropriate responses to any prompts generated by the statements. Each statement or response must appear on a separate line in the file.

EOF

Terminates the reading of input from the current file and causes Oracle OLAP to resume reading input from the location from which the INFILE statement was executed. Use of INFILE EOF is optional. See "About the Input File" and "INFILE with Both NOW and EOF".

NOW

Indicates that Oracle OLAP should open the input file specified in the INFILE and read its statements immediately upon encountering the INFILE instead of waiting until the program containing the INFILE is finished which has the effect of nesting the input file's statements within the program. See "INFILE with Both NOW and EOF". This argument must be specified after file-id.

NLS_CHARSET charset-exp

Specifies the character set that Oracle OLAP uses when reading data from the file specified by file-id which allows Oracle OLAP to convert the data accurately into the current character set, as identified by the NLS_LANG option. This argument must be specified after file-id. When this argument is omitted, then Oracle OLAP handles the data in the file as having the database character set, which is recorded in the NLS_LANG option.

Usage Notes

About the Input File

When the end of the input file is reached, Oracle OLAP resumes reading input from the location from which the INFILE statement was executed (which could be another input file). You do not have to end the input file with the statement INFILE EOF.

INFILE ignores trailing blanks at the end of a line, or between the last text on a line and a continuation mark. INFILE also ignores blank lines.

When you use the NOW keyword and the input file ends with a continued statement, the statement is ignored. For example, if the file ends with "show - ," Oracle OLAP ignores a SHOW statement.

Using INFILE in a Program

When you include an INFILE statement without the NOW keyword in a program, the INFILE statement is not executed until the program has finished executing. In a nested program, it is not executed until all the programs involved have finished executing. Also, when several INFILE commands have been executed by a program, the input files are read in the opposite order from which they were specified.

For example, assume that program.a calls program.b which calls program.c, and each program contains two INFILE commands, one before and one after the call to the next program (as illustrated in the following code). In this case, the order of execution is: a2, b2, c2, c1, b1, a1.

program.a
   INFILE a1
   "
        program.b
           INFILE b1
           "
                program.c
                   INFILE c1
                   INFILE c2
           "
           INFILE b2
   "
   INFILE a2

When you include an INFILE statement in a program with the NOW keyword, the INFILE statement executes immediately. However, INFILE with the NOW keyword requires more space than usual on the program stack. To conserve stack space, use the NOW keyword only when it is necessary.

INFILE with NOW Outside of Programs

The NOW keyword is intended for use within programs, but you can use it at any time. When you use it when the input file would not ordinarily be deferred, the NOW keyword has no visible effect. However, because NOW requires extra stack space, do not use it in these situations.

INFILE with Both NOW and EOF

When you use both the NOW and EOF keywords, the NOW keyword is ignored.

Displaying Infiled Statements and Responses

When you want the statements from a disk file to be copied to a debugging file as they are executed, see the DBGOUTFILE command.

Examples

Syntax

LD [text]

Parameters

text

The text of the description you want to assign to the definition. When text is omitted, any existing description for the current definition is deleted.

You can create a multiline description by using a hyphen as a continuation character. However, you cannot create a description with an initial blank line with an LD statement.

Examples

Syntax

LIMIT {dimension | valueset } [concat-component] limit-type [limit-clause] [IFNONE label]

where limit-type is one of the following keywords that specify how Oracle OLAP should modify the current status list:

• TO
• ADD
• INSERT [FIRST|LAST|BEFORE position|AFTER position]
• KEE
• REMOVE
• KEEP REORDER
• COMPLEMENT
• SORT [NAFIRST]

Parameters

dimension

The name of the dimension or dimension surrogate for which you are setting status.

valueset

The name of the valueset for which you are assigning values.

concat-component

Specifies the name of the component of the concat dimension whose values are used to determine the limit. When you specify a value for concat-component, the limit sets the status of the specified concat dimension using the values of dimension which is a component of the concat dimension. This limit-clause applies only when dimension is a concat dimension. The status of a concat dimension and of its component dimensions are not shared. Changing the status of a component dimension after you have used that dimension as the limit-clause in setting the status of a concat dimension does not change the status of the concat dimension.

TO

Replaces the status of a dimension or valueset with the values specified by the limit-clause arguments. The TO keyword selects values from the default status of a dimension in the same order as they appear in the LIMIT statement or in the order implied by the valuelist argument. When you use arguments that imply ordering, the ordering of the values is based on their positions in the default status.

ADD

Expands the status of a dimension or valueset by adding the values specified by the limit-clause arguments that are not already in status. The ADD keywords selects values from the default status of a dimension in the same order as they appear in the LIMIT statement or in the order implied by the valuelist argument. When you use arguments that imply ordering, the ordering of the values is based on their positions in the default status. ADD adds unique dimension values in the specified order after the current status list or valueset list.

INSERT

Expands the status of a dimension or valueset by inserting the values specified by the limit-clause arguments in a specified position in the current status. The INSERT keyword selects values from the default status of a dimension in the same order as they appear in the LIMIT statement or in the order implied by the valuelist argument.

When you use arguments that imply ordering (for example,value1 TO value2), the ordering of the values is based on their positions in the default status. INSERT adds values to a specified position in the current status. When an added value is already in status, it is removed from its position in the current status and added in the order in which it appears in the valuelist, preserving the order of the added values.

FIRST

Inserts the new values before the first value in status.

LAST

Inserts new values after the last value in status.

BEFORE

AFTER

Specifies whether new values Oracle OLAP inserts new values before or after position in the current status.

position

A dimension value in the current status, a character expression whose value is a dimension value in the current status, or an INTEGER expression whose value represents the position of a dimension value in the default status.

KEEP

Reduces the status of a dimension or valueset by keeping only the values specified by the limit-clause arguments. Oracle OLAP performs the selection based on the current dimension status. KEEP preserves the current order of values among the values that remain in the status.

KEEP REORDER

Like a simple KEEP, KEEP REORDER reduces the status of a dimension or valueset by keeping only the values specified by the limit-clause arguments. Oracle OLAP performs the selection based on the current dimension status. However, KEEP REORDER orders the result in the order of the selection arguments (that is, the limit-clause arguments) rather than by the current status order.

REMOVE

Reduces the status of a dimension or a valueset by removing the values specified by the limit-clause arguments. Oracle OLAP performs the selection based on the current dimension status. KEEP preserves the current order of values among the values that remain in the status.

COMPLEMENT

Replaces the status of a dimension or valueset with the values that are not specified by the limit-clause arguments. When you do not specify any arguments after COMPLEMENT, status is replaced by all values not now in status. Oracle OLAP performs the selection based on the current dimension status. COMPLEMENT leaves dimension values that remain in their default order. (Abbreviated COMP)

SORT

Sorts the values of a dimension or valueset according to the limit-clause arguments. LIMIT creates a temporary list of values based on the limit-clause arguments, and uses this list to sort the current status list. Any values not present in the temporary list are moved to the end of the current status list.

NAFIRST

Specifies that NA values are placed first in the sort list rather than last.

limit-clause

Specifies the values to use for the limit. The syntax is quite complex and, consequently, has been divided into the following topics:

• LIMIT (using values) command
• LIMIT using LEVELREL command
• LIMIT (using related dimension) command
• LIMIT (using parent relation)
• LIMIT NOCONVERT command
• LIMIT command (using POSLIST)

IFNONE

(For use only within an OLAP DML program) Specifies that program execution should branch to label when the requested status has null status or is based on a related dimension that turns out to have null status (that is, to have no values). In either case, the null status is not put into effect when program execution branches. Instead, the original status, before the LIMIT statement was executed, is retained even when OKNULLSTATUS is YES. Within an OLAP DML program, you cannot use both IFNONE and NULL in the same statement.

label

The name of a label elsewhere in the program constructed following the "Guidelines for Constructing a Label". Execution of the program branches to the line directly following the specified label.

Note that label, as specified in IFNONE, must not be followed by a colon. However, the actual label elsewhere in the program must end with a colon.

Usage Notes

Specifying a Value of a Concat Dimension

To specify a value of a nonunique concat dimension, use the following syntax.

<base-dimension: value>.

Default Status List

When you first attach an analytic workspace, the current status list of each dimension consists of all of the values of the dimension that have read permission, in the order in which the values are stored. This list of values is called the default status list for the dimension.

Unique Values

LIMIT selects only unique values of a dimension. When a value appears more than once in a LIMIT statement, it is placed in status in the order of its first appearance. For example, the following lines.

LIMIT time TO 'Jan97', 'Feb97', 'Jan97'
STATUS time

produce this output.

The current status of TIME is:
JAN97, FEB97 

Nonexistent Values

Oracle OLAP does not signal an error when you try to set the status of a dimension or valueset that has no values, unless you explicitly list values that do not exist. For example, assume that you have not added any values to a newly defined dimension WEEK. In this case, the statement LIMIT week TO FIRST 10 does not cause an error. However, LIMIT week TO 'Pete' causes an error because Pete is not a value. Similarly, LIMIT week TO 20 causes an error because week does not have a value at position 20.

Setting the Status of a Dimension or Valueset to Null

Oracle OLAP allows the status of a dimension or valueset to be set to null (empty status) only when you have explicitly specified that you want null status to be permitted. You can give this permission in either of two ways:

• Set the OKNULLSTATUS option to YES. This specification indicates that null status should be allowed whenever it occurs (unless the IFNONE argument is present in a LIMIT statement).

• Use the NULL keyword in a LIMIT statement to set the status of a particular dimension or valueset to null. You can do this by specifying TO NULL or KEEP NULL. This specification indicates that null status should be allowed for this LIMIT statement only.

When you have not used either of these two methods to give permission for null status and you execute a LIMIT statement that would result in null status, Oracle OLAP does not change the status to null when it executes the statement. Instead, Oracle OLAP leaves the status as it was before the statement was issued and either signals an error (when IFNONE is not present) or branches to the IFNONE label (when IFNONE is present).

An IFNONE argument indicates that you do not want program execution to take its normal course when a dimension's status were to be set to null. Therefore, when IFNONE is present, Oracle OLAP branches to the IFNONE label and does not set the status to null, even when OKNULLSTATUS is YES. When the NULL keyword is present with IFNONE, Oracle OLAP signals the inconsistency with an error.

IFNONE requires the use of unstructured programming techniques. Oracle OLAP now provides alternative structured techniques, so the use of IFNONE is discouraged. IFNONE has been retained for compatibility with previous versions of Oracle OLAP.

Limiting a Conjoint

To limit a conjoint dimension to a value list, you can use the following constructions:

• Specify the actual values, surrounding each combination with angle brackets

  LIMIT proddist TO '<Tents, Boston>' -
     '<Footwear, Denver>'

• Use a variable name for the values, surrounding the combination with angle brackets.

  prodname = 'Canoes'
  distname = 'Seattle'
  LIMIT proddist To <prodname, distname>
  

• Create a multiline list, where each line is a combination surrounded by angle brackets.

  namelist = '<Tents Boston>\n<Footwear, -
     Denver>\n <Canoes, Seattle>'
  LIMIT proddist TO namelist

• Use the implicit relation between a conjoint dimension and its base dimension to limit the conjoint dimension. For example, use the following statement to limit PRODDIST to all conjoint values having "Canoes" as one of its base values.

  LIMIT proddist TO product 'Canoes'

  Note:

  You can use logical position numbers for base dimension values in a conjoint dimension. "Example 7-125" illustrates using logical position numbers

For an example of how you can limit a conjoint dimension that has a concat base dimension, see Example 10-25.

Limiting a Concat

You can define a concat dimension using simple dimensions, conjoint dimensions, and other concat dimensions as the base dimensions of the concat. The syntax for limiting a concat dimension to one of its values is the following.

LIMIT concatdim TO <base-dim: value>

For example, the concat dimension reg.dist.ccdim has the simple dimensions region and district as its base dimensions. The following statement sets the status of reg.dist.ccdim to two of its values, region: East and district: Atlanta.

LIMIT reg.dist.ccdim TO <region: 'East'> <district: 'Atlanta'>

For other methods of setting the status of a concat dimension, see Example 10-16.

Alternative to Branching Using an IFNONE Label

As an alternative to branching to an IFNONE label, you can also handle null status for a dimension with the OKNULLSTATUS option. When you set OKNULLSTATUS to YES, then you are allowed to set the status of a dimension to null. You can then check for null status and execute appropriate commands with an IF...THEN...ELSE command, or you can handle null status as a case in a SWITCH command.

OKNULLSTATUS = YES
LIMIT month TO sales GT salesnum
IF STATLEN(month) LT 1
   THEN GOTO showerr

Examples

Syntax

LIMIT BASEDIMS {<dimlist>}TO object [ [KEEP] limit-clause ]

Parameters

dimlist

A space-delimited list of objects that identifies the base dimensions for which you want Oracle OLAP to change status and the valuesets for which you want Oracle OLAP to add values. Within dimlist, you can specify the following values:

• base-dimension-name
• The name of a base dimension of object.
• relation-name
• The name of a relation. When you specify a relation, Oracle OLAP sets the status of the related dimension.
• valueset-name
• The name of a valueset for a base dimension; or a LIMIT function. When you specify the name of a valueset, Oracle OLAP assigns a single value to the specified valueset.

The default value of dimlist is the completely expanded list of the base dimensions of object.

object

The name of a composite, a conjoint dimension, a concat dimension, or a partition template object.

KEEP

Specifies that Oracle OLAP performs the selection based on the current dimension status of the base dimensions of the object. KEEP preserves the current order of values among the values that remain in the status.

limit-clause

Specifies how Oracle OLAP should select values from object to modify the current status lists of the base dimensions.

The syntax of limit-clause is the same syntax as any of the non-positional limit-clause arguments in the various forms of the LIMIT command (that is, the syntax of the LIMIT command after the limit-type argument such as "TO"). For the syntax of these arguments, see LIMIT (using values) command, LIMIT using LEVELREL command, LIMIT (using parent relation), LIMIT (using related dimension) command, LIMIT NOCONVERT command, and LIMIT command (using POSLIST).

When this optional argument is not specified, Oracle OLAP sets the status of the base dimensions to the current status of the dimensions.

Syntax

LISTBY dimensions

Parameters

dimensions

A list of one or more dimensions or composites, separated by spaces. When you list multiple dimensions, all the dimensions must be in the same workspace. LISTBY returns a list of objects that are dimensioned by all the dimensions you specify. When you specify an unnamed composite, use the following format:

LISTBY SPARSE dim1 dim2 ...

Usage Notes

Composites and Conjoint Dimensions

The report produced by LISTBY includes any named or unnamed composite, or conjoint dimension, whose base dimension list includes the dimensions you specify.

The report also includes any object whose dimension list includes a named or unnamed composite that in turn has the specified dimensions as base dimensions.

Examples

Syntax

LISTFILES

Examples

Syntax

LISTNAMES [AW workspace|'*'] [objtype-list|ALL] -      [SORTED|UNSORTED] [LIKE 'pattern']

Parameters

AW workspace

AW '*'

Specifies the name of an attached workspace whose objects you want to list. When you omit the workspace name, LISTNAMES lists the objects in the current workspace. When you use the '*' (asterisk) argument instead of an analytic workspace name, LISTNAMES produces a separate report for each attached workspace.

objtype-list

ALL

Specifies one or more of the following types of objects whose names you want to list: AGGMAP, COMPOSITE, DIMENSION, FORMULA, MODEL, OPTION, PROGRAM, RELATION, VALUESET, VARIABLE, and WORKSHEET. You can include a trailing "S" on any object type, for example, DIMENSIONS. You can list these object types in any order. ALL (the default) specifies that the names of objects of all these types should be listed.

SORTED

UNSORTED

SORTED (the default, abbreviated SORT) specifies that the object names should be sorted alphabetically. UNSORTED (abbreviated UNSORT) specifies that the object names should not be sorted alphabetically.

LIKE 'pattern'

Compares the names of the definitions in an analytic workspace to the text pattern you specify and lists the names that match. A definition name is like a text pattern when corresponding characters match. Besides literal matching, LIKE lets you use wildcard characters to match multiple characters in a string. An underscore (_) character in a pattern matches any single character. A percent (%) character in a pattern matches zero or more characters.

Usage Notes

NAME Dimension

NAME is a special dimension that is used by Oracle OLAP to organize the list of objects in an analytic workspace. Its values are the names of the objects defined in the current workspace. (

You cannot explicitly change the values of the NAME dimension. Instead the values are changed as DEFINE, DELETE, MOVE, or RENAME statements execute. Also, you cannot use a qualified object name to specify the NAME dimension of an analytic workspace that is not the current workspace.

Examples

Syntax

LOAD object. . .

Parameters

object. . .

The name of a program, formula, or model.

Usage Notes

Definitions Loaded on First Use

All of the objects in an analytic workspace (except for programs, formulas, and models) are loaded into memory when the analytic workspace is attached. Programs, models, and formulas are loaded into memory when first used or when requested using the LOAD command. The time required for loading is small but perceptible, and an application builder fine-tuning a system might want to preload objects in a startup program so that the application runs up to speed from the beginning of a session.

Effect of Loading Many Objects

Loading too many objects into memory can cause Oracle OLAP to run out of memory when it processes a long statement. It is best to use LOAD sparingly, choosing the objects for maximum effect.

LOAD Does Not Compile Programs

When a program is not compiled, LOAD does not automatically compile it. For best performance, always compile the program and save the compiled code by updating your workspace. Then when you load the program in another session (for example, with an AUTOGO program), the program is ready to run. See the COMPILE command for more information about compilation.

Examples

Syntax

LOG EOF | TRACEFILE | SAVE | {[APPEND] file-name}

Parameters

EOF

Stops recording of the session and closes any opened log record file.

TRACEFILE

Specifies that the session information should be directed to the Oracle trace file, which is identified by the TRACEFILEUNIT option.

SAVE

Forces Oracle OLAP to update the log file. Lines of input and output are not always written to disk as they are generated. Instead, the lines are stored temporarily then written to disk periodically. LOG SAVE effectively issues the LOG EOF and LOG APPEND file-name commands which ensures that all appropriate lines are written to disk by closing the log file and reopening it. Additional lines of input and output are appended to the file.

APPEND

Specifies that the record of your session should be added to the end of an existing disk file. When you omit this argument, the new output replaces the current contents of the file.

file-name

A text expression that is the name of the file to which session information should be written. Unless the file is in the current directory, you must include the name of the directory object in the name of the file.

Note:

Directory objects are defined in the database, and they control access to directories and file in those directories. You can use a CDA statement to identify and specify a current directory object. Contact your Oracle DBA for access rights to a directory object where your database user name can read and write files.

Usage Notes

Automatic Closing of a Log File

When you use LOG file-name to start recording in a disk file, LOG closes any log record file that is currently open. LOG issues this close even when the new file is not actually opened (as when you specify an invalid file name in the LOG statement).

Examples

Syntax

MAINTAIN object {ADD|DELETE|RENAME|MOVE|MERGE} args

The keywords that you can use with the MAINTAIN command varies by object:

• MAINTAIN dimension {ADD|DELETE|RENAME|MOVE|MERGE} args

  The keyword that you can use varies by the type of dimension that you want to maintain:

  • With a non-concat dimension, you can use the ADD, DELETE, RENAME, MOVE, or MERGE keywords to add, delete, rename, move, or merge non-concat dimension values. You can also use the ADD keyword to add temporary calculated members to a dimension.

  • With a concat dimension, you can only use the MOVE keyword to move concat dimension values.

• MAINTAIN composite {ADD|DELETE|MERGE} args

• MAINTAIN partition-template {ADD|DELETE|MOVE} args

The specific syntax varies by keyword. Consequently, there are separate topics for each keyword of the MAINTAIN command:

• MAINTAIN ADD
• MAINTAIN DELETE
• MAINTAIN MERGE
• MAINTAIN MOVE
• MAINTAIN RENAME

For information that applies to the MAINTAIN command in general, see the Usage Notes in this topic.

Usage Notes

Triggering Program Execution When MAINTAIN Executes

Using the TRIGGER command, you can make the execution of a MAINTAIN statement an event that automatically executes an OLAP DML program. See "Trigger Programs" for more information.

Automatic Status Reset

When you use the ADD, DELETE, MERGE, or MOVE keyword to maintain a dimension or composite whose status is not currently ALL, the MAINTAIN command automatically resets status to ALL before performing the maintenance function. However, when you use the RENAME keyword to maintain a dimension whose status is not currently ALL, the MAINTAIN command does not change the status of the dimension.

Maintain Permission

You cannot perform maintenance on a dimension when a PERMIT MAINTAIN statement denies maintain permission for the dimension. Maintain permission is implicitly denied whenever read permission is restricted for a dimension, even when you specify maintain permission for the dimension. (See the PERMIT command.)

Maintaining Dimensions in Multiwriter Analytic Workspaces

Keep the following points in mind when maintaining dimensions in an analytic workspace that is attached in multiwriter mode:

• You cannot update a variable when any of its dimensions have been acquired and modified.

• Reverting a dimension after adding dimension values is not recommended because it can result in suboptimal space allocation for variables dimensioned by the dimension.

• When an acquired variable is dimensioned by an acquired dimension that has been maintained, you cannot update the variable until the dimension is updated or released.

• You do not have to acquire composites in order for them to be maintained, Oracle OLAP automatically performs concurrent dimension maintenance for the composite dimensions.

Also, before you can maintain dimensions in an analytic workspace that is attached in multiwriter mode, you must first acquire the dimension using an ACQUIRE statement.

For example, assume that user A and user B both have to perform what-if computations on both actuals and budget. After performing the what-if computations, user A needs to modify actuals and B needs to modify budget. Finally, both user A and user B have to add a new time dimension value and add data corresponding to that new dimension value to actuals or budget.

User A issues the following OLAP DML statements.

AW ATTACH myworkspace MULTI
...make modifications 
ACQUIRE actuals
...make more modifications
ACQUIRE time
MAINTAIN time ADD 'Y2002'
actuals (time 'Y2002', ...) = ...
UPDATE MULTI actuals, time
COMMIT
RELEASE actuals, time
AW DETACH myworkspace

User B issues the following OLAP DML statements.

AW ATTACH myworkspace MULTI
...make modifications 
ACQUIRE budget
...make more modifications
ACQUIRE time--> failed
ACQUIRE RESYNC time WAIT
MAINTAIN time ADD 'Y2003'
budget (time 'Y2003', ...) = ...
UPDATE MULTI budget, time
COMMIT
RELEASE budget, time
AW DETACH myworkspace

MAINTAIN and Dimension Surrogates

You cannot use a MAINTAIN statement on a dimension surrogate. You can only use MAINTAIN to add values to or delete them from a dimension. However, when you add or delete a dimension value, then Oracle OLAP adds or removes a position from surrogates of that dimension. When you add a position to a dimension, the corresponding position in a surrogate for that dimension receives an NA value.

Maintaining a Concat Dimension

A concat dimension contains the values of its component dimensions. You do not directly add, merge, or delete the values of a concat dimension using MAINTAIN statements. Instead, when you add, merge, or delete values from a component dimension of the concat, Oracle OLAP automatically adds or deletes the values from the concat dimension. You can use the MOVE keyword of the MAINTAIN command to change the order of the values of a concat dimension.

Syntax

MODEL specification

Parameters

specification

A multiline text expression that contains one or more of the following OLAP DML statements:

• SET (=) command     
• DIMENSION (in models) statement
• INCLUDE statement

The maximum number of lines you can have in a model is 4,000. Each statement is a line of the multiline text expression. When coding an ALLOCMAP statement at the command line level, separate statements with newline delimiters (\n), or use JOINLINES.

For a discussion of designing a model specification, see "Model Specification".

Usage Notes

Model Specification

The model specification consists of the following OLAP DML statements:

1. One of the following:

   • Exactly one INCLUDE statement that specifies the name of another model to include. See "Nesting Models" for more information.

   • One or more DIMENSION (in models) statements coded following the "Guidelines for Writing DIMENSION Statements in a Model".

     Note:

     When a model contains an INCLUDE statement, then it cannot contain any DIMENSION statements. However, the model referenced in the INCLUDE statement or the root model in a hierarchy must contain the DIMENSION statements needed by the parent model(s).

2. One or more SET commands or equations written following the "Rules for Equations in Models".

   See Also:

   "Dimension Status and Model Equations" for information on how Oracle OLAP processes equations in a model

3. A final END statement that indicates the end of the model specification. (Omit when coding the specification in an Edit window of the OLAP Worksheet.)

The maximum number of lines you can have in a model is 32,000.

MODEL Statement in an Aggregation Specification

Within an aggmap, you can use a special MODEL statement to execute a predefined model. (See the MODEL (in an aggregation) statement under the AGGMAP command for more information.

Methods of Calculating Data Within a Variable

Both models and aggmap objects calculate data values within a variable based on relationships among dimension members. When a parent-child relationship exists among dimension members (that is, the dimension has a hierarchical structure) and all aggregate values can be calculated using the same method, then you can use a RELATION statement within an aggregation specification to calculate the values. However, when the dimension is not hierarchical and different equations are needed to calculate the values, then you must define a model. You can use a MODEL (in an aggregation) to execute the MODEL within an aggregation specification or you can run a model at the command line using the syntax shown in "Running a Model".

Deleting a Model Specification

You can remove the specification of a model without deleting the model definition. Consider the model with a CONSIDER statement. Then issue a MODEL statement and enter the word END as the model specification.

Examples

Syntax

MODEL.COMPRPT

Examples

Syntax

MODEL.DEPRT

Examples

Syntax

MODEL.XEQRPT

Usage Notes

Running MODEL.XEQRPT

Before you can run the MODEL.XEQRPT program, you must

1. Set the MODERROR option to STOP or CONTINUE.

2. Execute the model.

When the model halts because of an error, run the MODEL.XEQRPT program.

Effect of MODERROR on MODEL.XEQRPT

The results returned by MODEL.XEQRPT vary depending on the setting of the MODERROR option:

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

• 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, a report on the solution for the final values of the additional dimensions is produced.

  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

Syntax

MONITOR ON [programs]  | OFF | INIT | FILE [[APPEND] file-name] | RESET

where programs is one of the following:

• ALL [awlist]
• program-name

Parameters

ON

Starts looking for the specified programs to be run so that Oracle OLAP can gather line-by-line timing data in a monitor list. (It continues the current monitoring process without interruption when monitoring is already on, or resumes with a gap when monitoring was off.)

When you do not specify a value for programs, the default is the program or programs specified in the last executed MONITOR ON statement. When there was no such statement in your current session, no data is collected and no error is produced.

ALL

Specifies that all of the programs in one or more analytic workspaces are monitored.

awlist

The name of one or more analytic workspaces (optionally separated by commas) whose programs you want monitored. When you omit this argument, Oracle OLAP monitors all of the programs in all of the attached analytic workspaces

program-name

The name of a specific program that you want monitored.

OFF

Stops monitoring programs and freezes any timing data currently in the monitor list which allows you send the list to the current outfile or to a text file either immediately, or later in your session.

RESET

(Useful only when monitoring is on.) Retains information about the programs that ares currently specified for monitoring and the Oracle OLAP memory that is allocated for the current monitor list, but discards any timing data currently in the list. In addition, RESET causes MONITOR to again begin waiting for you to run the same programs. When you do, MONITOR automatically gathers new timing data into a new monitor list for the same programs in the same memory allocation as before.

INIT

(Useful only when monitoring is on.) Initializes the monitoring environment. Initialization consists of discarding the program names and the timing data associated with the current monitor list, and releasing the Oracle OLAP memory previously used for that list so it can be used for other purposes or for collecting new data in a new monitor list.

FILE

Specifies that the timing data that is currently in the monitor list is sent to a file.

APPEND

Specifies that the timing data is appended to an existing file. When you omit this argument, the new output replaces the current contents of the file.

file-name

A text expression that is the name of the file to receive the output. Unless the file is in the current directory, you must include the name of the directory object in the name of the file. When file-name is specified, the data is sent to the named text file. FILE has no effect on the monitor list, so you can send the same list repeatedly to different destinations. When file-name is omitted, Oracle OLAP sends the timing data that is currently in the monitor list to the current outfile.

Note:

Directory objects are defined in the database, and they control access to directories and file in those directories. You can use a CDA statement to identify and specify a current directory object. Contact your Oracle DBA for access rights to a directory object where your database user name can read and write files.

Usage Notes

Monitor List

Each entry (that is, line) in the monitor list focuses on the execution of a single program line, regardless of how many times it is executed. Each entry is divided into the following four sections:

• Cumulative total time of all executions in milliseconds rounded to the nearest 10 millisecond, in columns 1 through 11

• Number of times executed, in columns 12 through 18

• Line number, in columns 19 through 23

• Text of the line, in column 24 and subsequent columns

Here is a sample of MONITOR output with column numbers included for reference.

12345678901234567890123456789012345678901234567890
 
60              1    1 push name 
30              1    2 trap on GETOUT noprint 
51              1    3 limit name to obj(type) eq 'DIMENSION'
0               1    4 for name 
0               0    5    do 
450             6    6    limit &name to ALL ifnone BYPASS 
0               0    7 BYPASS: 
0               0    8    doend 
0               0    9 GETOUT: 
0               1   10 pop name 

The following is the full description of the program used for the preceding output. Note that in the output, the line with the LIMIT command is truncated because it is too long to fit.

DEFINE allstat PROGRAM
LD Program to set the status of all dimensions in the analytic workspace to ALL
PROGRAM
PUSH NAME
TRAP ON getout NOPRINT
LIMIT NAME TO OBJ(TYPE) EQ 'Dimension' IFNONE getout
FOR NAME
   DO
   LIMIT &NAME TO ALL IFNONE bypass
bypass:
   DOEND
getout:
POP NAME
END

Attaching, Detaching, and Reattaching Analytic Workspaces

When Oracle OLAP executes a program in an analytic workspace that has been attached, detached, a new block of data is collected.

Truncated Statement Lines

When a program line is too long, the MONITOR output truncates it. Continuation lines do not appear in the output.

Producing a Report

When you want to produce an Oracle OLAP report from the timing data in the MONITOR file, you can write a program that uses a FILEREAD statement to read the data into an Oracle OLAP variable, and then use Oracle OLAP reporting capabilities to produce a report. You can use the TRACKREPORT program as a model. However, keep in mind that the TRACKREPORT program was written to produce a report on TRACKPRG output, not MONITOR output.

Bracketing Lines

When you just want to time a particular line or group of lines in a program, you can insert MONITOR ON and MONITOR OFF statements in the program to bracket just the line or lines in which you are interested.

Using MONITOR for a Nested Programs

When you do not want to run a nested program by itself, you can specify its name in a MONITOR ON statement and then run the program that calls it. MONITOR gathers timing data only for the specified (nested) program. When the specified program is called more than once, for each program line, MONITOR accumulates the total seconds taken by all the times the line was run and provide the number of times it was run.

When you just want to time a particular execution of a nested program that is called more than once, you can insert MONITOR ON and MONITOR OFF statements in the calling program to bracket the single call in which you are interested.

Using MONITOR with Very Small Programs

You might not be able to reproduce the results exactly for very small programs. When the CPU interrupts processing to do other tasks, that time is a greater percentage of the total execution time.

Unit of Measure for MONITOR

The MONITOR and TRACKPRG commands use milliseconds as the unit for recording execution time. The execution time does not include time spent on I/O and time spent waiting for the next statement.

Examples

Syntax

MOVE name... {FIRST|LAST|{BEFORE|AFTER} name2} [AW workspace]

Parameters

name...

The names of one or more objects to move. You can specify the names individually, or use one of the following forms to specify a group of names:

• name TO name
• FIRST n 
• LAST n
• boolean-expression (dimensioned by NAME)

You can specify a qualified object name to indicate the attached workspace in which the object resides. As an alternative, you can use the AW argument to specify the workspace. Do not use both.

When you do not use a qualified object name or the AW argument to specify an analytic workspace, Oracle OLAP looks for the object in the current workspace.

FIRST

LAST

The logical position in the NAME dimension to which Oracle OLAP moves the objects specified by the name argument. Specifying FIRST moves the objects to the beginning of the NAME dimension. Specifying LAST (the default) moves the names to the end of the NAME dimension.

BEFORE name2

AFTER name2

The position before or after a particular object (name2) to which Oracle OLAP moves the objects specified by the name argument.

AW workspace

The name of an attached workspace in which you want to move the object. When you do not use a qualified object name or the AW argument to specify an analytic workspace, objects are moved in the current workspace.

Usage Notes

Alphabetizing Your Objects

You can arrange your workspace objects alphabetically with the following statements, which work on the NAME dimension.

SORT NAME A NAME
MOVE CHARLIST(NAME) FIRST

Examples

Syntax

OUTFILE {EOF | TRACEFILE | [APPEND] file-name [NOCACHE] [NLS_CHARSET charset-exp]}

Parameters

EOF

The current outfile is closed and output is redirected to the default outfile.

TRACEFILE

Specifies that the output should be directed to the Oracle trace file, which is identified by the TRACEFILEUNIT option.

APPEND

Specifies that the output should be added to the end of an existing disk file. When you omit this argument, the new output replaces the current contents of the file.

file-name

A text expression that is the name of the file to which output should be written. Unless the file is in the current directory, you must include the name of the directory object in the name of the file.

Note:

Directory objects are defined in the database, and they control access to directories and file in those directories. You can use a CDA statement to identify and specify a current directory object. Contact your Oracle DBA for access rights to a directory object where your database user name can read and write files.

NOCACHE

Specifies that Oracle OLAP should write lines to the outfile as they are generated. Without this keyword, Oracle OLAP reduces file I/O activity by saving text and writing it periodically to the file. The NOCACHE keyword slows performance significantly, but it ensures that every line is immediately recorded in the outfile. This argument must be specified after file-name

NLS_CHARSET charset-exp

Specifies the character set that Oracle OLAP uses when writing data to the file specified by file-name which allows Oracle OLAP to convert the data accurately into that character set. This argument must be specified after file-name. When this argument is omitted, then Oracle OLAP writes the data to the file in the database character set, which is recorded in the NLS_LANG option.

Usage Notes

Outfiling a PERMIT_READ or PERMIT_WRITE Program

The contents of a PERMIT_READ or a PERMIT_WRITE program is emptied when outfiled. To successfully copy the contents of these programs to and from analytic workspaces, rename them before using OUTFILE; and then, after using INFILE to copy them into an analytic workspace, name them back to PERMIT_READ or PERMIT_WRITE.

Current Outfile Identifier

As a first step, every OUTFILE statement closes the current outfile. When OUTFILE opens a new outfile on disk, it automatically assigns to it an arbitrary INTEGER as its file unit number. The current file unit number is held in the OUTFILEUNIT option.

Appending to an Outfile

When you send output to a file and then send output to a second file, the first file does not remain open. To resume sending output to the first file, you must execute another OUTFILE statement and include the APPEND file-name phrase.

Automatic Closing of Outfile

When you use OUTFILE file-name to direct output to a disk file, OUTFILE closes any outfile currently open. OUTFILE issues a close even when the new file is not actually opened (as when you specify an invalid file-name in an OUTFILE statement).

Paging Options and Redirected Output

The paging options control the organization of text output in pages. Examples are: BMARGIN, LINENUM, LINELEFT, PAGESIZE, PAGENUM, PAGEPRG, PAGING, TMARGIN, and LSIZE. The paging options have a separate value for each separate outfile. When you set a paging option to control output to a disk file, the new value remains in effect until you use an OUTFILE statement again to redirect output. At this point, the paging option returns to its default value. Therefore, when you want a paging option to have a particular value for a disk file, you generally have set it after you execute an OUTFILE statement.

Current and Default Outfiles

The current outfile is the destination for the output of statements, such as REPORT and DESCRIBE, that produce text. When you have not used an OUTFILE statement to send output to a file, Oracle OLAP uses your default outfile.

Examples

Syntax

PAGE [n]

Parameters

n

A positive INTEGER expression that indicates that a page break should occur only when there are fewer than n lines left on the current page. When the number of lines left equals or exceeds n, no page break occurs. See Example 10-70.

Usage Notes

Top of Page

No page break occurs when you are already at the top of a page when a PAGE statement is executed.

Producing the Header

The PAGE program signals that further output should be produced on a new page, but it does not produce a header on the new page unless there is further output. When there is further output, Oracle OLAP produces the heading that is defined by the current PAGEPRG program and then starts producing the output.

Examples

Syntax

PARSE text-expression

Parameters

text-expression

A text expression that contains one or more smaller expressions to be parsed. When you are processing program arguments, you can specify an ARGS function as the text expression that contains all the arguments for the program.

Usage Notes

Obtaining Information Produced by a PARSE Statement

See the INFO (PARSE) function for an explanation of how to obtain the information produced by a PARSE statement.

Examples

Syntax

PERMIT {READ|WRITE|MNT|PERMIT} [WHEN permission-condition...]

Parameters

READ

Depending on the permission conditions, grants or restricts permission to read all of the values of an object or only certain values in a dimension or dimensioned object.

Note that restricting read permission for a dimension also restricts maintain permission for that dimension. Also, when you grant read permission for an object, write permission is also allowed for the values you can read, unless you deny it with an explicit PERMIT WRITE statement.

WRITE

Depending on the permission conditions, grants or restricts permission to modify any of the values of an object or only certain values of a dimensioned object.

Restricting write permission for an object does not necessarily mean that the object is totally unavailable. For example, when you have restricted write access to a calculation specification (that is, an aggmap object, program, model, or formula, you can still execute the object— you just cannot edit the contents of the calculation specification.

Oracle OLAP does not prevent you from granting write permission for values that you cannot read within a dimensioned object. When you have both a PERMIT READ and a PERMIT WRITE statement for a dimensioned object, and some values which satisfy the permission conditions for write do not fall within the subset of values which satisfy the permission conditions for read, then those values may be modified but not seen.

Tip:

Write permission is not meaningful for dimensions— except to provide write access to objects dimensioned by the dimension. To grant or restrict the ability to add values to a dimension, issue PERMIT MNT statements for the dimension instead.

See Also:

"When the only PERMIT Statement for an Object is a PERMIT WRITE Statement"

MNT

Depending on the permission conditions, grants or restricts permission to maintain a dimension.

Note that you cannot grant maintain permission to a dimension for which you have restricted read permission. Oracle OLAP automatically denies maintain permission for a dimension when there is restricted read permission for that dimension.

PERMIT

Depending on the permission conditions, grants or restricts permission to use the PERMIT command to change the read, write, maintain, or permit permission for the object.

Note that unless explicitly you explicitly deny permit permission for an object, permit permission is allowed for an object no matter what other permissions are set for that object.

WHEN permission-condition...

When you omit the WHEN clause and execute a PERMIT READ, PERMIT WRITE, or PERMIT MNT statement, Oracle OLAP restores full read, write, or maintain permission to the object.

When you include the WHEN clause, the conditions for granting read, write, maintain, or permit permission consist of one or more Boolean expressions depending on whether or not the object has dimensionality, the type of permission you are setting and, for read and write permissions, whether or not the object has dimensionality:

• When you are specifying permit permission, maintain permission, or read or write permission that applies to an object without dimensionality or when you want the read or write permissions to apply to all the values of a dimensioned object, specify the permission condition as a Boolean expression that evaluates to a single value. (Not that if you do specify a dimensioned Boolean expression to specify permissions for an object without dimensionality or when you are specifying permit or maintain permission, then PERMIT uses the first value in status.)

  Tip:

  Avoid specifying a Boolean value as a permission condition (for example, YES or NO) for a PERMIT PERMIT statement. Instead specify the permission condition as a Boolean variable, a function that returns a Boolean result, or a Boolean value calculated by comparison operators. In this way, when permit permission has been denied, you can restore it by setting the value of the Boolean and executing a PERMITRESET statement. If you ever do lock up an object and are unable to modify its permission, you can specify permit permission for it in the workspace permission program for that workspace, then detach and reattach the workspace.

• If you are specifying read or write permission that applies to individual dimension values or to slices of a dimensioned object, specify one permission condition for each dimension of interest. In this case, the WHEN clause has the following syntax and each dimensioned permission condition consists of a Boolean expression dimensioned by a dimension of the object.

  • WHEN dimensioned_permission_condition1
  •    [BY dimensioned_permission_condition2
  • [UNION dimensioned_permission_condition2A]...

  Use the UNION phrase to efficiently access the union of several symmetric subcubes of a multidimensional variable.

  Note that if a Boolean expression for a dimensioned permission condition has any extra dimensions in addition to an object dimension, PERMIT takes the first value in status to determine which column of Boolean values to use. The intersection of the YES values for each dimension (a logical AND of the conditions) is the subset of values within the object to which the permission applies. When any of the object dimensions are not represented by a dimensioned permission condition, then Oracle OLAP assumes YES for all those dimension values.

Usage Notes

Important Considerations Before You Change the Permissions of an Object

Keep the following important considerations in mind before you change the permissions of an object:

• You must have permit permission for an object to issue PERMIT commands against it.

• Do not lock out the DBA user, who must always have access to everything in the workspace.

• Issuing PERMIT statements against an INTEGER dimension may result in undesired behavior. Dimensions with an INTEGER data type have values identified by their numeric position. PERMIT renumbers INTEGER dimensions to keep the normal sequence of INTEGER values (1, 2, 3, ...). When you must use PERMIT to change the default access rights for an INTEGER dimension and this behavior is not desirable, redefine the dimension with a different, non-integer data type.

When Permission Changes Take Effect

Within PERMIT_READ and PERMIT_WRITE programs, permissions ares only evaluated when you issue an explicit PERMIT or PERMITRESET statement and then reference the targeted object.

Once an analytic workspace is attached and any PERMIT_READ and PERMIT_WRITE programs have executed, permission changes specified by a PERMIT statement take effect:

• When a PERMITRESET statement executes.

• When you are targeting any object (except a dimension), and the permission condition consists of a single Boolean variable, any changes to that Boolean variable affect the permission immediately. (You never have to execute a PERMITRESET in this case.)

• In other situations, if you do not issue a PERMITRESET statement, permission are evaluated upon next reference to the object.

  Note:

  The OBJ function is an exception to this rule. Because OBJ does not load the object into memory, it does not reflect any changes to the object's permission since the last time it was loaded. When you want OBJ to provide information based on new permission criteria, execute a LOAD statement before the OBJ statement.

When the only PERMIT Statement for an Object is a PERMIT WRITE Statement

When the only PERMIT statement for an object is a PERMIT WRITE, then read permission is provided by default for the object. The default read permission is provided independent of the value of the permission condition(s) for the PERMIT WRITE statement. Consequently, a PERMIT WRITE with a single-cell permission condition which evaluates to NO provides read-only access to an nondimensional object or to all the values of a dimensioned object.

When the only PERMIT statement for an object is a PERMIT WRITE with dimensioned permission conditions, it designates some values for read/write access and the remaining values for read-only access. See Example 10-73.

How Dimension Permission Interacts with Permissions for Other Objects

All dimensioned data is affected by the read permission on its dimensions. The dimension values that satisfy the read permission condition determine the default status for the dimension. The values of dimensioned objects that correspond to dimension values without read permission are inaccessible.

However, in order for write permission associated with a dimension to apply to other objects dimensioned by it, there must be at least one PERMIT statement associated with the dimensioned object.

• When you want a dimensioned object to inherit write permission from its dimensions, but you do not want it to have permission of its own, which could interact with the dimension permission, you can simply use a PERMIT READ with a single-cell permission condition that evaluates to YES.

• When you want a dimensioned object to inherit write permission from its dimensions, and you also want it to have permissions of its own, understand how dimension permission interacts with individual object permissions before setting them.

Dimension permission interacts with permission for most of the objects dimensioned by it in the following ways:

• The access permissions of a dimension apply to all dimension surrogates defined for that dimension. You cannot use a PERMIT statement on a dimension surrogate.

• When there is read or write permission associated with a dimension, but no permission restriction associated with an object dimensioned by that dimension, then the permission for the dimensioned object is the same as the dimension permission.

• When there is read permission associated with both the dimension and the dimensioned object, Oracle OLAP determines the values with read permission in the object by taking the intersection of the values with read permission in the dimension and the values with read permission in the object.

• When there is write permission associated with both the dimension and the dimensioned object, Oracle OLAP determines the values with write permission in the object by taking the intersection of the values with read permission in the dimension, the values with write permission in the dimension, and the values with write permission in the object.

However, this is not the case for relations and valuesets. When there is restricted write permission for a dimension of a relation or a valueset, it does not affect relations and valuesets dimensioned by that dimension.

Determining Permission

The permission associated with an object is provided, like an LD, when you describe it using a DESCRIBE statement. The only exception is when you are denied permit permission for the object. In this case, no permission is provided when you describe it.

Advantages of PERMIT Over LIMIT as a Tool for Scoping

As a tool for scoping within application programs, PERMIT has several advantages over the LIMIT command. To restrict the scope of a dimensioned object according to a Boolean expression, you have to use two LIMIT statements, a LIMIT and a LIMITKEEP. You only need one PERMIT statement to do the same thing. Moreover, application users cannot change the restricted scope set by PERMIT commands in application programs. Application users can easily change the scope set by LIMIT commands in application programs simply by executing more LIMIT commands.

Handling Permission Violations

You can use the PERMITERROR option to control the way Oracle OLAP handles attempted violations of the permission established by PERMIT commands for variables. The default value of PERMITERROR is YES, meaning that Oracle OLAP signals an error when a user attempts to access a value for which permission is denied. When you set PERMITERROR to NO, Oracle OLAP simply denies access without signaling an error condition which is useful when you want to do a report of a dimensioned variable for which you have partial permission without limiting the dimensions to the permitted values up front. With PERMITERROR set to NO, values for which you do not have read permission appear as NA values in the report.

Examples

Syntax

PERMITRESET  [object_name]  [READ|WRITE]

Parameters

object_name

Specifies the name of an object for which permission conditions should be reevaluated. When you do not specify an object name, the permission conditions for all objects are reevaluated.

READ

Causes reevaluation of the permission conditions for PERMIT READ commands only, or for a PERMIT READ statement for the specified object.

WRITE

Causes reevaluation of the permission conditions for PERMIT WRITE commands only, or for a PERMIT WRITE statement for the specified object.

Examples

Syntax

POP name1 [nameN]

Parameters

name

The name of a dimension, valueset, variable, or option that was specified in a previous PUSH statement, whose saved value you want to restore.

Examples

For an example of using POP, see Example 10-84.

Syntax

POPLEVEL marker-expression [DISCARD]

Parameters

marker-expression

A text value used as a marker. This value must be the same as the value used in the corresponding PUSHLEVEL statement to mark the start of a series of saved values being popped.

DISCARD

Specifies that the pushed values for that level are discarded when you issue a POPLEVEL statement. When you do not specify DISCARD, the values that were pushed are used to reset the pushed objects.

Usage Notes

Possible Uses for POP LEVEL Statement

Two possible uses for POPLEVEL statements are:

• After a series of increasingly broadening or narrowing LIMIT commands, each with a corresponding PUSH.

• After a single extremely long and complicated PUSH statement, or a series of short ones given throughout a program, that may need a lot of editing. PUSHLEVEL and POPLEVEL allow you to edit the arguments for a long and complicated PUSH statement without also having to edit a corresponding long and complicated POP statement.

Examples

To see a sample program using POPLEVEL, see the example for the PUSHLEVEL command.

Syntax

PROGRAM [contents]

Parameters

contents

A text expression that is the OLAP DML statements that are the lines of the program. You can use most OLAP DML statements within a program. For a discussion of writing, compiling, and debugging OLAP DML programs, see OLAP DML Programs.

The maximum number of lines you can have in a program is 32,000.When coding a PROGRAM statement at the command line level, separate program lines with newline delimiters (\n), or use the JOINLINES function as shown in "Example 10-80".

Examples

Syntax

PROPERTY { name value | DELETE {ALL | name} }

Parameters

name

A text expression that contains the name of the property. The property name can be from 1 to 256 bytes long.

Note:

Do not create your own properties with names that begin with a $ (dollar sign). Properties with names beginning with a $ (dollar sign) are reserved for Oracle OLAP to use as "system" properties that Oracle OLAP interprets in predetermined ways.

Property names have the TEXT data type, unless you include a Unicode escape sequence in the value you specify for the name, or unless you explicitly convert the value you specify to NTEXT (using the CONVERT or TO_NCHAR functions).

value

An expression that contains the value of the property. The property value can have one of the following data types: NUMBER, INTEGER, LONGINTEGER, DECIMAL, SHORTDECIMAL, TEXT, NTEXT, ID, BOOLEAN, DATE, or DATETIME. Oracle OLAP determines the data type based on the value that you specify. For example, when you specify YES, then Oracle OLAP assumes a type of BOOLEAN. When you specify a date value that is stored in a variable of type DATE, then Oracle OLAP assumes a type of DATE for the property.

DELETE ALL

DELETE name

Deletes either all of the properties of the object or only the property you specify for name. You can specify only one name at a time.

Usage Notes

Triggering Program Execution When PROPERTY Executes

Using the TRIGGER command, you can make a PROPERTY statement an event that automatically executes an OLAP DML program. See "Trigger Programs" for more information

Changing a Property Value

When you execute a PROPERTY statement that assigns a new value to an existing property name, then the new value overwrites the previous one.

Determining Property Values with OBJ

To use properties with OLAP DML statements, you must obtain the values by using the property-related keywords of the OBJ function. For example, suppose a property called decplace stores the number of decimal places to use when reporting an object. When you execute a REPORT statement, you can use the OBJ function with the PROPERTY keyword to obtain the value of the decplace property and use that value with the REPORT statement's DECIMAL attribute.

Listing Property Values with FULLDSC

You can list the properties of an object by using a FULLDSC statement. You can use the output from FULLDSC to create new objects. See the FULLDSC program for more information.

Examples

Syntax

PUSH name1 [name]

Parameters

name

The name of a dimension, valueset, option, or variable whose status or value you want to save.

Usage Notes

Effect of a MAINTAIN Statement on a Dimension's Pushed Status

Using a MAINTAIN statement with a dimension clears that dimension's pushed status lists. For example, suppose you have pushed the dimension month several times, with different limits each time. When you then use a MAINTAIN statement to perform any maintenance activity on the month dimension, Oracle OLAP resets the status of month to ALL (the default), and popping that dimension has no effect.

Examples

Syntax

PUSHLEVEL marker-expression

Parameters

marker-expression

A text value to mark the start of a series of PUSH commands all of whose saved values are to be popped at once. A POPLEVEL statement that specifies the exact same marker-expression restores the whole series of saved values.

Usage Notes

Considerations When Designing PUSHLEVEL and POPLEVEL Statements

Keep the following points in mind when coding pushlevel statements:

• You can nest PUSHLEVEL/POPLEVEL pairs, if you specify a different marker for each pair, as illustrated in the following code.

  PUSHLEVEL 'firstlevel'
  PUSH PAGESIZE DECIMALS       < saves values in FIRSTLEVEL
  ...
  PUSHLEVEL 'secondlevel'
  PUSH month product           < Saves values in SECONDLEVEL
  ...
  POPLEVEL 'secondlevel'       < Restores values in SECONDLEVEL
  ...
  POPLEVEL 'firstlevel'        < Restores values in FIRSTLEVEL
  

  You do not normally need multiple levels in a single program. However, Oracle OLAP automatically creates nested levels when one program calls another program and each program contains a set of PUSHLEVEL and POPLEVEL commands.

• When you specify the same marker for two or more PUSHLEVEL commands, a POPLEVEL statement specifying that same marker restores values that were saved only since the most recent PUSHLEVEL statement.

• When you specify a different marker for two or more PUSHLEVEL commands, a POPLEVEL statement that specifies the marker of any PUSHLEVEL statement restores all the values that were saved since that statement, including values that were saved after later PUSHLEVEL commands.

Examples

Syntax

RECAP [number|ALL] [ 'search-text' ] [ FILE file-name ]

Parameters

number

A positive INTEGER that indicates the number of statements to be provided. When you specify search-text, RECAP provides this number of statements from the subset that contains the search-text string. When you do not specify search-text, RECAP provides this number of statements from the most recently executed portion of the command log. The default number is 10.

ALL

When you specify search-text, ALL requests every statement that meets the search requirements. When you do not specify search-text, ALL requests every statement in the command log.

search-text

A quoted text literal. When you specify this argument, RECAP searches the statements in the command log for the ones that contain search-text. The search is not case-sensitive. These statements then compose the subset from which RECAP provides number or ALL statements.

FILE file-name

Writes the output of a RECAP statement to the specified file For file-name specify a text expression that is the name of the file to which output should be written. Unless the file is in the current directory, you must include the name of the directory object in the name of the file.

Note:

Directory objects are defined in the database, and they control access to directories and file in those directories. You can use a CDA statement to identify and specify a current directory object. Contact your Oracle DBA for access rights to a directory object where your database user name can read and write files.

Usage Notes

Order of search-text Parameter

When you use both the search-text and the ALL or number arguments, you must specify search-text second.

RECAP with No Parameter

When you specify RECAP without an argument, the ten most recent statements are provided.

Re-Executing Statements

You can use the output of RECAP to edit a previously executed statement with REEDIT, or reexecute a previously executed statement with REDO.

Identifying Files and Directories

When specifying files and directories in OLAP DML statements, it is good practice to always enclose them in single quotes.

Examples

Syntax

REDO [number|index] 'original' 'replacement' [specifier] 

Parameters

number

A positive INTEGER that indicates the number of the statement to be re-executed. You can display the statements, with their numbers, using a RECAP statement.

index

A negative INTEGER or 0 (zero) that indicates the position of the statement to be re-executed relative to the end of the command log. The most recent statement is 0, the one before that is -1, and so on. The default is 0.

original

A text literal that is part of the statement to be re-executed.

replacement

A text literal that should replace original when the statement is re-executed.

specifier

A specifier listed in the following table. Each specifier indicates where text replacement should occur in the re-executed statement.

Table 10-2 Valid Values for REDO specifier

Specifier	Meaning
FIRST	Indicates that only the first occurrence of original should be changed to replacement.
LAST	Indicates that only the last occurrence of original should be changed to replacement.
n	A number indicating which occurrence of original should be changed to replacement. For example, 3 indicates the third occurrence.
ALL	Indicates that all occurrences of original should be changed to replacement
*	Indicates that all occurrences of original should be changed to replacement.

The default is ALL. When you do not provide a specifier, all occurrences of original are changed to replacement.

Usage Notes

REDO with No Argument

When you type REDO without an argument, the most recent statement are re-executed.

Case-Sensitivity

When matching original with the text of the statement to be re-executed, REDO ignores case differences. For example, assume you specify AT as original, REDO matches it with at, At, aT, or AT in the statement.

When replacing original with replacement, REDO retains the case of all characters in replacement. For example, assume you specify ShOw as replacement, that is exactly how it appears in the re-executed statement.

Examples

Syntax

REEDIT [number|index] 'original' 'replacement' [specifier] 

Parameters

number

A positive INTEGER that indicates the number of the statement to be edited. You can display the statements, with their numbers, using a RECAP statement.

index

A negative INTEGER or 0 (zero) that indicates the position of the statement to be edited relative to the end of the command log. The most recent statement is 0, the one before that is -1, and so on. The default is 0.

original

A text literal that is part of the statement to be edited.

replacement

A text literal that should replace original when the statement is edited.

specifier

A specifier listed in the following table. Each specifier indicates where text replacement should occur in the edited statement.

Table 10-3 Valid Values for REEDIT specifier

Specifier	Meaning
FIRST	Indicates that only the first occurrence of original should be changed to replacement.
LAST	Indicates that only the last occurrence of original should be changed to replacement.
n	A number indicating which occurrence of original should be changed to replacement. For example, 3 indicates the third occurrence.
ALL	Indicates that all occurrences of original should be changed to replacement
*	Indicates that all occurrences of original should be changed to replacement.

The default is ALL. When you do not provide a specifier, all occurrences of original are changed to replacement.

Usage Notes

REEDIT with No number or index Argument

When you type REEDIT without number or index, the most recent statement is edited.

Case-Sensitivity

When matching original with the text of the statement to be edited, REEDIT ignores case differences. For example, assume you specify AT as original, REEDIT matches it with at, At, aT, or AT in the statement.

When replacing original with replacement, REEDIT retains the case of all characters in replacement. For example, assume you specify ShOw as replacement, that is exactly how it appears in the edited statement.

Examples

Syntax

REGRESS [NOINTERCEPT] dependent independent... [WEIGHTBY weight]

Parameters

NOINTERCEPT

Directs Oracle OLAP to suppress the constant term (intercept) in the regression equation. The default is to calculate a constant term.

dependent

An expression to be used as the dependent variable in the regression.

In calculating the results, REGRESS loops over all the dimensions of the dependent and independent variables.

independent

One or more expressions to be used as the independent variables (regressors) in the regression.

WEIGHTBY weight

Specifies a weighted regression. The numeric expression weight supplies the weights for each data point (observation). Giving higher weights to more reliable observations results in a higher quality regression. WEIGHTBY must come last in the REGRESS command.

When weight is less than zero for any observation, an error occurs. When weight is equal to zero for any observation, that observation is ignored in the calculation. When WEIGHTBY is omitted, an unweighted regression is calculated.

Usage Notes

Ignoring NA Values

In performing its calculations, the REGRESS command ignores any observation that has an NA value.

Producing a Standard Report

The standard report for a regression shows the coefficient, standard error, and T-ratio for each independent variable; and the R-square, F-Statistic, number of observations, and standard error of estimate for the regression. To produce this report, type the following.

REGRESS.REPORT

Obtaining Results

For information on how to obtain portions of the results of REGRESS for your own reports or further analysis, use an INFO statement.

Examples

Syntax

REGRESS.REPORT

Examples

Syntax

RELATION {dimension-name relation-name }| DELETE { ALL | dimension-name}

Parameters

dimension-name

The name of a previously-defined dimension. The dimension specified by the dimension-name argument must be a dimension of the currently considered object for which you want to specify or delete a default relation.

Note:

The dimension specified by the dimension-name argument must be a dimension of the currently considered object. The current object is the object that has been most recently defined or considered during the current session. To make an object definition the current definition, use a CONSIDER statement.

relation-name

The name of a previously-defined relation that Oracle OLAP uses as the default relation for the dimension specified by dimension-name.

DELETE

Specifies removal of previously-specified default relation information.

ALL

Removes all previously-defined default relation information.

Usage Notes

How Oracle OLAP Chooses Between Multiple Relations

When there are multiple relations between the two dimensions and you attempt to perform calculation on one of those dimension based on related dimensions, Oracle OLAP chooses the relation to use when executing the statement as follows:

1. Uses the relation specified in the statement, if any.

2. Uses the default relation for the dimension. Oracle OLAP determines the default relation as follows:

   1. When you have specified a default relation using the RELATION command, Oracle OLAP recognizes that relation as the default relation.

   2. When you have not specified a default relation using the RELATION command, Oracle OLAP recognizes the first relation that you defined as the default relation.

Identifying Default Relations

Use the OBJ function with the RELATION keyword to identify the default relation information for an object.

Multiple RELATION Commands Against the Same Object

Unlike other statements that operate against the most recently considered object (for example, an LD statement), a new RELATION command does not replace previously-issued RELATION commands. Instead, issuing multiple RELATION commands against the same object has a cumulative effect.

Examples

Syntax

RELEASE  objects

Parameters

objects

One or more variables, relations, valuesets, dimension names, or analytic workspace names, separated by commas, that you want to release. To specify individual partitions of a partitioned variable, use the following syntax.

     variable_name (PARTITION partition_name [, PARTITION partition_name ]...)

Precede each analytic workspace name with AW using the following syntax:

AW analytic workspace name

When you specify an analytic workspace in this list, all acquired objects in that analytic workspace are released after all pending changes are made to them. All changes made to the variables, relations, valuesets, or dimensions before a RELEASE statement executes are preserved as private changes after the release statement.

Usage Notes

Releasing Non-Updated or Uncommitted Objects

Similarly to using an AW DETACH statement for analytic workspaces that has been updated. Using RELEASE for objects that have been updated does not allow others to acquire the object until you commit or roll back the transaction. It may still be useful to release an object that has been updated before a commit when one wants to make further what-if changes and later needs to use UPDATE to update all acquired variables.

Releasing a Dimension Causes the Dimension to Revert

When you release an acquired dimension, the dimension is automatically reverted (see the REVERT command for an explanation of what it means to revert a dimension).

As the following code illustrates, releasing an acquired dimension causes an automatic revert.

User A issues the following OLAP DML statements.

AW ATTACH myworkspace MULTI
ACQUIRE RESYNC time WAIT
MAINTAIN time ADD 'Y2002'
actuals (time 'Y2002', ...) = 37
REPORT time --> ..., 'Y2002'
... report
SHOW actuals (time 'Y2002', ...) --> 37
RELEASE time
REPORT time --> ... (no 'Y2002')
AW ATTACH myworkspace MULTI
... report
ACQUIRE RESYNC actuals, time WAIT
MAINTAIN time ADD 'Y2002'
actuals (time 'Y2002', ...) = 37
REPORT time --> ..., 'Y2002'
SHOW actuals (time 'Y2002', ...) --> 37
... report
REVERT time
REPORT time --> ... (no 'Y2002')
MAINTAIN time ADD 'Y2002'
... report
REPORT time --> ..., 'Y2002'
SHOW actuals (time 'Y2002', ...) --> NA
... report

Examples

Syntax

CALL REMOVE_CUBE_MODEL(logical_cube, logical_dim, model_name, is_static_model)

Parameters

CALL

Because REMOVE_CUBE_MODEL is an OLAP DML program with arguments, you invoke it using the OLAP DML CALL statement.

logical_cube

A text expression that is the name of the cube as defined in the Oracle data dictionary.

See Also:

"Guidelines for Specifying Values for the Names of Logical OLAP Objects"

logical_dim

A text expression that is the Oracle data dictionary name of the cube dimension being modified.

model_name

A text expression that is the name of the logical model that is associated with logical_dim.

is_static_model

A Boolean expression that specifies whether the MODEL statement to be removed is before or after the RELATION (for aggregation) statements in the aggmap for logical_cube.

The default value is TRUE which means that MODEL statement to be removed is before the RELATION statements (that is, model_name is a static model).

Specify FALSE if the MODEL statement you want to remove is after the RELATION statements (that is, model_name is a dynamic model).

Syntax

CALL REMOVE_DIMENSION_MEMBER(member_id, logical_dim, hier_list, [ auto_compile ])

Parameters

CALL

Because REMOVE_DIMENSION_MEMBER is an OLAP DML program with arguments, you invoke it using the OLAP DML CALL statement.

member_id

A text expression that is the member that you want to remove from the cube dimension hierarchies.

logical_dim

A text expression that is the Oracle data dictionary name of the cube dimension being modified.

See Also:

"Guidelines for Specifying Values for the Names of Logical OLAP Objects"

hier_list

A multi-line text expression consisting of the Oracle data dictionary names of all of the hierarchies from which you want to remove the cube dimension member. Specify one hierarchy name per line.

To entirely remove a member from the dimension, specify NA.

auto_compile

A Boolean expression that specifies whether or not you want related analytic workspace objects (for example, changes to the parent relation) to be updated immediately.

The default value is TRUE in which case all of the changes to the analytic workspace that are needed to remove the cube dimension member happen now.

Specify FALSE only when, for performance reasons, you want to make a bulk set of changes before issuing a compile. In this case, you need to explicitly compile the cube dimension before the values of the analytic workspace objects take effect as described in "Explicitly Compiling a Cube Dimension".

Note:

Regardless of the value that you specify for this argument, the new member is always immediately removed from the dimension -- even when an error is signaled during compilation.

Examples

Syntax

CALL REMOVE_MODEL_DIMENSION(logical_dim, model_name, explicit_dim)

Parameters

CALL

Because REMOVE_MODEL_DIMENSION is an OLAP DML program with arguments, you invoke it using the OLAP DML CALL statement.

logical_dim

A text expression that is the Oracle data dictionary name of the cube dimension being modified.

See Also:

"Guidelines for Specifying Values for the Names of Logical OLAP Objects"

model_name

A text expression that is the name of the logical model that is associated with logical_dim.

explicit_dim

A text expression that is the identifier of the dimension that you want to remove from the cube dimension's model.

Syntax

RENAME oldname newname [AW workspace]

Parameters

oldname

The name of an existing object in an analytic workspace. You can specify a qualified object name to indicate the attached workspace in which the object resides. As an alternative, you can use the AW keyword to specify the workspace. Do not use both.

When you do not use a qualified object name or the AW keyword to specify an analytic workspace, the object is renamed in the current workspace.

For an unnamed composite, use the same syntax that was used to create it. See "Naming an Unnamed Composite".

newname

The new name. The new name of an analytic workspace object cannot duplicate any other name in the workspace in which the object exists. Choose a name according to the rules for naming analytic workspace objects (see the main entry for the DEFINE command. To change a named composite to an unnamed composite, use the SPARSE keyword as the newname argument. See "Unnaming a Named Composite".

AW workspace

The name of an attached workspace in which you want to rename the object. When you do not use a qualified object name or the AW keyword to specify an analytic workspace, the object is renamed in the current workspace.

Usage Notes

Updating Associated Objects

When you change the name of a variable, objects that use that variable, such as formulas, are not automatically updated.

When you change the name of a dimension, the definitions of any objects that are dimensioned by that dimension are automatically updated. Additionally, any valuesets for the renamed dimension are automatically updated for the new name.

RENAME and PERMIT

You may not rename an object when a PERMIT statement denies you the right to change its permission. Renaming an object does not affect permission associated with it.

Naming an Unnamed Composite

You can name an unnamed composite with a RENAME statement. The following example assigns the name m.prod to an unnamed composite that is dimensioned by market and product.

RENAME SPARSE <market product> m.prod

Unnaming a Named Composite

You can change a named composite to an unnamed composite when the composite has no properties or permission restrictions and when there is at least one object dimensioned by it. In addition, there cannot be an unnamed composite with the same dimensions in the same order as the named composite, and the named composite cannot be used in the dimension list of any unnamed composite. To change a named composite to an unnamed composite, use the SPARSE keyword as the newname argument. The following example changes the named composite m.prod to an unnamed composite.

RENAME m.prod SPARSE

Restrictions on Renaming Composites

You cannot rename a composite when it is a base dimension of an unnamed composite, or when one of its base dimensions is an unnamed composite.

Examples

Syntax

REPORT [NOHEAD] [GRANDTOTALS] [[SUBTOTALS] {GROUP group-dimension}...] -       [[SUBTOTALS] [attributes] DOWN down-dimension] -      [[ROWTOTALS] { ACROSS across-dimension [limit-clause]: }... ] -      [SUBTOTALS] [attributes] expression(s)

Parameters

When you specify only the expression argument, REPORT produces a report with the layout described in "Default Layout". When you specify some but not all of the dimensions of an expression in GROUP, DOWN, or ACROSS phrases, REPORT follows the default layout as closely as possible with the unspecified dimensions.

NOHEAD

Specifies that the report should contain no initial blank line and no headings. NOHEAD overrides any HEADING arguments you specify for the attributes argument and suppresses all headings that the REPORT command normally generates automatically.

GRANDTOTALS

Includes a grand total for each numeric column at the end of your report.

SUBTOTALS

Includes subtotals for numeric columns. A row of dashes precedes each row of subtotals. When you specify SUBTOTALS for an expression or DOWN phrase, you get subtotals for each GROUP dimension (or composite). When you specify SUBTOTALS for a GROUP phrase, you get subtotals for the specified dimension and for any slower-varying GROUP dimensions. The subtotals for a group appear at the bottom of the last slice in the group.

GROUP

Produces a separate group, or two-dimensional slice, of the data for each value of group-dimension. You can use the GROUP phrase more than once to specify multiple GROUP dimensions (or composites). In this case, you produce a separate slice for each combination of the values of the GROUP dimensions.

group-dimension

The name of a dimension or composite from which to retrieve the values to use as group labels, or one or more TEXT expressions that are the actual values you want to use as group labels:

• When group-dimension is the name of a composite or a conjoint dimension, Oracle OLAP creates a separate group for each base dimension.

• When group-dimension is a TEXT expression, the expression must be dimensioned only by the desired GROUP dimension, and each value of the expression should be descriptive text that corresponds to its associated dimension value.

The dimensions that you specify in a GROUP phrase are not required to be relevant to the data they loop over. See "Default Layout".

DOWN

Specifies that the report includes row labels (that is, one or more columns on the left side of your report that label the other values). You can have only one DOWN phrase.

down-dimension

The name of a dimension or composite from which to retrieve the values to use as row labels, or one or more TEXT expressions that are the actual values you want to use as row labels.

• When down-dimension is the name of a non-conjoint dimension, REPORT produces only one column of row labels.

• When down-dimension is the name of a composite or a conjoint dimension, Oracle OLAP creates a separate column for each base dimension.

• When down-dimension is a TEXT expression, the expression must be dimensioned only by the desired DOWN dimension, and each value of the expression must be descriptive text that corresponds to its associated dimension value.

The dimensions that you specify in a DOWN phrase are not required to be relevant to the data they loop over. See "Specifying Extra Dimensions".

ROWTOTALS

Includes a column headed "TOTAL" at the right side of the report with a total for each numeric row. Including a row total in your report does not imply either column subtotals or a grand total.

ACROSS

Produces a row of column headings across the top of your report, one for each value in dimension. Under each heading, REPORT produces a column of data for the data expression you specify. You can have multiple ACROSS phrases (or composites) in the report.

across-dimension

The name of a dimension or composite from which to retrieve the values to use as column headings, or one or more TEXT expressions that are the actual values you want to use as column headings.

• When across-dimension is the name of a composite or a conjoint dimension, Oracle OLAP creates a separate heading column for each base dimension.

• When across-dimension is a TEXT expression, the expression must be dimensioned only by the desired across-dimension, and each value of the expression should be descriptive text that corresponds to its associated dimension value. For information on providing formatted labels for a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR, see "Formatting DAY, WEEK, MONTH, QUARTER, and YEAR Dimension Values".

The dimensions that you specify in an ACROSS phrase are not required to be relevant to the data they loop over. See "Specifying Extra Dimensions".

limit-clause

When you specify a dimension in the ACROSS phrase, the temporary status of that dimension during the execution of the execution of the REPORT statement. (You cannot include a limit-clause argument when you specify a composite in the ACROSS phrase.)

The syntax of limit-clause is the same syntax as any of the limit-clause arguments in the various forms of the LIMIT command (that is, the syntax of the LIMIT command after the limit-type argument such as "TO"). For the syntax of these arguments, see LIMIT (using values) command, LIMIT using LEVELREL command, LIMIT (using parent relation), LIMIT (using related dimension) command, LIMIT NOCONVERT command, and LIMIT command (using POSLIST).

When the limits you specify result in an empty status for the dimension, an error occurs (regardless of the setting of the OKNULLSTATUS option). However, when you include the phrase IFNONE label, the error is suppressed and execution of your program branches to the specified label, where you can handle the error.

attributes

One or more format attributes from Table 10-4 that specify how to format the data. For information on providing formatted labels for a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR, see "Formatting DAY, WEEK, MONTH, QUARTER, and YEAR Dimension Values".

Tip:

When a variable has a formatting property attached to its definition, you can use the OBJ function to obtain the value of that property and use it as the value of an attribute in a REPORT statement.

When you do not specify any attributes, Oracle OLAP formats the data values using the default format described in "Default Layout". In this case, Oracle OLAP automatically determines the width of the columns, the number of decimal places, whether commas are used to mark thousands in numeric values, and so on.

Table 10-4 Formal Attributes for Data Values in a Report

Attribute	Meaning
HEADING 'text'	Specifies text to use instead of default column headings. When you use the HEADING attribute, the position of the heading you specify varies depending on how many expressions it must span in your report. Consequently, your heading may or may not replace a default heading. Also, when you use the HEADING attribute to specify a column title that is wider than the column width, the text of the title wraps within the width of its column.
WIDTH n	(Abbreviated: W.) Makes the column n spaces wide. The maximum width is 4,000 characters. Columns with a width of 0 (zero) are suppressed.(See also "Default Values for the Width Attribute".)
TRUNCATE	(Abbreviated: TRUNC.) Truncates a character value to the column width when it does not fit in the column.
NOTRUNCATE	(Abbreviated: NOTRUNC) Creates additional lines when the character value does not fit in the column. (Default.)
CENTER	(Abbreviated: C.) Centers the value within its column.
LEFT	(Abbreviated: L.) Left-justifies the value within its column. (Default for text data.)
RIGHT	(Abbreviated: R.) Right-justifies the value within its column. (Default for numeric and Boolean data.)
SPACE n	(Abbreviated: SP.) Precedes the column with the specified number of spaces. (By default, REPORT precedes the first column by 0 spaces, and other columns by 1 space.)
FILL 'char'	Puts char into unused positions in the column. (The default fill character is a space.)
COMMA	Marks thousands and millions with commas or the character currently recorded in the THOUSANDSCHAR option. Overrides the setting of the COMMAS option.
NOCOMMA	Does not marks thousands and millions. Overrides the setting of the COMMAS option.
PAREN	Uses parentheses to indicate negative numbers. Overrides the setting of the PARENS option
NOPAREN	Uses the minus sign to indicate negative numbers. Overrides the setting of the PARENS option.
DECIMAL n	(Abbreviated: D.) Shows n decimal places. Decimal places are separated using the character currently recorded in the DECIMALCHAR option. Overrides the value of the DECIMALS option. Note: REPORT suppresses decimal places in row and column totals of integer data unless you specify the DECIMAL attribute for the totaled expression. Additionally, when you set the DECIMAL attribute to 0 and you use the NOLEADINGZERO keyword, any decimal values between -1 and 1 that are rounded to 0 are not shown.
MDECIMAL n	Shows n decimal places in numbers formatted with M-notation; n can be any number from 0 to 16, or 255.
NODECIMAL	Shows the number of decimal places specified by the setting of the DECIMALS option. (Default.)
EDECIMAL n	Shows n decimal places in numbers formatted with E-notation; n can be any number from 0 to 16, or 255.
ENOTATION	Always uses scientific notation, also called exponential notation or E-notation (appends "E", and includes a sign before the exponent, for example, .230E+2 or .230E-2).
NOENOTATION	Does not use E-notation; instead, uses to conditional M-notation.
CENOTATION	Conditionally uses E-notation, when needed to make a value fit in a column.
MNOTATION	Always uses M-notation (divides values by one million and appends "M").
NOMNOTATION	Does not use M-notation; instead, uses asterisks for oversize values.
CMNOTATION	Conditionally uses M-notation, when needed to make a value fit in a column. (Default.)
FOLDDOWN	For a multiline character value, places the first line on the row with the other values, and places additional lines below the rest of the row; also strips any leading or trailing spaces. (Default.)
FOLDUP	For a multiline character value, places all but the last line above the rest of the row, and the last line on the row with the other values; also strips any leading or trailing spaces.
INDENT n	Indents the value n spaces within its column. The default is 0.
LEADINGZERO	Puts a leading zero before decimal numbers between -1 and 1.
NOLEADINGZERO	Suppresses leading zeros before decimal numbers between -1 and 1.
CNLEADINGZERO	Puts a leading zero before decimal numbers between -1 and 1 when it does not cut off any significant digits. (Default.)
LSET 'text'	Adds text to the left of the value. When you use LSET with an expression that contains NA values, the text you specify with LSET is not included to left of any NA value
NOLSET	Does not add anything to the left of the value. (Default.)
RSET 'text'	Adds text to the right of the value. When you use RSET with an expression that contains NA values, the text you specify with RSET is not included to right of any NA value.
NORSET	Does not add anything to the right of the value. (Default.)
NASPELL 'text'	Uses text instead of NA values. Overrides the setting of the NASPELL option.
NONASPELL	Spells NA values as indicated by the NASPELL option. (Default.)
YESSPELL 'text'	Uses text for TRUE Boolean values. The default is recorded in the YESSPELL option.
NOSPELL 'text'	Uses text for FALSE Boolean values. Overrides the setting of the NOSPELL option.
ZSPELL 'text'	Uses text instead of zero numeric values. Overrides the setting of the ZSPELL option.
NOZSPELL	Spells zero numeric values as specified by the ZSPELL option. (Default.)
OVER textexp	Overlines the value with the value of a character expression (textexp). When textexp is a literal value, it must be enclosed in single quotes. Useful literal values include: '-' to overline value or column, '=' to double overline value or column, and '' to indicate that a value or column is does not have an overline. To overline only when a condition is met, for textexp use: IF boolean-expression THEN '-' ELSE ''
UNDER textexp	Underlines the value with the value of a character expression (textexp). When textexp is a literal value, it must be enclosed in single quotes. Useful literal values include: '-' to underline value or column, '=' to double underline value or column, and '' to indicate that a value or column is not underlined. To underline only when a condition is met, for textexp use: IF boolean-expression THEN '-' ELSE ''
VALONLY	Used with the UNDER and OVER attributes, underline or overline the value only.
NOVALONLY	Used with the UNDER and OVER attributes, underlines or overlines the entire width of the column. (Default.)

expression . . .

The data to be shown in the report. The way the data looks depends on its data type and the attributes you specify.

Note:

The REPORT command is not equipped to deal with NTEXT values. Do not include them in any part of a report.

You can specify multiple expressions and these expressions do not have to have the same dimensions:

• When you have several data expressions in your REPORT statement, you can specify different format attributes before each. When you want attributes to apply to two or more data expressions, enclose the expressions in angle brackets (< >).

  attributes <expression1, expression2>

• Attributes outside the brackets apply to all the expressions within the brackets. However, you can also specify attributes for only one expression (even an attribute that contradicts one that applies to the group) within the brackets by including them immediately before the expression.

  attributes0 <attributes1 expression1, expression2>

  In this case, attributes0 applies to both expression1 and expression2; while attributes1 only applies to expression1.

Tip:

To create running totals, use the RUNTOTAL function.

Usage Notes

Report Options

Several options effect reports created using the OLAP DML. These options are listed in "Report Options".

Default Layout

When you do not specify any of the layout phrases (GROUP, DOWN, or ACROSS), REPORT tries to format its output compactly (typically, a two-dimensional report of the data with one dimension down the side and the others across the top, much like a spreadsheet). Any additional dimensions of the data form "slices" or separate two-dimensional segments, like a series of spreadsheets. By default, REPORT uses the following rules to determine the layout:

• The fastest-varying dimension in an expression (the one that appears first in the definition of that expression) goes across, the next fastest goes down, and any remaining dimensions become GROUP slices.

• The order of dimensions in a list of two or more expressions is a simple combination of the dimensions that appear in the definitions of the component expressions. The original order is preserved as far as possible, subject to the rule that repeated mentions of the same dimension are dropped. For example, the dimensions of the combined variables price and industry.sales, where price has the dimensions <month product> and industry.sales has the dimensions <quarter product region>, are <month product quarter region>.

When you produce a report of data for a variable dimensioned by a composite, REPORT automatically breaks out the data by the base dimensions of the composite that is used in the definition of the variable. When a particular combination of base dimension values does not exist in the composite, the report shows NA for the corresponding data cell. See Example 10-104.

Default Values for the Width Attribute

When you omit the WIDTH attribute for an ACROSS phrase, the default width is the value of the COLWIDTH option (default is 10).

When you omit the WIDTH attribute for a DOWN phrase or when you specify a simple dimension in the DOWN phrase, the default width is the value of the LCOLWIDTH option (default is 14).

When you omit the WIDTH attribute for a DOWN phrase that specifies a conjoint dimension or a composite, the default label width is the width of the COLWIDTH option and there is a separate column for each base dimension. You can provide a different width for each base dimension column by using the KEY function. You can produce a label column for each base dimension with the KEY function and use a separate WIDTH attribute for each column. For example, assume that proddist is a composite with the base dimensions product and district. In this case, you can use a statement similar to the following one.

REPORT DOWN < W 8 KEY(proddist, product) -
   W 12 KEY(proddist, district) > . . .

When you use the default line width of 80 characters (determined by the LSIZE option) and the default column width settings (with a single label column of 14 characters) a line of output can accommodate the labels column and six data columns.

The combined width of all the columns of a report cannot be greater than 4,000 characters.

When a numeric value is too large to fit into a data column, REPORT rounds it off to the nearest million with the symbol M at the right side of the cell. When a value is still too large, REPORT replaces the value with a series of asterisks.

Formatting DAY, WEEK, MONTH, QUARTER, and YEAR Dimension Values

When you use a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR as the dimension in an ACROSS, DOWN, or GROUP phrase, you can use the CONVERT function to override the dimension's VNF (or the default VNF) and provide your own format for the dimension value names. To override the VNF, use the CONVERT function with a vnf argument instead of the dimension argument to the ACROSS, DOWN, or GROUP keyword. For example, in a report of units data, you can format the labels for the month dimension by using the following statement.

REPORT HEADING 'Month' DOWN -
   CONVERT(month TEXT '<mtextl> <yyyy>') units

Specifying Extra Dimensions

The REPORT command uses whatever dimensions you specify in laying out the report, regardless of whether the expressions to be shown are dimensioned by these dimensions. When an expression is not dimensioned by one or more of the dimensions specified, the values of that expression are repeated for each value of the extra dimension. This fact is sometimes useful for comparisons. See Example 10-103.

Examples

Syntax

RESYNC objects

Parameters

objects

A list of one or more variables, relations, valuesets, or dimension names, separated by commas, that you want to resynchronize. Oracle OLAP ignores any acquired objects in this list.

Use Name as a value in objects to specify that you want to resynchronize the newest objects defined by other users.

Usage Notes

Keeping Logical Relationship of Objects

When using RESYNC keep in mind the logical relationship of different objects to avoid losing the logical consistency of the data by promoting some objects, but not others to a new generation.

Resynchronizing Objects that Share a Composite Dimension

Objects that share a composite dimension can be resynchronized separately when all such objects that are not being resynchronized are either unchanged or acquired.

Examples

Syntax