MAINTAIN

The MAINTAIN command enters and maintains the values of dimensions, composites, and partition template objects.

Note:

You can also issue a MAINTAIN statement for a surrogate dimension that has a Maintain trigger. In this case, Oracle OALP only executes the Maintain trigger program; no other action occurs. See "Trigger Programs" for more information for more information. Issuing a MAINTAIN statement for a surrogate dimension that does not have a Maintain trigger, returns an error.

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:

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

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 since 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 need 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 need 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 need 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.

MAINTAIN ADD

The MAINTAIN command with the ADD keyword adds new TEXT, ID, and INTEGER values to a non-concat dimension, composite, or partition; or adds a new temporary calculated member to a dimension.

Note:

You can also issue a MAINTAIN ADD for TEXT, ID, and INTEGER Values statement for a surrogate dimension that has a Maintain trigger. In this case, Oracle OALP only executes the Maintain trigger program one time for each value; no other action occurs. See "Trigger Programs" for more information for more information. Issuing a MAINTAIN statement for a surrogate dimension that does not have a Maintain trigger, returns an error.

Syntax

The syntax for using the MAINTAIN command with the ADD keyword depends on the type of the object being maintained and whether you are adding a permanent or temporary member.

For this reason, the following separate entries are provided for MAINTAIN ADD:

MAINTAIN ADD for TEXT, ID, and INTEGER Values
MAINTAIN ADD for DAY, WEEK, MONTH, QUARTER, and YEAR Values
MAINTAIN ADD SESSION
MAINTAIN ADD TO PARTITION

MAINTAIN ADD for TEXT, ID, and INTEGER Values

The MAINTAIN command with the ADD keyword adds new TEXT, ID, or INTEGER values to a non-concat dimension or composite.

Note:

You can also issue this MAINTAIN ADD statement for a surrogate dimension that has a Maintain trigger. In this case, Oracle OALP only executes the Maintain trigger program one time for each value in valuelist; no other action occurs. See "Trigger Programs" for more information for more information. Issuing a MAINTAIN statement for a surrogate dimension that does not have a Maintain trigger, returns an error.

Syntax

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

Arguments

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 will be added. FIRST indicates that the new values will be inserted before any existing values. LAST indicates that new values will be added at the end of 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 will be added before or at the end of any existing INTEGER values (depending on your specification), and all the INTEGER values in the resulting series will be automatically adjusted into simple numerical 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 will be added before or after the INTEGER position you specify, and the INTEGER values in the whole of the resulting series will be automatically adjusted into simple numerical 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.

Notes

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 will automatically add the value to the appropriate composite.

Examples

Example 10-39 Adding Values to a TEXT Dimension

This statement adds Omaha and Seattle to the end of the dimension values for the city dimension.

MAINTAIN city ADD 'Omaha' 'Seattle'

This statement adds Atlanta at the beginning of the list of cities and inserts Peoria after Omaha.

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

Here the value of the TEXT variable textvar is inserted before the fifth dimension value of city. When you assign the value Columbus to textvar, you must make sure it is in mixed case, because you want the dimension value to be in mixed case.

textvar = 'Columbus'
MAINTAIN city ADD textvar BEFORE 5

Example 10-40 Adding Values to a Conjoint Dimension

The following is an example of adding values to a conjoint dimension.

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

You can also assign a value of a base dimension to a text variable and use the name of the variable inside the angle brackets.

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

MAINTAIN ADD for DAY, WEEK, MONTH, QUARTER, and YEAR Values

The MAINTAIN command with the ADD keyword adds new values to a dimension of type DAY, WEEK, MONTH, QUARTER, and YEAR.

Syntax

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

Arguments

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 a number of periods to add at the beginning or end of an existing list of dimension values.

Examples

Example 10-41 Adding Values to Dimension of Type QUARTER

In this example you define a new QUARTER dimension, called qtr, and you add dimension values for the quarters in 1998 and 1999. You only need to add the first and last dimension values you want. Oracle OLAP fills in the intervening values. To add the first and last quarters, you can specify any dates that fall within those quarters.

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

MAINTAIN ADD SESSION

The MAINTAIN command with the ADD SESSION keywords adds a temporary calculated member to a dimension and applies it to the specified objects; or applies a previously-defined calculated member to the specified objects. The calculated member and its definition do not persist from session to session; both are deleted at the end of the session in which they are created.

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

Arguments

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 more than one dimension member, 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 more than one dimension name, 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 more than one aggmap object, 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 more than one variable name, 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.

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

Example 10-42 Creating Calculated Dimension Members with Aggregated Values

Assume that an analytic workspace has a dimension named letter and a variable named my_quantity with the following definitions and permanent values.

DEFINE letter DIMENSION TEXT
DEFINE my_quantity VARIABLE DECIMAL <letter>

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

You can define temporary dimension members for the letter dimension and aggregate data in my_quantity for those members following these steps:

Determine the aggregation that you want to perform and define and populate the necessary supporting objects.

Create an empty child-parent relation for the letter dimension

DEFINE letter.parentrel RELATION letter <letter>

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

Define a simple model to be used to calculate values associated with the letter dimension
```
DEFINE my_model MODEL
MODEL 
  DIMENSION letter
 END
```

Define and compile a simple aggmap to be used to calculate my_quantity values associated with the letter dimension

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

COMPILE my_aggmap

Define a variable to contain the definition for the custom aggregation, This new variable will be the same as my_quantity except that has my_aggmap as its default aggmap.

DEFINE my_quantity_definition VARIABLE DECIMAL <letter>

CONSIDER my_quantity_definition
PROPERTY '$AGGMAP' 'my_aggmap'

REPORT my_quantity_definition

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

Add temporary members to the letter dimension and specify how variable values for those members are to be calculated.

MAINTAIN letter ADD SESSION 'C' = 'A' * 'B'
MAINTAIN letter ADD SESSION 'D' = AGGREGATION('A', 'B') -
    APPLY TO AGGMAP my_aggmap
MAINTAIN letter ADD SESSION 'E' = 'C' + 'D' -
    APPLY WITH RELATION letter.parentrel
MAINTAIN letter ADD SESSION 'F' = 10 * 'E' -
    APPLY FOR VARIABLE my_quantity_definition

A report of the letter dimension shows the new dimension members.

LETTER
--------------
A
B
C
D
E
F

Aggregate my_quantity using the aggmap object named my_aggmap.

REPORT AGGREGATE(my_quantity USING my_aggmap)

                AGGREGATE(MY_QUANTITY USING
LETTER                   MY_AGGMAP)
-------------- ------------------------------
A                                       10.00
B                                      100.00
C                                    1,000.00
D                                      110.00
E                                    1,110.00
F                                   11,100.00

Assume now that you issue the UPDATE and COMMIT statements to update and commit your analytic workspace. Then you detach the analytic workspace and end your session.

Later you start a new session and attach the same analytic workspace. When you ask for a description of the analytic workspace you can see that all of the objects that were in the analytic workspace when the UPDATE was issued still exist.

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>

However, when you report on the letter dimension and the my_quantity variable, the temporary dimension members that you added in the previous session and their related values in the my_quantity variable do not exist.

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

MAINTAIN ADD TO PARTITION

The MAINTAIN ADD TO PARTITION statement adds previously-populated dimension or composite values to a partition of a previously-defined partition template object.

Tip:

Use MAINTAIN MOVE TO PARTITION to maintain partition values when you have already populated a partitioned variable.

Syntax

MAINTAIN partition-template ADD TO PARTITION partition valuelist

Arguments

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

MAINTAIN DELETE

The MAINTAIN command with the DELETE keyword deletes members from non-concat dimensions and composites; or deletes the data of previously-partitioned variables from one partition to another as it changes the dimension or composite values defined for a partition in the partition template which the variables are dimensioned.

Note:

You can also issue a MAINTAIN DELETE statement for a surrogate dimension that has a Maintain trigger. In this case, Oracle OALP only executes the Maintain trigger program; no other action occurs. See "Trigger Programs" for more information for more information. Issuing a MAINTAIN statement for a surrogate dimension that does not have a Maintain trigger, returns an error.

Syntax

The syntax for using the DELETE keyword of the MAINTAIN command to delete members varies depending on the type of object from which you are deleting the members. For this reason, the following separate entries are provided for MAINTAIN DELETE:

MAINTAIN DELETE dimension
MAINTAIN DELETE composite
MAINTAIN DELETE FROM PARTITION

MAINTAIN DELETE dimension

The MAINTAIN command with the DELETE keyword deletes dimension members from non-concat dimensions.

Note:

MAINTAIN DELETE composite

The MAINTAIN command with the DELETE keyword deletes dimension members from composites.

MAINTAIN DELETE FROM PARTITION

The MAINTAIN DELETE FROM PARTITION command deletes the data of previously-partitioned variables from one partition to another as it changes the dimension or composite values defined for a partition in the partition template which the variables are dimensioned.

Tip:

Use MAINTAIN MOVE TO PARTITION to maintain partition values when you have already populated a partitioned variable.

Syntax

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

Arguments

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

Example 10-48 Adding and Deleting Partition Values

Assume that you have defined the following objects in your analytic workspace. on

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

Assume that instead of having all sales values dimensioned levels by all time values of a year in a partition, you want to have partitions by days and by summary time values (month and year). To change partition_sales_by_year to reflect this new partitioning scheme, you issue the following statements.

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

The partition_sales_by_year partition template object now has the following definition.

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

MAINTAIN MERGE

The MAINTAIN command with the MERGE keyword provides a quick way to make sure all dimension or composite values on a separate list are included in a non-concat dimension or composite. Using the MERGE keyword with the MAINTAIN command automatically adds the new values from the list and ignores the duplicates. This method of entering dimension values can save a significant amount of time when you have a large number of values to enter.

You can use MERGE with dimensions of any data type, including DAY, WEEK, MONTH, QUARTER, and YEAR dimensions. However, since Oracle OLAP provides a quick way of adding values of DAY, WEEK, MONTH, QUARTER, and YEAR dimensions through the ADD keyword, the MERGE keyword may not be as useful with DAY, WEEK, MONTH, QUARTER, and YEAR dimensions as it is with TEXT or ID dimensions.

At the same time as you are merging values into a dimension, you can also update a relation that involves that dimension.

Note:

You can also issue this MAINTAIN MERGE statement for a surrogate dimension that has a Maintain trigger. In this case, Oracle OALP only executes the Maintain trigger program one time for each value in exp; no other action occurs. See "Trigger Programs" for more information for more information. Issuing a MAINTAIN statement for a surrogate dimension that does not have a Maintain trigger, returns an error.

Syntax

MAINTAIN dimension|composite MERGE exp [RELATE relation]

dimension: A non-concat dimension, already defined in an attached analytic workspace, whose values are to be entered or changed.
composite: A composite whose values are to be added, deleted, or merged. 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>).
exp: Specifies an expression whose values are to be merged with dimension; for example, the name of a dimensioned text variable that contains dimension values, or a single-cell text variable whose value is a multiline list of dimension values. MAINTAIN MERGE ignores any NAs in exp. When dimension is an INTEGER dimension, then exp specifies the number of values that you want in the dimension. When the actual total is less, MAINTAIN MERGE adds enough values to reach the specified total. For example, when an INTEGER dimension has 10 positions, MERGE 5 has no effect; but MERGE 15 would add 5 values.
RELATE relation: Specifies a relation to be updated as new values from exp are merged into dimension. At least one of the dimensions of exp must also appear in the definition of relation. When exp is a single-cell value, you cannot use the RELATE phrase.

Examples

Example 10-49 Using the MERGE Keyword with Composites

Suppose you want to define a composite that is made up of all combinations of the first three values of the product dimension and the first five values of the district dimension. You can efficiently include all 15 values with the following statements.

DEFINE comp_proddist COMPOSITE <product district>
LIMIT product TO FIRST 3
LIMIT district TO FIRST 5
MAINTAIN comp_proddist MERGE <product district>

This method works with conjoint dimensions as well.

MAINTAIN MOVE

A MAINTAIN command with the MOVE keyword has different effects depending on the object on which it operates:

When maintaining a dimension, MAINTAIN MOVE changes the position of one or more values in a non-concat dimension or a dimension of type TEXT, ID, or INTEGER or adds previously-populated dimension or composite values to a partition
When maintaining a partition, MAINTAIN MOVE moves the data of a previously-partitioned variables from one partition to another as it changes the dimension or composite values defined for a partition in the partition template which the variables are dimensioned.

Note:

You can also issue a MAINTAIN MOVE dimension value statement for a surrogate dimension that has a Maintain trigger. In this case, Oracle OALP only executes the Maintain trigger program; no other action occurs. See "Trigger Programs" for more information for more information. Issuing a MAINTAIN statement for a surrogate dimension that does not have a Maintain trigger, returns an error.

Syntax

The syntax for using the MAINTAIN command with the MOVE keyword depends on the type of the object being maintained.

For this reason, the following separate entries are provided for MAINTAIN MOVE:

MAINTAIN MOVE dimension value
MAINTAIN MOVE TO PARTITION

MAINTAIN MOVE dimension value

A simple MAINTAIN MOVE statement changes the position of one or more values in a non-concat dimension or a dimension of type TEXT, ID, or INTEGER. You cannot use the MOVE keyword of the MAINTAIN command with composites or with dimensions of type DAY, WEEK, MONTH, QUARTER, or YEAR.

Note:

You can also issue a MAINTAIN MOVE statement for a surrogate dimension that has a Maintain trigger. In this case, Oracle OALP only executes the Maintain trigger program; no other action occurs. See "Trigger Programs" for more information for more information. Issuing a MAINTAIN statement for a surrogate dimension that does not have a Maintain trigger, returns an error.

Syntax

MAINTAIN dimension MOVE limit-clause

Arguments

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.

Notes

Sorting Values

You can sort the values of a dimension with the following statements.

LIMIT dimension TO ALL
SORT dimension A sort-criterion
MAINTAIN dimension MOVE VALUES(dimension) FIRST

The sorting criterion can be any expression you choose (see the SORT command). To sort the dimension alphabetically, use the dimension itself as the criterion (see Example 10-52, "Moving Dimension Values into Sorted Order". After using the SORT command to sort the dimension values, you use the MAINTAIN command to make the sorted order permanent.

You can use the SORT command for a temporary sort of the values of a dimension with a type of DAY, WEEK, MONTH, QUARTER, or YEAR. For example, you might want to use the sorted order in a report. However, you cannot use the MAINTAIN command to save the sorted order as the permanent order of a dimension with the type of DAY, WEEK, MONTH, QUARTER, or YEAR. The values of these types of dimensions must be stored in increasing chronological order.

Examples

Example 10-50 Moving a Dimension Value to a Specific Position

This statement moves the position of the city Houston to the position following the fifth dimension value.

MAINTAIN city MOVE 'Houston' AFTER 5

Example 10-51 Moving a Dimension Value to the End of the Status List

In this example, you use the TEXT variable textvar to move Seattle to the end of the list of cities.

textvar = 'Seattle'
MAINTAIN city MOVE textvar LAST

Example 10-52 Moving Dimension Values into Sorted Order

Here you put the values of city in alphabetical order.

SORT city A city
MAINTAIN city MOVE VALUES(city) FIRST

Example 10-53 Moving Values of Concat Dimensions

The following statement moves the reg.dist.ccdim concat dimension value <district: 'Denver'> after the concat dimension value <region: 'West'>.

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

The following statement moves the concat dimension value <district: 'Denver'> after the position that corresponds to the first value of the component district dimension. If the first value in the status of district is Atlanta, then <district: 'Denver'> moves after the value <district: 'Atlanta'> in the concat dimension.

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

The following statement moves the concat dimension value <district: 'Dallas'> after the third value of the concat dimension.

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

MAINTAIN MOVE TO PARTITION

A MAINTAIN MOVE TO PARTITION statement combines both add and move capabilities. You can use a MAINTAIN MOVE TO PARTITION statement to:

Add previously-populated dimension or composite values to a partition in the same manner as MAINTAIN ADD TO PARTITION
Change the dimension or composite values defined for a partition in the partition template by which the variables are dimensioned and, at the same time, move the data of a previously-partitioned variables dimensioned by those dimensions and composites from one partition to another.

Syntax

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

Arguments

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.

TO

Indicates a range of values.

Examples

Example 10-54 Specifying the Values of a Partition Using Valuesets

Assume that you have defined a partition template object with the following definition that does not specify the actual dimension values for each partition.

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

To specify the values of each partition using valuesets, you take the following steps:

Define a valueset for each year's values.

DEFINE vs_2004 VALUESET time
LIMIT vs_2004 to '01Dec2004' '31Dec2004' '01Jan2004''31Jan2004' -
     'Jan2004' 'Dec2004' '2004'
DEFINE vs_2003 VALUESET time
LIMIT vs_2003 to '01Dec2003' '31Dec2003' '01Jan2003''31Jan2003' -
     'Jan2003' 'Dec2003' '2003'
DEFINE vs_2002 VALUESET time
LIMIT vs_2002 to '01Dec2002' '31Dec2002' '01Jan2002''31Jan2002' -
     'Jan2002' 'Dec2002' '2002'

Using MAINTAIN MOVE statements, specify values for the partitions of the partition template.

MAINTAIN partition_sales_by_year MOVE TO PARTITION time_2004 vs_2004
MAINTAIN partition_sales_by_year MOVE TO PARTITION time_2003 vs_2003
MAINTAIN partition_sales_by_year MOVE TO PARTITION time_2002 vs_2002

When you issue a DESCRIBE statement, you can see that the description of the partition_sales_by_year partition template now includes the appropriate values of time in each partition definition.

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

MAINTAIN RENAME

The MAINTAIN command with the RENAME keyword changes the spelling of one or more dimension values. You cannot use RENAME keyword with a composite or with dimensions of type INTEGER, DAY, WEEK, MONTH, QUARTER, or YEAR.

Note:

You can also issue a MAINTAIN RENAME statement for a surrogate dimension that has a Maintain trigger. In this case, Oracle OALP only executes the Maintain trigger program; no other action occurs. See "Trigger Programs" for more information for more information. Issuing a MAINTAIN statement for a surrogate dimension that does not have a Maintain trigger, returns an error.

Syntax

MAINTAIN dimension RENAME {value new-value}...

Arguments

dimension: A non-concat dimension of type TEXT or ID that is already defined in an attached analytic workspace and whose values are to be renamed. You cannot specify a dimension of type INTEGER, DAY, WEEK, MONTH, QUARTER, or YEAR.
value: Specifies an existing dimension value to be renamed. You can specify a dimension value, a character expression whose value is a dimension value, or an INTEGER expression whose value represents the position of a dimension value.
new-value: A text constant or a TEXT or ID expression that is the new spelling for the dimension value.

Examples

Example 10-55 Renaming Values of a TEXT Dimension

This statement changes the spelling of the cities Chic and Bost to Chicago and Boston.

MAINTAIN city RENAME 'Chic' 'Chicago' 'Bost' 'Boston'

In this example you use the TEXT variable textvar to change the second city to Atlanta.

textvar = 'Atlanta'
MAINTAIN city RENAME 2 textvar