MAINTAIN

Syntax

MAINTAIN composite|dimension ADD valuelist [FIRST|LAST|BEFORE position|AFTER position]

Parameters

dimension

A non-concat dimension, already defined in an attached analytic workspace.

composite

A composite. When the composite is a named composite, it must be defined in an attached analytic workspace. When the composite is unnamed, it must have been used in defining an object in an attached analytic workspace. Use the SPARSE keyword to refer to an unnamed composite (for example, SPARSE <market product>).

ADD valuelist

Specifies that the values in valuelist are to be added to the dimension or composite:

• When you use this argument to add values to a composite or a dimension of type TEXT or ID, the valuelist can be text literals or a TEXT or ID expression. When it is a multiline text expression, each element (line) is treated as a separate value.Do not add null dimension values (empty single quotes) or values that consists of spaces only, because there is no way you can refer to such values in the future.

• When dimension is INTEGER, valuelist can be an INTEGER quantity, such as 5 or 100.

FIRST

LAST

Specify the logical position at which dimension values are added. FIRST indicates that the new values are inserted before any existing values. LAST indicates that new values are added after the current values. LAST is the default. When you are adding a certain quantity of INTEGER values to an INTEGER dimension, that quantity of INTEGER values are added before or after any existing INTEGER values (depending on your specification), and all the INTEGER values in the resulting series are automatically adjusted into simple numeric order.

All values specified before the keyword FIRST or LAST are placed in that position, not just the one value immediately preceding the keyword in your statement.

BEFORE position

AFTER position

Specify a position before or after which the dimension values are to be added. For position you can specify an existing dimension value, a character expression whose value is an existing dimension value, or an INTEGER expression whose value represents the position of a dimension value. When you are adding a certain quantity of INTEGER values to an INTEGER dimension, that quantity of INTEGER values are added before or after the INTEGER position you specify, and the INTEGER values in the whole of the resulting series are automatically adjusted into simple numeric order.

All values specified before the keywords BEFORE or AFTER are placed in that position, not just the one value immediately preceding the keyword in your statement.

Usage Notes

Performance When Using MAINTAIN ADD BEFORE or AFTER position

The cost performance of using MAINTAIN ADD BEFORE or MAINTAIN MOVE AFTER statements depends on the number of values in the dimension rather than the number of values being added. Consequently, instead of issuing many of these statements one after another:

1. Add the values using simple MAINTAIN ADD statements

2. Set the status of the dimension status to the values you are concerned with.

3. Order the added values, by issuing a single MAINTAIN MOVE dimension value statement

Adding Values to an INTEGER Dimension

When you use MAINTAIN to add values in an INTEGER dimension, the values are renumbered to keep the normal sequence of INTEGER values (1, 2, 3, ...).

Conjoint Dimensions and Composites

Each value of a conjoint dimension or composite is a combination of values from each of the dimensions (and composites, if any) in its dimension list. To add values to a conjoint dimension or composite, specify each value combination enclosed in angle brackets. The values in a given combination must be in the same order as the dimensions and composites in the definition of the conjoint dimension or composite. Each dimension value in the combination must already exist as a value in the corresponding base dimension. However, when a composite value in the combination does not exist, Oracle OLAP automatically adds the value to the appropriate composite.

Examples

MAINTAIN city ADD 'Omaha' 'Seattle'

MAINTAIN city ADD 'Atlanta' FIRST, 'Peoria' AFTER 'Omaha'

textvar = 'Columbus'
MAINTAIN city ADD textvar BEFORE 5

DEFINE proddist DIMENSION <product, district>
MAINTAIN proddist ADD <'Tents' 'Boston'> <'Footwear' 'Denver'>

prodname = 'Canoes'
distname = 'Seattle'
MAINTAIN proddist ADD <prodname, distname>

Syntax

MAINTAIN dimension ADD {valuelist|{n PERIODS FIRST}|{n PERIODS LAST}}

Parameters

dimension

A non-concat dimension, already defined in an attached analytic workspace.

ADD valuelist

Specifies that the values in valuelist are to be added to the dimension. When dimension is of type DAY, WEEK, MONTH, QUARTER, or YEAR, then valuelist can be text constants or a TEXT, ID, or DATE expression. When the values are TEXT, they can be in the format specified by the VNF (value name format) for the dimension (or in the default format for the type of dimension you are maintaining when the dimension does not have a VNF) or in a valid input style for date values. When the values are specified as a TEXT expression, each element or line is treated as a separate value.

When the values are in the format specified by the VNF or in the default format for this type of dimension, each value explicitly indicates the time period you want to add. For example, assume that the VNF for a month dimension is '<MTXT><YY>'. In this case, the value JAN99 represents the month January 1999.

When you specify a value for a DAY, WEEK, MONTH, QUARTER, or YEAR dimension as a date, you must provide only the date components that are relevant for the type of dimension you are maintaining. For a DAY or WEEK dimension, you must supply the day, month, and year components. For a MONTH or QUARTER dimension, you must supply only the month and year (for example, 'JUN98' or '0698' for June 1998). For a YEAR dimension, you must specify only the year (for example, '98' for 1998). For information about the valid input styles for dates, see the DATEORDER option.

When you add a dimension value by specifying a DATE expression or a TEXT value that represents a complete date, you can specify any date that falls within the time period you want to add. For example, to add the month January 1999, you can specify any date from '01JAN99' through '31JAN99'. Oracle OLAP uses the DATEORDER option to resolve any ambiguities.

When adding values to a DAY, WEEK, MONTH, QUARTER, or YEAR dimension that does not yet have values, you must specify only the first and last values you want to add for the dimension. Oracle OLAP automatically fills in the gaps with appropriate values for the intervening time periods.

When a DAY, WEEK, MONTH, QUARTER, or YEAR dimension already has values, you can add values only at the beginning or the end of the existing list. To add values, you must specify only the first or last value you want to add. Oracle OLAP automatically fills in the gap between the existing list and the value you specify.

n PERIODS FIRST

n PERIODS LAST

Specifies the number of periods to add at the beginning or end of an existing list of dimension values.

Examples

DEFINE qtr DIMENSION QUARTER
MAINTAIN qtr ADD '01jan98' '31dec99'

Syntax

MAINTAIN dimension ADD SESSION member_name [= calculation] -

      [STEP DIMENSION (stepdim...)][apply-to]

where:

• calculation is one of the following:

  model-equation

  AGGREGATION (dimension-members....)

• apply-to specifies the basis on which the custom aggregation is added using one of the following phrases:

  APPLY TO AGGMAP aggmaps

  APPLY FOR VARIABLE variables

  APPLY WITH RELATION relations

Parameters

dimension

A dimension that is already defined in an attached analytic workspace. You can specify any type of dimension for dimension except a non-unique concat dimension or a base dimension of either a unique or non-unique concat dimension.

ADD SESSION

ADD SESSION indicates maintenance of a temporary calculated member.

member-name

Specifies the name of the temporary calculated member.

=

Indicates that you are defining a new calculated member.

model-equation

A text expression that specifies the calculation used as a dynamic model to calculate custom member values. (See the SET command for more information about model equations.)

AGGREGATION

Indicates that the temporary calculated member is added as a custom aggregation using the specified dimension members. This clause effectively modifies the RELATION statement of aggmap objects that are the aggregation specification for variables dimensioned by dimension. Consequently, a MAINTAIN ADD SESSION statement that contains an AGGREGATION clause must also contain an APPLY WITH RELATION clause.

dimension-members

A text expression that specifies one or more dimension values to be used by the custom aggregation. When using a literal to specify multiple dimension members, separate the values with commas

STEP DIMENSIONS

Indicates that the calculation is a time-series function.

stepdim

A text expression that specifies the dimension along which the time-series function is calculated. When using a literal to specify multiple dimension names, separate the names with commas.

APPLY TO AGGMAP

Indicates that the calculated temporary member is added only to dimensions used by the specified aggmap objects.

aggmaps

A text expression that specifies the name of one or more aggmap objects to which the temporary calculated member is added. When using a literal to specify multiple aggmap objects, separate the names with commas. The temporary calculated member is added to each of the specified aggmap objects.

APPLY FOR VARIABLE

Indicates that the temporary calculated member is added only to dimensions used by the default aggmap objects for the specified variables.

variables

A text expression that specifies the one or more variable names for which the temporary calculated member is added to. When using a literal to specify multiple variable names, separate the names with commas. The temporary calculated member is added to the default aggmap object of each specified variable.

Important:

When a specified variable does not have a default aggmap, using this clause generates an error. Use AGGMAP SET or $AGGMAP to specify a default aggmap for the variable.

APPLY WITH RELATION

Indicates that the temporary calculated member is added dimensions used by the aggregation specifications that contain a RELATION statement for the specified relation.

relation

A text expression that specifies the name of the relation for which there must be a RELATION statement in the AGGMAP statement.

Usage Notes

Finding Out Information About Temporary Calculated Members

Once you have added a temporary calculated member using a MAINTAIN statement, you can use AGGMAPINFO to discover the temporary calculated members you have added, the equations used to calculate members, and the dimension members used in the right-hand side of equations used to calculate custom members.

Examples

DEFINE letter DIMENSION TEXT
DEFINE my_quantity VARIABLE DECIMAL <letter>

LETTER                  MY_QUANTITY
-------------- ------------------------------
A                                       10.00
B                                      100.00

DEFINE LETTER DIMENSION TEXT

DEFINE LETTER.PARENTREL RELATION LETTER <LETTER>

DEFINE MY_QUANTITY VARIABLE DECIMAL <LETTER>

DEFINE MY_MODEL MODEL
MODEL
DIMENSION letter
END

DEFINE MY_AGGMAP AGGMAP
AGGMAP
RELATION letter.parentrel PRECOMPUTE(NA)
MODEL my_model PRECOMPUTE(NA)
END

DEFINE MY_QUANTITY_DEFINITION VARIABLE DECIMAL <LETTER>

LETTER
--------------
A
B

REPORT letter.parentrel

LETTER                LETTER.PARENTREL
-------------- ------------------------------
A              NA
B              NA

REPORT my_quantity

LETTER                  MY_QUANTITY
-------------- ------------------------------
A                                       10.00
B                                      100.00

LETTER             MY_QUANTITY_DEFINITION
-------------- ------------------------------
A                                          NA
B                                          NA

REPORT AGGREGATE(my_quantity USING my_aggmap)

                AGGREGATE(MY_QUANTITY USING
LETTER                   MY_AGGMAP)
-------------- ------------------------------
A                                       10.00
B                                      100.00

Syntax

MAINTAIN partition-template ADD TO PARTITION partition valuelist

Parameters

partition-template

A text expression that is the name of a previously-defined partition template object.

ADD TO PARTITION

Specifies that values are to be added to the partition.

partition

A text expression that is the name of a previously-defined partition in the partition template specified by partition-template.

valuelist

Text literals or a TEXT or ID expression specifying the values to be added. When it is a TEXT expression, each element (line) is treated as a separate value. The values in the expression are added exactly as they are typed.

For a concat dimension, you can specify a value of the concat dimension, or the name of a component dimension and a value or position of that dimension. You can use the values of a dimension surrogate as the values of value.

Note that you cannot partition along an INTEGER dimension.

TO

Indicates a range of values.

Examples

For an example of adding values to a partition, see Example 10-48, "Adding and Deleting Partition Values".

Syntax

MAINTAIN dimension DELETE limit-clause

Parameters

dimension

A non-concat dimension, already defined in an attached analytic workspace, whose values are to be deleted.

Note:

You cannot use a dimension surrogate as the dimension argument of a MAINTAIN DELETE command. However, you can use a dimension surrogate as a value within the limit-clause parameter.

limit-clause

Specifies the values to delete from the dimension.

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

Usage Notes

Deleting Temporary Calculated Members From Dimensions

When you use a MAINTAIN DELETE statement to delete a temporary calculated member, Oracle OLAP:

1. Deletes the member from the dimension.

2. Removes the calculation from all aggmap objects that currently contain the corresponding calculation.

Deleting Values in an Integer Dimension

When you use MAINTAIN to delete values in an INTEGER dimension, the values are renumbered to keep the normal sequence of INTEGER values (1, 2, 3, ...).

Examples

MAINTAIN city DELETE 'Omaha' 'Newark'

intvar = 5
MAINTAIN city DELETE FIRST intvar

MAINTAIN city DELETE population.c LT 75000

MAINTAIN prodid DELETE prodid(prodtype 'Three-Person Tent')

LIMIT quarter TO FIRST 1

Syntax

MAINTAIN composite DELETE comp-arg

where comp-arg is one of the following constructs:

valuelist

ALL

base-dim [valuelist]

boolean-expression

{TOP | BOTTOM} n BASEDON exp

{TOP | BOTTOM} n-percent PERCENTOF expression

LONGLIST

Parameters

composite

A composite whose values are to be deleted. When the composite is a named composite, it must be defined in an attached analytic workspace. When the composite is unnamed, it must have been used in defining an object in an attached analytic workspace.

Use the SPARSE keyword to refer to an unnamed composite (for example, SPARSE <market product>).

valuelist

Specifies one or more values to be deleted from the composite. The valuelist can be text constants or a text expression.

ALL

Deletes all composite values, but does not delete the definition of the composite itself.

base-dim [valuelist]

Deletes the values that include the listed values of a base dimension of the composite. The argument valuelist can be one value, a list of values, or a range of values (using TO to specify an inclusive range). You cannot use position numbers to specify a range of values. When you omit valuelist, Oracle OLAP deletes all values that include base-dim values currently in status.

boolean-expression

Deletes all composite values for which the Boolean expression is TRUE. The boolean-expression must be dimensioned by the dimension or the composite from which you the values deleted. When it has additional dimensions, their status must each be limited to one value.

TOP n BASEDON exp

BOTTOM n BASEDON exp

Deletes the top or bottom n values based on the highest (TOP) or lowest (BOTTOM) values in exp. The expression must be dimensioned by the composite from which you the values deleted. When it has additional dimensions, their status must each be limited to one value.

BOTTOM n-percent PERCENTOF expression

TOP n-percent PERCENTOF expression

Deletes values by finding the top or bottom performers based on a criterion. This construction sorts values and deletes them based on their contribution, by percentage, to an expression.

LONGLIST

Indicates a long list (up to 2,000 values) of individual values to delete. When there are fewer than 300 values, LONGLIST is not needed.

Syntax

MAINTAIN partition-template DELETE FROM PARTITION partition { dim-arg| comp-arg}

Parameters

partition-template

A text expression that is the name of a previously-defined partition template object.

partition

A text expression that is the name of a previously-defined partition in the partition template specified by partition-template.

DELETE FROM PARTITION

Specifies that values are to be deleted from the partition and from partitioned variables dimensioned using a partition template that includes the partition.

dim-args

Specifies the values of a dimension that to use when deleting partitioned variable values and when redefining the values that are in the partition You can use any of the constructs specified for the dim-arg argument in MAINTAIN DELETE dimension.

comp--args

Specifies the values of a composite to use when deleting partitioned variable values and when redefining the values that are in the partition You can use any of the constructs specified for the comp-arg argument in MAINTAIN DELETE composite.

Examples

DEFINE time DIMENSION TEXT
DEFINE time_parentrel RELATION time <time>
DEFINE product DIMENSION TEXT
DEFINE partition_sales_by_year PARTITION TEMPLATE <time product> -
   PARTITION BY LIST (time) -
    (PARTITION time_2004 VALUES ('2004', 'Dec2004', 'Jan2004', '31Dec2004', - 
          '01Dec2004', '31Jan2004', '01Jan2004') <TIME PRODUCT> -
     PARTITION time_2003 VALUES ('2003', 'Dec2003', 'Jan2003', '31Dec2003', -
         '01Dec2003', '31Jan2003', '01Jan2003') <TIME PRODUCT> -
     PARTITION time_2002 VALUES ('2002', 'Dec2002', 'Jan2002', '31Dec2002', -
         '01Dec2002', '31Jan2002', '01Jan2002') <TIME PRODUCT>)

"Create the new partition
CHGDFN partition_sales_by_year DEFINE -
         (PARTITION partition_month_years VALUES () <time product>)
"Delete the values for months and years from the partitions for years
MAINTAIN partition_sales_by_year DELETE FROM PARTITION time_2004 '2004'- 
        'Dec2004' 'Jan2004'
MAINTAIN partition_sales_by_year DELETE FROM PARTITION time_2003 '2003'-
        'Dec2003''Jan2003'
MAINTAIN partition_sales_by_year DELETE FROM PARTITION time_2002 '2002'-
       'Dec2002' 'Jan2002'
"Add the month and year values to the new partition for summary values
MAINTAIN partition_sales_by_year ADD TO PARTITION partition_month_years '2004'-
      'Dec2004' 'Jan2004' '2003' 'Dec2003''Jan2003' '2002' 'Dec2002' 'Jan2002'

DEFINE PARTITION_SALES_BY_YEAR PARTITION TEMPLATE <TIME PRODUCT> -
   PARTITION BY LIST (TIME) -
    (PARTITION TIME_2004 VALUES ('31Dec2004', '01Dec2004', '31Jan2004', -
       '01Jan2004') <TIME PRODUCT> -
     PARTITION TIME_2003 VALUES ('31Dec2003', '01Dec2003', '31Jan2003', -
       '01Jan2003') <TIME PRODUCT> -
     PARTITION TIME_2002 VALUES ('31Dec2002', '01Dec2002', '31Jan2002', -
       '01Jan2002') <TIME PRODUCT> -
     PARTITION PARTITION_MONTH_YEARS VALUES ('2004', 'Dec2004', 'Jan2004', -
       '2003', 'Dec2003', 'Jan2003', '2002', 'Dec2002', 'Jan2002')-
         <TIME PRODUCT>)

Syntax

MAINTAIN dimension MOVE limit-clause

Parameters

dimension

A non-concat dimension, already defined in an attached analytic workspace, whose values are to be entered or changed. The dimension must be of type TEXT, ID, or INTEGER. You cannot specify a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR.

limit-clause

Specifies the values you want to move.

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

Usage Notes

What Effects Performance of MAINTAIN MOVE

The performance cost of issuing a MAINTAIN MOVE command is dependent on the length of the dimension, not on the number of values being moved. Consequently, when moving values in a large dimension, do not issue several MAINTAIN MOVE statements one after another; instead, set up the dimension status to what you want and then issue a single MAINTAIN MOVE command.

Reordering or Sorting Values within a Dimension

When you want to reorder or sort dimension values using a MAINTAIN MOVE statement, to specify that you want to use all of the dimension values that are in status, use the STATUS keyword of the limit-clause argument of LIMIT (using values) command rather than a VALUES command.

Examples

MAINTAIN city MOVE 'Houston' AFTER 5

textvar = 'Seattle'
MAINTAIN city MOVE textvar LAST

MAINTAIN reg.dist.ccdim MOVE <district: 'Denver'> AFTER <region: 'West'>

MAINTAIN reg.dist.ccdim MOVE <district: 'Denver'> AFTER <district: 1>

MAINTAIN reg.dist.ccdim MOVE <district: 'Dallas'> AFTER 3

Syntax

MAINTAIN partition-template MOVE TO PARTITION partition value [TO value]

Parameters

partition-template

A text expression that is the name of a previously-defined partition template object.

MOVE TO PARTITION

Specifies that values are to be added to the partition or moved from one partition to another.

partition

A text expression that is the name of a previously-defined partition in the partition template specified by partition-template.

value

Specifies one or more values of a previously-populated dimension or composite. You can specify these values as:

• A literal value.

• An expression whose value is a dimension value.

• For all dimensions except NUMBER dimensions, an INTEGER expression whose value represents the position of a dimension value.

• A valueset; or a LIMIT function.

For a concat dimension, you can specify a value of the concat dimension, or the name of a component dimension and a value or position of that dimension. You can use the values of a dimension surrogate as the values of value.

TO

Indicates a range of values.

Examples

DEFINE PARTITION_SALES_BY_YEAR PARTITION TEMPLATE <TIME PRODUCT> -
   PARTITION BY LIST (TIME) -
    (PARTITION TIME_2004 VALUES () <TIME PRODUCT> -
     PARTITION TIME_2003 VALUES () <TIME PRODUCT> -
     PARTITION TIME_2002 VALUES () <TIME PRODUCT>)

DEFINE PARTITION_SALES_BY_YEAR PARTITION TEMPLATE <TIME PRODUCT> - 
    PARTITION BY LIST (TIME) -
    (PARTITION TIME_2004 VALUES -
('2004','Dec2004','Jan2004', 31Dec2004',01Dec2004','31Jan2004','01Jan2004')-
     PARTITION TIME_2003 VALUES -
('2003','Dec2003','Jan2003', 31Dec2003',01Dec2003','31Jan2003','01Jan2003')-
      PARTITION TIME_2002 VALUES -
                     ('2002','Dec2002','Jan2002', 31Dec2002',01Dec2002','31Jan2002','01Jan2002'))