CLEAR

The CLEAR command deletes the data that you specify for one or more variables.

Syntax

CLEAR [STATUS | {ALL [CACHE]}] [VALUES | {aggdata [USING aggmapname]}] FROM varname...

where aggdata is one or more of the following keywords that identifies the type of aggregated data that you want deleted from the variable.

AGGREGATE

LEAVES

PRECOMPUTES

NONPRECOMPUTES

Arguments

STATUS

Specifies that only the data that is currently in status will be taken into consideration. (Default)

ALL

Specifies that all of a variable's data will be taken into consideration regardless of the current status.

CACHE

Empties the session cache (see "What is an Oracle OLAP Session Cache?" for details).

VALUES

(Default) Deletes all of a variable's stored data and replaces each deleted data value with an NA value.

AGGREGATE

Deletes the data in all cells populated by the execution of an AGGREGATE command or an AGGREGATE function.

PRECOMPUTES

For all variables except those dimensioned by a compressed composite, deletes any data that was calculated when an AGGREGATE command executed and replaces that data with NA values.

NONPRECOMPUTES

Deletes any data that was calculated on the fly when a AGGREGATE function executed and replaces that data with NA values.

LEAVES

Deletes the detail-level data, meaning, the "leaf" data.

Note:

You cannot specify this keyword for a variable dimension by a compressed composite.

FROM varname

Specifies the name of the variable or variables from which data will be deleted. When you specify more than one variable, then every variable must have exactly the same dimensions in exactly the same order in its definition. In other words, when you include multiple variables in one command, those variables must be identical in their dimensionality.

USING aggmapname

Specifies the name of the aggmap that should be used. When you include this phrase, the dimensionality of every variable included in the CLEAR command must be identical to the dimensionality of the aggmap. In other words, every variable definition must have the same dimensions in the same order as those in the definition of the aggmap.

Note:

You cannot specify this phrase for a variable dimension by a compressed composite.

Examples

Example 9-5 Clearing a Variable's Data

The CLEAR command gives you an easy way to delete all of a variable's stored data. Suppose you have defined a sales variable and loaded data into it. You then find out that much of this data has changed. It will be more efficient to clear the sales variable and reload all of the data than it would be to change the existing data. You can do so with the following statement.

CLEAR ALL FROM sales

In this example, the VALUES keyword is assumed by default. Therefore, all of the sales data is deleted and replaced with NA values.

Example 9-6 Clearing Aggregated Data

Suppose you have aggregated data for your sales and units variable, and you have specified that all other data should be calculated on the fly.

The sales and units variables are defined with the same dimensions in the same order: time, product, and geography. Therefore, they have been aggregated with the sales.agg aggmap, which has the following definition.

DEFINE sales.agg AGGMAP <time, product, geography>

The sales.agg aggmap has the following contents.

RELATION time.r PRECOMPUTES (time ne 'YEAR99')
RELATION product.r PRECOMPUTES (product ne 'ALL')
RELATION geography.r

After aggregating both sales and units, you learn that there are certain geographic regions that none of your users will access. Because geography is the slowest-varying dimension, you can probably reduce the number of pages needed to store data by deleting data for the geographic regions that no one will need. This can reduce the size of your analytic workspace and possibly improve performance.

Set the status for each dimension. The only geographic regions that users will need are New England, Europe, and Australasia. The following statements put all time periods and all products for every geographic region in the current status, except for the geographic regions that users need. In other words, the following statements put all of the data that users do not need to access in status.
```
LIMIT time TO ALL
LIMIT product TO ALL
LIMIT geography COMPLEMENT 'NewEngland' 'Europe' 'Australasia'
```

Use the following statement to delete the unneeded data.

CLEAR STATUS PRECOMPUTES FROM sales units USING sales.agg

Example 9-7 Clearing Cached Data

Data is cached when an aggmap specifies calculation on the fly and contains a CACHE SESSION statement.

For example, suppose the sales.agg aggmap has the following contents.

RELATION time.r PRECOMPUTES (time ne 'YEAR99')
RELATION product.r PRECOMPUTES (product ne 'ALL')
RELATION geography.r
CACHE SESSION

Note that the sales.agg contains a CACHE SESSION command. This means that Oracle OLAP calculates some of the data at the time a user requests it, and then stores it in the session cache. To clear this data from the sales variable, use the following statement.

CLEAR ALL CACHE FROM sales