| Oracle® OLAP DML Reference 10g Release 1 (10.1) Part Number B10339-02 |
|
|
View PDF |
| Oracle® OLAP DML Reference 10g Release 1 (10.1) Part Number B10339-02 |
|
|
View PDF |
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
Using the TRIGGER command, you can make the MAINTAIN command an event that automatically executes an OLAP DML program. See "Trigger Programs" for more information.
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.
You cannot perform maintenance on a dimension when a PERMIT MAINTAIN command 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.)
When the dimension has the NTEXT data type and an argument that represents a dimension value has the TEXT data type, MAINTAIN converts the argument value to NTEXT. Similarly, when the dimension has the TEXT data type and an argument that represents a dimension value has the NTEXT data type, the LIMIT command converts the argument value to TEXT; however, in this case, the conversion can result in data loss when the NTEXT value cannot be represented in the database character set.
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.
Before you can maintain dimensions in an analytic workspace that is attached in multiwriter mode, you must first acquire the dimension using the ACQUIRE command.
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
You cannot use the MAINTAIN command 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.
A concat dimension contains the values of its component dimensions. You do not directly add, merge, or delete the values of a concat dimension with the MAINTAIN command. 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.
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:
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
A non-concat dimension, already defined in an attached analytic workspace.
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>).
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.
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 integers to an INTEGER dimension, that quantity of integers will be added before or at the end of any existing integers (depending on your specification), and all the integers 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 command.
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 integers to an INTEGER dimension, that quantity of integers will be added before or after the integer position you specify, and the integers 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 command.
Notes
When you use MAINTAIN to add values in an integer dimension, the values are renumbered to keep the normal sequence of integers (1, 2, 3, ...).
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 16-35 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 16-36 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>
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
A non-concat dimension, already defined in an attached analytic workspace.
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 DATEORDER.
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.
Specifies a number of periods to add at the beginning or end of an existing list of dimension values.
Examples
Example 16-37 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'
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 it's 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
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 indicates maintenance of a temporary calculated member.
Specifies the name of the temporary calculated member.
Indicates that you are defining a new calculated member.
A text expression that specifies the calculation used as a dynamic model to calculate custom member values. (See SET for more information about model equations.)
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.
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
Indicates that the calculation is a time-series function (see "Time-Series Functions").
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.
Indicates that the calculated temporary member is added only to the aggmaps identified by 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.
Indicates that the temporary calculated member is added only to the variables identified by 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. |
Indicates that the temporary calculated member is added only to those aggmap objects whose aggregation specification contains a RELATION command for the relation specified by relation.
A text expression that specifies the name of the relation for which a temporary calculated member should be added.
Notes
Once you have added a temporary calculated member using the MAINTAIN command, 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 16-38 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
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
A text expression that is the name of a previously-defined partition template object.
Specifies that values are to be added to the partition.
A text expression that is the name of a previously-defined partition in the partition template specified by partition-template.
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.
Indicates a range of values.
Examples
For an example of adding values to a partition, see Example 16-44, "Adding and Deleting Partition Values".
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:
The MAINTAIN command with the DELETE keyword deletes dimension members from non-concat dimensions.
|
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
MAINTAIN dimension DELETE dim-arg
where dim-arg is one of the following constructs:
value [[TO] value]
ALL
rel-dim [valuelist]
{FIRST | LAST} n
n PERIODS {FIRST | LAST}
boolean-expression
{BOTTOM | TOP} n BASEDON exp
LONGLIST
NTH n
{BOTTOM | TOP} n-percent PERCENTOF expression
NOCONVERT nonconarg
POSLIST poslistarg
family-phrase
valueset
Arguments
A non-concat dimension, already defined in an attached analytic workspace, whose values are to be deleted.
Specifies one value, a list of values, or a range of values (using TO to specify an inclusive range) to be deleted from the values of a dimension. For value you can specify an existing value, a text expression whose value is an existing value, a valueset (containing one or more dimension names), or (except for a NUMBER dimension) an integer expression whose value represents the position of a dimension value. For dimensions of type DAY, WEEK, MONTH, QUARTER, and YEAR, value can also be a DATE expression or a text expression that represents a date; Oracle OLAP deletes the time period within which the date falls. For dimensions of type DAY, WEEK, MONTH, QUARTER, and YEAR, you can delete values only from the beginning or the end of the existing list of values. When you delete a certain quantity of integers from an INTEGER dimension, the integers in the whole of the resulting series will be automatically adjusted into simple numerical order.
Deletes all dimension values. This does not delete the definition of the dimension or composite itself.
Deletes the dimension values that are related to the listed values of a related dimension. The valuelist can be one value, a list of values, or a range of values (using TO to specify an inclusive range). When you omit valuelist, all values related to the current status of rel-dim are deleted.
For dimensions of type DAY, WEEK, MONTH, QUARTER, and YEAR, the related values must be deleted from the beginning or the end of the existing list. For example, assume that the first values in the month dimension are the months of 1995. In this case, you can maintain month by specifying year as the rel-dim and Yr95 as the valuelist of years.
Instead of specifying a dimension name for rel-dim, you can specify the name of the relation. This enables you to choose which relation is used when there is more than one. You cannot supply a valuelist when you specify the name of a relation.
Every dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR is related to all other dimensions of those type through an implicit relation. When you delete values of one DAY, WEEK, MONTH, QUARTER, or YEAR dimension by specifying another dimension with one of those types as the rel-dim, Oracle OLAP uses the implicit relation by default. However, when an explicit relation is defined between the two DAY, WEEK, MONTH, QUARTER, or YEAR dimensions, you can override the default by specifying the name of the explicit relation as the rel-dim.
Deletes the first or last n dimension values in the list; n can be any numeric expression. DECIMAL and SHORTDECIMAL values are truncated to integers. When you delete a certain quantity of integers from an INTEGER dimension, the integers in the whole of the resulting series will be automatically adjusted into simple numerical order.
These arguments are only valid for dimensions of type DAY, WEEK, MONTH, QUARTER, or YEAR dimension. Specifying this argument deletes the first or last n values in the list; n can be any numeric expression. DECIMAL and SHORTDECIMAL values are truncated to integers. The n PERIODS FIRST and n PERIODS LAST arguments have the same effect as, but are faster than, the FIRST n and LAST n arguments.
Deletes all dimension values for which the Boolean expression is TRUE. The boolean-expression must be dimensioned by the dimension from which you the values deleted. When it has additional dimensions, their status must each be limited to one value. When you use the boolean-expression argument with a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR, values that meet the criterion will be deleted only when they are at the beginning or the end of the list of dimension values.
Deletes the top or bottom n values of the dimension based on the highest (TOP) or lowest (BOTTOM) values in exp. The 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. When you use TOP n BASEDON exp or BOTTOM n BASEDON exp for a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR, values that meet the criterion will be deleted only when they are at the beginning or end of the list of dimension values.
Indicates a long list (up to 2,000 values) of individual dimension values to delete. When there are fewer than 300 values, LONGLIST is not needed.
Deletes the nth value in a dimension's full set of values.
Deletes values of a dimension 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. For example:
MAINTAIN product DELETE TOP 30 PERCENTOF TOTAL(sales, product)
will sort products in descending order by each product's contribution to TOTAL(sales, product) and then deletes the product, starting from the top, until the cumulative total of sales by product reaches or exceeds 30 percent of all sales.
Deletes a dimension value based on its numeric position. NOCONVERT takes an argument whose values are the numeric positions to be deleted in the maintained dimension. See the explanation of LIMIT command (NOCONVERT).
Deletes a dimension value based on its numeric position. POSLIST takes a text argument whose values are the numeric positions to be deleted in the maintained dimension. See the explanation of LIMIT command (using POSLIST).
Deletes a dimension value based on its family tree. See the explanation of LIMIT command (using parent relation).
Deletes the values in the dimension that match the values in the valueset.
Notes
When you use a MAINTAIN DELETE statement to delete a temporary calculated member, Oracle OLAP:
Deletes the member from the dimension.
Removes the calculation from all aggmap objects that currently contain the corresponding calculation.
You cannot use a dimension surrogate as the dimension argument of a MAINTAIN DELETE command. However, you can use a dimension surrogate a value within the command.
When you use MAINTAIN to delete values in an integer dimension, the values are renumbered to keep the normal sequence of integers (1, 2, 3, ...).
Examples
Example 16-39 Deleting Dimension Values by Value
This statement deletes Omaha and Newark from the values for city.
MAINTAIN city DELETE 'Omaha' 'Newark'
Example 16-40 Deleting the First Five Values of a Dimension
In this example, you use the INTEGER variable intvar to remove the first five cities from the dimension city.
intvar = 5 MAINTAIN city DELETE FIRST intvar
Example 16-41 Deleting Dimension Values Based on a Boolean Expression
Here you remove from city all those cities with a population of less than 75,000 people. You use the variable population.c, which contains the population for each city.
MAINTAIN city DELETE population.c LT 75000
Example 16-42 Deleting Dimension Values Using Surrogate to Specify Values
Assume that prodid is a NUMBER dimension and prodtype is a TEXT dimension surrogate for prodid. Assume also that the values of prodid are 17, 40, and 56. The values of prodtype are Two-Person Tent, Three-person Tent, and Four-person Tent. The following statement deletes a value from prodid and from its surrogate.
MAINTAIN prodid DELETE prodid(prodtype 'Three-Person Tent')
Example 16-43 Deleting Related MONTH Values
In this example, you use the related dimension quarter to remove values of the dimension month. All months related to the values of quarter currently in the status are deleted.
LIMIT quarter TO FIRST 1
MAINTAIN month DELETE quarter
The MAINTAIN command with the DELETE keyword deletes dimension members from composites.
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
Arguments
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>).
Specifies one or more values to be deleted from the composite. The valuelist can be text constants or a text expression.
Deletes all composite values. This does not delete the definition of the composite itself.
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.
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.
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.
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.
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.
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
A text expression that is the name of a previously-defined partition template object.
A text expression that is the name of a previously-defined partition in the partition template specified by partition-template.
Specifies that values are to be deleted from the partition and from partitioned variables dimensioned using a partition template that includes the partition.
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.
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 16-44 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>)
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]
A non-concat dimension, already defined in an attached analytic workspace, whose values are to be entered or changed.
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>).
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.
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 16-45 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.
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:
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 value [TO value] {FIRST|LAST|BEFORE position|AFTER position}
Arguments
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.
Specifies one or more values of dimension. 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.
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.
Indicates a range of values.
Specify the position to which values will be moved. FIRST indicates that the values are to be moved to the beginning of the value list. LAST (the default) indicates that the values are to be moved to the end of the value list. When you are moving a certain quantity of integers in an INTEGER dimension, that quantity of integers will be moved to the beginning or to the end of the existing series of integers, and the integers in the whole of the resulting series will be automatically adjusted into simple numerical order.
Specify a position before or after which the dimension values are to be moved. 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 move a certain quantity of integers in an INTEGER dimension, then that quantity of integers moves before or after the integer position you specify, and the integers in the whole of the resulting series automatically adjust into simple numerical order.
For a concat dimension, you can specify as position a value of the concat dimension or the position of a value in a component dimension. See "Sorting Values".
Notes
You cannot use a dimension surrogate as the dimension argument of a MAINTAIN MOVE command. However, you can use a dimension surrogate values as a value to within the statement.
For example, assume that prodid is a NUMBER dimension and prodtype is a TEXT dimension surrogate for prodid. The values of prodid are 17, 40, and 56. Assume also that the values of prodtype are Two-Person Tent, Three-Person Tent, and Four-Person Tent. The following statement moves the last value to the first position in both the dimension and its surrogate.
MAINTAIN prodid MOVE prodid(prodtype 'Four-Person Tent') FIRST
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 16-48, "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 16-46 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 16-47 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 16-48 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 16-49 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
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
A text expression that is the name of a previously-defined partition template object.
Specifies that values are to be added to the partition or moved from one partition to another.
A text expression that is the name of a previously-defined partition in the partition template specified by partition-template.
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.
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.
Indicates a range of values.
Examples
Example 16-50 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'))
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
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.
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.
A text constant or a TEXT or ID expression that is the new spelling for the dimension value.
Examples
Example 16-51 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
|
 Copyright © 2003 Oracle Corporation All Rights Reserved. |
|