You can clear data from a specified region in an aggregate storage database and retain the data located in other regions. This feature is useful when you want to delete volatile data (such as data corresponding to the last month) but retain historical data. You must have Database Manager or Administrator permission to clear data.
Methods for clearing data from a region:
Physical
The input cells in the specified region are physically removed from the aggregate storage database, as illustrated in Figure 164, Physically Clearing a Region of Data.
If there are multiple data slices in the database, 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;
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 in Figure 165, 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 database 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 database.
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. See the Oracle Essbase Technical Reference.
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.Sample clear data in region 'CrossJoin({Jan},{Forecast1, Forecast2})';
During the clear region operation, you cannot perform operations that update the database (such as loading data, merging data slices, or clearing data from another region), nor export data. You can query the database; 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 database. See Clearing All Data from an Aggregate Storage Database.