Clearing Data from Aggregate Storage Cubes

You can clear all data from an Essbase aggregate storage (ASO) cube, or you can clear data from a specified region, retaining the data located in other regions. You can do physical or logical clears.

To clear data, you must have the same privileges as for loading data (Database Update Permission or higher).

Clearing Data from Specific Regions

This method is useful when you want to delete volatile data (such as data corresponding to the last month), while retaining historical data.

Methods for clearing data from a region:

Physical

The input cells in the specified region are physically removed from the aggregate storage cube, as illustrated below.

Figure 38-2 Physically Clearing a Region of Data

If there are multiple data slices in the cube, the physical clear region operation automatically merges all data slices into the main data slice. After data for the specified region is cleared, Essbase materializes all aggregate views that were present in the main data slice before the clear region operation took place.

The process for physically clearing data completes in a length of time proportional to the size of the input data, not to the size of the data being cleared. Therefore, you might use this method only when removing large slices of data.

To physically clear data, use the alter database MaxL statement with the clear data in region grammar and the physical keyword:
```
alter database appname.dbname clear data in region 'MDX set expression' physical;
```
To save time, you can use a comma-separated list of MDX set expressions to clear from multiple physical regions.
Logical

The input cells in the specified region are written to a new data slice with negative, compensating values that result in a value of zero for the cells you want to clear, as illustrated below.

Figure 38-3 Logically Clearing a Region of Data

The logical clear region operation automatically merges only the data slice with zero values into the main data slice; other data slices in the cube are not merged. After data for the specified region is cleared, Essbase materializes aggregate views only in the new data slice.

The process for logically clearing data completes in a length of time that is proportional to the size of the data being cleared. Because compensating cells are created, this option increases the size of the cube.

To logically clear data, use the alter database MaxL statement with the clear data in region grammar but without the physical keyword:
```
alter database appname.dbname clear data in region 'MDX set expression';
```
Queries to the logically cleared region return zero values instead of #MISSING values. You may need to update formulas that rely on #MISSING values for empty cells.

To remove cells with a value of zero, use the alter database MaxL statement with the merge grammar and the remove_zero_cells keyword.

Note:

Oracle does not recommend performing a second logical clear region operation on the same region, because the second operation does not clear the compensating cells created in the first operation and does not create new compensating cells.

In specifying the region to be cleared, follow these guidelines:

The region must be symmetrical.
- {(Jan, Budget)} is a valid symmetrical region that clears all Budget data for Jan.
- {(Jan, Forecast1),(Feb, Forecast2)} is an invalid region because it consists of two asymmetrical regions (Jan, Forecast1 and Feb, Forecast2).
Individual members in any dimension in the region specification must be stored members.
Members in the region cannot be:
- Dynamic members (members with implicit or explicit MDX formulas)
- From attribute dimensions
  
  If you need to clear cells by an attribute, use the Attribute MDX function.
Members in the region can be upper-level members in stored hierarchies, which is a convenient way to specify multiple level 0 members.

For example, you can specify Qrt1, which is the same as specifying Jan, Feb, and Mar (the level 0 children of Qrt1):

The following two MaxL statements produce the same results:
```
alter database appname.dbname clear data in region '{Qtr1}';
```
```
alter database appname.dbname clear data in region '{Jan, Feb, Mar}';
```
(Physically clearing data only) Members in the region can be upper-level members in alternate hierarchies.

For example, you can specify High End Merchandise, which is the same as specifying Flat Panel, HDTV, Digital Recorders, and Notebooks (the shared, level 0 children of High End Merchandise):

The following two MaxL statements produce the same results:
```
alter database appname.dbname clear data in region '{High End Merchandise}';
```
```
alter database appname.dbname clear data in region '{[Flat Panel],[HDTV],[Digital Recorders],[Notebooks]}';
```
To specify members in alternate hierarchies when logically clearing data, use the Descendants MDX function.

Note:

When the region contains upper-level members from alternate hierarchies, you may experience a decrease in performance. In this case, consider using only level 0 members.
The MDX set expression must be enclosed with single quotation marks.

For example, to clear all January data for Forecast1 and Forecast2 scenarios, use this statement:

alter database ASOSamp.Basic clear data in region 'CrossJoin({Jan},{Forecast1, Forecast2})';

During the clear region operation, you cannot perform operations that update the cube (such as loading data, merging data slices, or clearing data from another region), nor export data. You can query the cube; however, the query results are based on the data set before the clear region operation.

The clear data in region grammar cannot clear data from the entire cube.

Clearing All Data

Clearing all data from an aggregate storage cube is the same as for a block storage cube. To clear the entire cube, use the alter database MaxL statement with the reset grammar:

alter database appname.dbname reset;