Populating Workspace Data Objects, 3 of 5

Maintaining Dimensions and Composites

The first step in populating an analytic workspace is to store values in analytic workspace dimensions. The list of stored dimension values is called the default status list of the dimension. When you first attach an analytic workspace, the default status list is the current status list of each dimension.

Using the MAINTAIN command, you can add, delete, merge, reposition, or change simple, composite, or conjoint dimension values, and you can reposition concat dimension values. Storing and manipulating the values of a dimension is called maintaining the dimension.

How Maintaining a Dimension Affects Dimension Status

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 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),	the pushed status list of the dimension is cleared, and popping that dimension has no effect.

For more information on popping and pushing dimension status, see "Introducing Dimension Status".

Avoiding Deferred Maintenance

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 use that dimension so that deferred maintenance is unnecessary. You can do this by using commands similar to the following, where the sample dimension is product.

LIMIT NAME TO OBJ(ISBY product) 
LOAD &values(NAME) 
MAINTAIN product ADD ...

Adding Values to Dimensions

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 dimension or a composite.

You do not add values directly to a concat dimension. Instead, if you add a value to a base dimension of the concat dimension, then Oracle OLAP automatically adds the value to the concat dimension. Similarly, you do not add values to a dimension surrogate, but if you add a value to the dimension of the dimension surrogate, then you can add a surrogate for the new value to the dimension surrogate.

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 the MAINTAIN command with the ADD keywords to add values to a dimension in the following ways:

You can merely specify the values that you want to add. In this case, the values are added to the end of the list of dimension values.
You can specify both the values that you want to add and where you want the values to be placed.

Example 5-1 Adding Values to Dimensions

This command adds ATLANTA at the beginning of the list of cities and inserts PEORIA after OMAHA.

MAINTAIN city ADD 'ATLANTA' FIRST, '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.

SHOW VALUES(city NOSTATUS)
ATLANTA
CONCORD
LINCOLN
NEW YORK
OMAHA
PEORIA
SEATTLE

Updating Relations When Merging New Values

When you are merging values into a dimension it is a good practice to update any relations that involve that dimension:

In some cases, using the simplified syntax of the MAINTAIN command shown below, you can update a relation at the same time you merge values into a dimension.
```
MAINTAIN dimension MERGE [exp [RELATE relation] ]
```
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.
In other cases, you need to explicitly update any relations that involve that dimension.

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.

Deleting Values from Dimensions

You can use the MAINTAIN command with the DELETE keyword to remove values from a dimension. 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:

One value, a list of values, a range of values, or all values
The values that match a list of values of a named related dimension
The values that are first, last, or in a specified position in the dimension
The values that meet a Boolean criterion
After the dimension values are sorted according to a specified criterion, the top or bottom n values of the dimension, or the top or bottom n performers, by percentage
For a hierarchical dimension, the values that have a certain relationship within the hierarchy
The values in the dimension that match the values in a valueset

Example 5-2 Deleting Values from a Dimension

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.

SHOW VALUES (city NOSTATUS)
ATLANTA
CONCORD
LINCOLN
COLUMBUS
PEORIA
SEATTLE

You use the variable population.c, which contains the population for each city.

MAINTAIN city DELETE population.c LT 75000

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.

SHOW VALUES (city NOSTATUS)
ATLANTA
CONCORD
COLUMBUS
SEATTLE

Deleting Values from Conjoint Dimensions

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.

MAINTAIN prod_dist DELETE <'SNOWSHOES' 'ATLANTA'>

Changing the Position of Dimension Values

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.

textvar = 'SEATTLE'
MAINTAIN city MOVE textvar LAST

Storing Dimension Values in Sorted Order

You can store the values of a dimension in sorted order by taking the following actions:

Limit the dimension to all of its values.
```
LIMIT dimension TO ALL
```
Sort the dimension values based on your desired sorting criterion.
```
SORT dimension A sort-criterion
```
To sort the values alphabetically, sort by the dimension itself.

Store the dimension values in their sorted order.

MAINTAIN dimension MOVE VALUES(dimension) FIRST

Suppose that the default status list for the city dimension contains the following values.

SHOW VALUES (city NOSTATUS)
ATLANTA
CONCORD
LINCOLN
COLUMBUS
PEORIA
SEATTLE

The following commands sort the values of city in alphabetical order and then store the values in that order.

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

The default status list of city reflects the new sorted order.

SHOW VALUES (city NOSTATUS)
ATLANTA
COLUMBUS
CONCORD
LINCOLN
PEORIA
SEATTLE

Maintaining Composites and Conjoint Dimensions

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.

Maintaining Composites

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.

Maintaining Conjoint Dimensions

Conjoint dimensions, unlike composites, are actual dimensions that you must explicitly maintain using the MAINTAIN command.

Maintaining Concat Dimensions

You can use the MAINTAIN command to change the order of the values in a concat dimension. If you use the MAINTAIN MOVE command on a simple dimension that is a component of a concat dimension, then the positions of the values of the concat dimension are not affected.

You cannot use the MAINTAIN command to add, delete, or rename concat dimension values or merge values from another dimension to those of the concat dimension.

If you use the MAINTAIN command to add a value to a simple dimension that is a component of a concat dimension, then Oracle OLAP adds that value to the concat dimension as a value of the component dimension. If you merge values from a simple dimension with a component simple dimension, then Oracle OLAP adds those values to the concat dimension as values of the component dimension.

If you delete or rename a value of a simple dimension that is a component of a concat dimension, then Oracle OLAP deletes or renames the value in the concat dimension. If you use the MAINTAIN command to add, merge, or delete the values of a simple dimension component of a concat dimension, the status of the concat dimension is automatically set to ALL.