|Oracle9i OLAP Services Developer's Guide to the OLAP DML
Release 1 (9.0.1)
Part Number A86720-01
Populating OLAP DML Data Objects, 3 of 6
The first step in populating an analytic workspace is to store values in the analytic workspace's dimensions. The list of stored dimension values is called the dimension's default status list. When you first attach an analytic workspace, the default status list is the current status list of each dimension.
You can add, delete, merge, reposition, or change dimension values using the MAINTAIN command. Consequently, storing and manipulating the values of a dimension is called maintaining the dimension.
You can only maintain a dimension when you have permission to both maintain and read the dimension. Maintain permission is implicitly denied whenever read permission is restricted for a dimension, even when you specify maintain permission for the dimension.
By default, you have permission to both read and maintain dimensions. However, either or both of these permissions can be changed using the PERMIT command.
For more information on using the PERMIT command, see "Adding Security to an Analytic Workspace" and the topic for the PERMIT command in the OLAP DML Reference.
As outlined in the following table, using the MAINTAIN command sometimes affects dimension status.
|IF you use the MAINTAIN command with . . .||THEN . . .|
the ADD, DELETE, MERGE, or MOVE keyword and the current status of a dimension is not ALL,
the dimension's status is reset to ALL before it performs the requested maintenance.
a dimension that has a pushed status list (that is, a status list that was created using the PUSH commands),
that dimension's pushed status list is cleared, and popping that dimension has no effect.
For more information on popping and pushing values, see "Introducing Dimension Status" and the entries for the POP and PUSH commands in the OLAP DML Reference.
When you maintain a dimension, the objects that are dimensioned by it must be modified. If these objects are in memory, then they are modified immediately; if these objects are not in memory, then maintenance is deferred until they are loaded into memory.
In situations that involve a lot of dimension maintenance and a large update at the end, deferred maintenance can trigger errors. Examples are issuing a MAINTAIN DELETE ALL command, or performing a data load in which a large number of values is added to a dimension. Before starting such projects, load into memory the objects that are dimensioned by the dimension you are maintaining so that deferred maintenance is unnecessary. You can do this by using commands similar to the following, where the sample dimension is PRODUCT.
To add new values to the end of a dimension or composite, use the MAINTAIN command with the ADD keyword. The actual way that the values are added, and the arguments that you use vary depending on whether you are adding values to a non-time dimension, a time dimension, or a composite.
You can use the MAINTAIN command with the MERGE keyword as a quick way to make sure all dimension values on a separate list are included in a dimension. When you use this syntax, the new values from the list are automatically added and the duplicates are ignored. 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 time data types. However, because the ADD keyword provides a quick way of adding time dimension values, the MERGE keyword may not be as useful with time dimensions as with TEXT or ID dimensions.
You can use the MAINTAIN command with the ADD keywords to add values to a non-time dimension in the following ways:
This command adds ATLANTA at the beginning of the list of cities and inserts PEORIA after OMAHA.
Displaying the default status list for the CITY dimension shows that the new values have been added in the appropriate places in the list.
You can use the MAINTAIN command with the ADD keyword to add new values to time dimensions (that is, dimensions with the DAY, WEEK, MONTH, QUARTER, or YEAR data type). You can specify what values you want to add by specifying:
Regardless of how you specify the values, keep the following points in mind:
When you add a dimension value by specifying a DATE expression or a text value that represents a complete date, keep the following points in mind:
For more information on the valid input styles for dates, see the entry for the DATEORDER option in the OLAP DML Reference.
When you add a dimension value by specifying the values as text literals or TEXT or ID expressions (rather than as a date), keep the following points in mind:
<MTXT><YY>, then the value JAN99 represents the month January 1999.
For more information on the default formats for time dimensions, see the entry for the VNF command in the OLAP DML Reference.
Suppose you define a new time dimension, called QTR, with a data type of QUARTER, and you add dimension values for the quarters in 1998 and 1999. You must add only the first and last dimension values you want, and the intervening values will be filled in automatically.
To add the first and last quarters, you can specify any dates that fall within those quarters.
Displaying the default status list for the dimension shows the new dimension values.
When you are merging values into a dimension it is a good practice to update any relations that involve that dimension:
The exp argument specifies a dimensioned expression whose values you want to merge into the dimension; for example, the name of a dimensioned text variable that contains dimension values.
The RELATE relation phrase specifies the name of the relation that you want to update.
Note: The exp argument must be dimensioned and at least one of these dimensions must also be in the definition of the relation that is specified in the RELATE relation phrase.
For the complete syntax for the MAINTAIN command, see the entry for the command in the OLAP DML Reference. For information about explicitly updating relations, see "Assigning Values to Data Objects".
Suppose you want to define a composite, named COMP_PRODDIST, 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 commands.
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.
You can use the MAINTAIN command with the DELETE keyword to remove values from a dimension. Using the MAINTAIN command with DELETE keyword, you select the values that you want to delete in much the same way that you select values using the LIMIT command. You can select for deletion:
You delete values from a dimension with a time data type (that is, DAY, WEEK, MONTH, QUARTER, or YEAR) the same way that you delete values from any other dimension except that you can delete values only from the beginning or the end of the existing list of values.
Suppose that you want remove from CITY all those cities with a population of less than 75,000 people. Before you issue the command, the default status list for the CITY dimension contains the six values shown below.
You use the variable POPULATION.C, which contains the population for each city.
Assuming that only Lincoln and Peoria have populations of fewer than 75,000, the default status list of the CITY dimension now contains the following values.
You can use the MAINTAIN command with the DELETE keyword to delete values from a conjoint dimension.
You can also delete values from a conjoint dimension by using the MAINTAIN command directly on the base dimension of the conjoint dimension. When you delete a value from the base dimension, any values associated with that base dimension value are deleted from the conjoint dimension.
Suppose you have a conjoint dimension named PROD_DIST with the base dimensions of PRODUCT and DISTRICT. To delete the value
<'SNOWSHOES' 'ATLANTA'> from that conjoint dimension, you would use the following command.
For dimensions that have a non-time data type, you can use the MAINTAIN command with the MOVE keyword to change the position of one or more values in a dimension list. You cannot change the position of a value in a time dimension or in a composite.
When you want to store the dimension values in alphabetical order, you can first use the SORT command to temporarily sort the values, and then use the MAINTAIN command to store the values in the sorted order.
Use the TEXT variable TEXTVAR to move SEATTLE to the end of the list of cities.
You can store the values of a dimension in sorted order by taking the following actions:
Note: To sort the values alphabetically, sort by the dimension itself.
Note: To sort the values alphabetically, sort by the dimension itself.
Note: You cannot use the MAINTAIN command to save the sorted order as the permanent order of a time dimension. The values of a time dimension must be stored in increasing chronological order.
For more information on using the SORT command, see the entry for the command in the OLAP DML Reference.
Suppose that the default status list for the CITY dimension contains the following values.
The following commands sort the values of CITY in alphabetical order and then store the values in that order.
The default status list of CITY reflects the new sorted order.
Both composites and conjoint dimensions are lists of dimension-value combinations in which one value is taken from each of the dimensions on which the composite or conjoint dimension is based. Composites and conjoint dimensions differ in the way that they are maintained.
Composites are internal structures that are automatically maintained. Consequently, the simplest way to maintain a composite is to merely maintain its base dimensions and let the values in the composite be maintained automatically.
In most cases, it is not necessary to do anything to maintain composites. However, if you want to have a very fine degree of control, you may have to explicitly maintain the composite. In this case, you can use the MAINTAIN command to add, delete, and merge values.
Conjoint dimensions, unlike composites, are actual dimensions that you must explicitly maintain. Conjoint dimensions are not automatically maintained. In programs, you use the MAINTAIN command to maintain the values in a conjoint dimension.