Preparing Aggregate Storage Databases

The topics in this section describe dimension build and data load process differences between aggregate storage and block storage databases. You should be familiar with data load, dimension build, and rules file concepts and procedures.

For information about using data sources to change outlines and to load data values, see Understanding Data Loading and Dimension Building and Performing and Debugging Data Loads or Dimension Builds.

For information on the maximum size of a buildable aggregate storage outline, see Outline Paging Limits.

Building Dimensions in Aggregate Storage Databases

Aggregate storage dimension build changes to the outline can result in all aggregate views or all data values being cleared from the database when the dimension build is finished. Aggregate Storage Database Restructuring describes the results of outline changes.

If you use multiple data sources to build dimensions, you can save processing time by performing an incremental dimension build. Incremental dimension builds enable you to defer restructuring until all data sources have been processed.

For information about incremental dimension build, see Performing Deferred-Restructure Dimension Builds.

Differences between outline characteristics of block storage outlines and aggregate storage outlines affect data sources and rules files. For example, defining a dimension as sparse or dense is not relevant to aggregate storage outlines.

Rules File Differences for Aggregate Storage Dimension Builds

Rules files for building aggregate storage outlines must define only outline properties and field types that apply to aggregate storage outlines.

After converting a block storage outline to aggregate storage, update the rules files by associating them to the aggregate storage version of the outline.

Field Type Differences

The field contains a number (0–127) that specifies the order in which the member is evaluated in the outline. Values less than 0 or greater than 127 are reset to 0 and 127, respectively. No warning message is displayed. For information on the solve order property, see Calculation Order.

Valid build methods for solve order:

Generation
Level
Parent-child references

Data Source Differences for Aggregate Storage Dimension Builds

The following table lists the member codes that are recognized in dimension build data sources as properties for members of aggregate storage database outlines. Any other consolidation code is ignored and + (add) is assumed. (Data sources for modifying aggregate storage outlines should not include field values that apply only to block storage outlines.)

Table 38-1 Consolidation Codes for Members of Aggregate Storage Outlines

Code	Description
%	Expresses as a percentage of the current total in a consolidation (applies only to members of a dynamic hierarchy)
*	Multiplies by the current total in a consolidation (applies only to members of a dynamic hierarchy)
+	Adds to the current total in a consolidation (applies only to members of a dynamic hierarchy)
-	Subtracts from the current total in a consolidation (applies only to members of a dynamic hierarchy)
/	Divides by the current total in a consolidation (applies only to members of a dynamic hierarchy)
~	Excludes from the consolidation (applies only to members of a dynamic hierarchy or members beneath Label Only members in a stored hierarchy)
C	Set member as top of a stored hierarchy (applies to dimension member or generation 2 member)
D	Set member as top of a dynamic hierarchy (applies to dimension member or generation 2 member)
H	Set dimension member as multiple hierarchies enabled (applies to dimension member only)
K	Reset the time balance property to NONE (applies to accounts dimensions only).
N	Never allow data sharing
O	Tag as label only
P	Reset the time balance skip option to NONE (applies to accounts dimensions only).

Currency name and currency category field types are not supported for aggregate storage outlines.

In aggregate storage outlines, formulas must be specified in the same format as MDX numeric value expressions.

Building Alternate Hierarchies in Aggregate Storage Databases

To build shared members in an aggregate storage outline, make these selections in the Rules Editor:

On the Properties tab, select Auto Configuration
On the Dimensions tab, under Dimension Properties, select Share on the General tab

When auto configuration and sharing are enabled, duplicate members are automatically created under a new parent as shared members.

Note:

There are restrictions on using the duplicate generation (DUPGEN) method to build alternate hierarchies in aggregate storage outlines.

Caution:

In alternate hierarchies in aggregate storage databases, you can associate attributes only with level-0 members.

Understanding Exclusive Operations on Aggregate Storage Databases

On aggregate storage databases, multiple exclusive operations cannot be performed simultaneously on the same database. If one exclusive operation is running and a second exclusive operation is attempted simultaneously, the second operation is rejected with a message indicating that the operation cannot proceed while the first operation is in progress. For example, when performing a partial data clear on an aggregate storage database, a second partial data clear cannot run concurrently on the database, even if the operations are clearing different regions in the database. While most exclusive operations are mutually exclusive, there are some exceptions.

Exclusive operations and exceptions:

Export

Multiple export operations can run at the same time because export is a read-only operation. Export operations can run at the same time as build aggregations and backup, both of which are exclusive operations; however, export is not compatible with any other exclusive operation.
Build aggregations
Backup (putting the database in read-only archive mode)
Data load (committing the contents of a load buffer to the database)

Creating an aggregate storage load buffer and loading data into the load buffer are not exclusive operations. These operations can run concurrently with any other operations. However, committing the data in the load buffer to the database is an exclusive operation.
Spreadsheet send operations (for example, updating cells)

If a send operation is running while another exclusive operation is attempted, the new operation waits for the send operation to finish before proceeding. Even though not compatible with the send operation, the new operation does not error out because send operations are always assumed to be small and fast (<5 seconds). This means it is possible for many users to perform spreadsheet send operations at the same time without those operations being rejected because they are incompatible with each other.

Note:

In the case where multiple exclusive operations are attempted while a send operation is running, the order in which the new exclusive operations execute after the send operation completes is random; the order is not based on the sequence in which the new exclusive operations were attempted. For example, if a send operation is running and an exclusive operation of a different type is attempted, the new exclusive operation waits for the send operation to finish before proceeding. If, in the meantime, more send operations are attempted by other users, those send operations might be executed before the other exclusive operation, even though they were attempted afterward. Therefore, the exclusive operation might wait indefinitely as long as there is at least one send operation waiting to be executed.
Merge slices
Custom calculations, allocations, and other write operations
Data clear operations (full, aggregates only, and partial)

Queries are allowed to run concurrently with all exclusive operations. However, if an operation adds, changes, or removes any data in the database, the following sequence takes place at the end of the operation, when the changes are made visible to queries:

Any new queries are temporarily blocked from starting (the queries wait).
Existing queries finish running.
Data changes from the exclusive operation are committed to the database.
Queries that are waiting proceed.

Queries are never rejected or canceled because of an operation that changes data on an aggregate storage cube.

Loading Data into Aggregate Storage Databases

Aggregate storage databases facilitate analysis of very large dimensions containing up to a million or more members. To efficiently support loading data values into such large databases, Essbase:

Allows the processing of multiple data sources through temporary aggregate storage data load buffers
Allows you to control the percentage of resources a data load buffer uses
Allows an aggregate storage database to contain multiple slices of data (a query to the database accesses each slice, collecting all of the data cells)
Provides an incremental data load process that completes in a length of time that is proportional to the size of the incremental data

To load values to aggregate storage databases, you can use the Jobs page in the Essbase web interface, or you can use the alter database and import data statements in MaxL. Examples in this document are based on using MaxL.

Note:

If values have been calculated and stored through an aggregation, Essbase automatically updates higher-level stored values when data values are changed. No additional calculation step is necessary. The existence and size of an aggregation can affect the time it takes to perform a data load.

You cannot export data when loading data into a database.

Incrementally Loading Data Using a Data Load Buffer

Using the import data MaxL statement to load data values from a single data source does not involve the aggregate storage data load buffer.

If you use multiple import database data MaxL statements to load data values to aggregate storage databases, you can significantly improve performance by loading values to a temporary data load buffer first, with a final write to storage after all data sources have been read.

In the aggregate storage data load buffer, Essbase sorts and commits the values after all data sources have been read. If multiple (or duplicate) records are encountered for any specific data cell, the values are accumulated. Essbase then stores the accumulated values—replacing, adding to, or subtracting from existing data values in the database. Using the aggregate storage data load buffer can significantly improve overall data load performance.

Note:

When using the aggregate storage data load buffer, the choice for replacing, adding, or subtracting values is specified for the entire set of data sources when loading the data buffer contents to the database.

While the data load buffer exists in memory, you cannot build aggregations or merge slices, because these operations are resource-intensive. You can, however, load data to other data load buffers, and perform queries and other operations on the database. There might be a brief wait for queries, until the full data set is committed to the database and aggregations are created.

The data load buffer exists in memory until the buffer contents are committed to the database or the application is restarted, at which time the buffer is destroyed. Even if the commit operation fails, the buffer is destroyed and the data is not loaded into the database. You can manually destroy a data load buffer by using the alter database MaxL statement.

Note:

Stopping the application before committing the buffer contents destroys the buffer. In this situation, after restarting the application, you must initialize a new buffer and load the data to it.

To use the data load buffer for aggregate storage databases:

Prepare the data load buffer, where data values are sorted and accumulated by using the alter database MaxL statement to initialize an aggregate storage data load buffer. For example:
```
alter database ASOsamp.Sample 
   initialize load_buffer with buffer_id 1;
```
Load data from your data sources into the data load buffer using the import database MaxL statement. Use multiple statements to load data from multiple data sources. You can include any combination of data sources. Specify a rules file if the data source requires one.
The following example loads two data sources, one of which uses a rules file, into the same data load buffer:
```
import database ASOsamp.Sample data 
   from server data_file 'file_1.txt' 
   to load_buffer with buffer_id 1
   on error abort; 
import database ASOsamp.Sample data
   from server data_file 'file_2' 
   using server rules_file ‘rule’ 
   to load_buffer with buffer_id 1;
   on error abort;
```
To load data into multiple load buffers simultaneously, see Performing Multiple Data Loads in Parallel.
Use the import data MaxL statement to commit the data load buffer contents to the database. For example:
```
import database ASOsamp.Sample data 
   from load_buffer with buffer_id 1;
```
To commit the contents of multiple data load buffers into the database with one MaxL statement, see Performing Multiple Data Loads in Parallel.

The following incremental data load example provides optimal performance when new data values do not intersect with existing values:

Create a single data load buffer using the ignore_missing_values and ignore_zero_values properties. For example:
```
alter database ASOsamp.Sample 
   initialize load_buffer with buffer_id 1
   property ignore_missing_values, ignore_zero_values;
```
If the database must be available for send data requests while the database is being updated, initialize the data load buffer with the resource_usage grammar set for 80%. For example:
```
alter database ASOsamp.Sample 
   initialize load_buffer with buffer_id 1
   resource_usage 0.8 property
   ignore_missing_values, ignore_zero_values;
```

Load the data into the buffer. For example:

import database ASOsamp.Sample data 
   from server data_file 'file_1.txt' 
   to load_buffer with buffer_id 1
   on error abort; 
import database ASOsamp.Sample data
   from server data_file 'file_2'
   to load_buffer with buffer_id 1;
   on error abort;

Commit the contents of the data load buffer to the database by creating a slice and adding values. For example:
```
import database ASOsamp.Sample data 
   from load_buffer with buffer_id 1
   add values create slice;
```

Controlling Data Load Buffer Resource Usage

When performing an incremental data load, Essbase uses the aggregate storage cache for sorting data. You can control the amount of the cache a data load buffer can use by specifying the percentage. The percentage is a number between .01 and 1.0 inclusive; only two digits after the decimal point are significant—for example, 0.029 is interpreted as 0.02. By default, the resource usage of a data load buffer is set to 1.0, and the total resource usage of all data load buffers created on a database cannot exceed 1.0. For example, if a buffer of size 0.9 exists, you cannot create another buffer of a size greater than 0.1.

Note:

Send operations internally create load buffers of size 0.2; therefore, a load buffer of the default size of 1.0 will cause send operations to fail because of insufficient data load buffer resources.

To set the amount of resources the buffer is allowed to use, specify the percentage when you initiate the data load in the Essbase web interface. If using MaxL, use the alter database MaxL statement with the resource_usage grammar.

For example, to set the resource_usage to 50% of the total cache, use this statement:

alter database ASOsamp.Sample
   initialize load_buffer with buffer_id 1
   resource_usage .5;

Setting Data Load Buffer Properties

When loading data incrementally, you can specify how missing and zero values in the source data are treated when loading the data into the data load buffer.

For resolving cell conflicts for duplicate cells, you can specify whether to use the last cell loaded into the load buffer.

The data load buffer properties:

ignore_missing_values: Ignores #MI values in the incoming data stream
ignore_zero_values: Ignores zeros in the incoming data stream
aggregate_use_last: Combines duplicate cells by using the value of the cell that was loaded last into the load buffer

Note:

When loading text and date values into an aggregate storage database, use the aggregate_use_last property to help eliminate invalid aggregations. For other guidelines, see Loading, Clearing, and Exporting Text and Date Measures.

If you use multiple properties in the command and any conflict, the last property listed takes precedence.

To set data load buffer properties, use the alter database MaxL statement with the property grammar.

For example:

alter database ASOsamp.Sample
   initialize load_buffer with buffer_id 1
   property ignore_missing_values, ignore_zero_values;

Resolving Cell Conflicts

By default, when cells with identical keys are loaded into the same data load buffer, Essbase resolves the cell conflict by adding the values together.

To create a data load buffer that combines duplicate cells by accepting the value of the cell that was loaded last into the load buffer, use the alter database MaxL statement with the aggregate_use_last grammar.

For example:

alter database ASOsamp.Sample
   initialize load_buffer with buffer_id 1
   property aggregate_use_last;

Note:

When using data load buffers with the aggregate_use_last grammar, data loads are significantly slower, even if there are not any duplicate keys.

Performing Multiple Data Loads in Parallel

Multiple data load buffers can exist on an aggregate storage database. To save time, you can load data into multiple data load buffers simultaneously.

Although only one data load commit operation on a database can be active at any time, you can commit multiple data load buffers in the same commit operation, which is faster than committing buffers individually.

To load data into multiple data load buffers simultaneously, use separate MaxL Shell sessions. For example, in one MaxL Shell session, load data into a buffer with an ID of 1:

alter database ASOsamp.Sample
   initialize load_buffer with buffer_id 1 resource_usage 0.5;
import database ASOsamp.Sample data
   from data_file "dataload1.txt"
   to load_buffer with buffer_id 1
   on error abort;

Simultaneously, in another MaxL Shell session, load data into a buffer with an ID of 2:

alter database ASOsamp.Sample
   initialize load_buffer with buffer_id 2 resource_usage 0.5;
import database ASOsamp.Sample data
   from data_file "dataload2.txt"
   to load_buffer with buffer_id 2
   on error abort;

When the data is fully loaded into the data load buffers, use one MaxL statement to commit the contents of both buffers into the database by using a comma-separated list of buffer IDs:

For example, this statement loads the contents of buffers 1 and 2:

import database ASOsamp.Sample data 
   from load_buffer with buffer_id 1, 2;

Note:

When loading SQL data into aggregate storage databases, you can use up to eight rules files to load data in parallel. This functionality is different than the process described above. When preforming multiple SQL data loads in parallel, you can use one import database MaxL statement with the using multiple rules_file grammar. Essbase initializes multiple temporary aggregate storage data load buffers (one for each rules file) and commits the contents of all buffers into the database in one operation. See the Oracle Essbase SQL Interface Guide.

Listing Data Load Buffers for an Aggregate Storage Database

Multiple data load buffers can exist on an aggregate storage database. For a list and description of the data load buffers that exist on an aggregate storage database, use the query database MaxL statement with the list load_buffers grammar:

query database appname.dbname list load_buffers;

This statement returns the following information about each existing data load buffer:

Table 38-2 Data Load Buffer Information

Field	Description
buffer_id	ID of a data load buffer (a number between 1 and 4,294,967,296).
internal	A Boolean that specifies whether the data load buffer was created internally by Essbase (TRUE) or by a user (FALSE).
active	A Boolean that specifies whether the data load buffer is currently in use by a data load operation.
resource_usage	The percentage (a number between .01 and 1.0 inclusive) of the aggregate storage cache that the data load buffer is allowed to use.
aggregation method	One of the methods used to combine multiple values for the same cell within the buffer: AGGREGATE_SUM: Add values when the buffer contains multiple values for the same cell. AGGREGATE_USE_LAST: Combine duplicate cells by using the value of the cell that was loaded last into the load buffer.
ignore_missings	A Boolean that specifies whether to ignore #MI values in the incoming data stream.
ignore_zeros	A Boolean that specifies whether to ignore zeros in the incoming data stream.

Creating a Data Slice

You can incrementally commit the data load buffer to an aggregate storage database to create a slice. After loading the new slice into the database, Essbase creates all necessary views on the slice (such as aggregate views) before the new data is visible to queries.

Creating a data slice is useful because it improves the performance of incremental data loads. The amount of time an incremental data load takes is proportional to the amount of new data; the size of the database is not a factor.

To create a data slice, use the import database MaxL statement with the create slice grammar.

For example, to create a slice by overriding values (the default), use this statement:

import database ASOsamp.Sample data
   from load_buffer with buffer_id 1
   override values create slice;

Note:

If you use override values when creating a slice, #MISSING values are replaced with zeros. Using this option is significantly slower than using the add values or subtract values options.

Merging Incremental Data Slices

Automatically Merging Incremental Data Slices During a Data Load to an Aggregate Storage Database

Using the AUTOMERGE and AUTOMERGEMAXSLICENUMBER configuration settings, you can specify whether Essbase automatically merges incremental data slices during a data load to an aggregate storage database.

AUTOMERGE configuration setting options:

ALWAYS—Specifies to automatically merge incremental data slices during a data load to an aggregate storage database. By default, merges are executed once for every four consecutive incremental data slices. If, however, the AUTOMERGEMAXSLICENUMBER configuration setting is used, the auto-merge process is activated when the AUTOMERGEMAXSLICENUMBER value is exceeded. The size of the incremental data slices is not a factor in selecting which ones are merged.

The default value is ALWAYS.
NEVER—Specifies to never automatically merge incremental data slices during a data load to an aggregate storage database. To manually merge incremental data slices, use the alter database MaxL statement with the merge grammar.
SELECTIVE—Specifies to activate the incremental data slice auto-merge process when the number of incremental data slices specified in the AUTOMERGEMAXSLICENUMBER configuration setting is exceeded. If the number of incremental data slices in the data load does not exceed the value of AUTOMERGEMAXSLICENUMBER, the auto-merge process is not activated.

Manually Merging Incremental Data Slices

You can merge all incremental data slices into the main database slice or merge all incremental data slices into a single data slice while leaving the main database slice unchanged. To merge slices, you must have the same privileges as for loading data (Administrator or Database Manager permissions).

After the new input view is written to the database, Essbase creates the aggregate views for the slice. The views created for the new slice are a subset of the views that exist on the main database slice.

Note:

You cannot export data when performing a merge.

If you cleared data from a region using the logical clear region operation, which results in a value of zero for the cells you cleared, you can elect to remove zero value cells during the merge operation.

To perform merging operations, use the alter database MaxL statement with the merge grammar.

For example, to merge all incremental data slices into the main database slice, use this statement:

alter database ASOsamp.Sample
   merge all data;

To merge all incremental data slices into the main database slice and remove zero value cells, use this statement:

alter database ASOsamp.Sample
   merge all data remove_zero_cells;

To merge all incremental data slices into a single data slice, use this statement:

alter database ASOsamp.Sample
   merge incremental data;

Note:

Before you copy an aggregate storage application, you must merge all incremental data slices into the main database slice. Data in unmerged incremental data slices is not copied.

Replacing Database or Incremental Data Slice Contents

For data sets that are small enough to reload completely while maintaining low data latency, Essbase can remove the current contents of a database and replace the database with the contents of a specified data load buffer. The atomic replacement functionality transitions querying the old contents of the database to the new contents without interrupting service. The newly loaded data set is aggregated to create the same set of views that existed for the replaced data set.

Essbase also allows for atomically replacing the contents of all incremental data slices in a database. Consider a situation in which data can be separated into a relatively large, static data set that is never updated and a relatively small, volatile data set for which the individual updates are difficult to identify but are confined to the volatile data set. For example, the large, static data set consists of historical transaction data for the last three years; however, for the transaction data for the past two months, users can change a characteristic of a transaction in the source database. Tracking these changes can be prohibitively complex. You can load the static data set as the main slice in a database and the volatile data set as one or more incremental slices.

Essbase removes the current contents of all incremental data slices and creates a new slice (using the add values grammar) with the contents of a specified data load buffer. The newly loaded data set is augmented with aggregated views based on the set of views that exist on the main slice.

Note:

To use the override grammar, create a data load buffer with the ignore_missing_values property for optimal performance. Additionally, you must ensure that there are not any conflicts between the static and volatile data sets (for example, there should not be a value in each data set for the same cell).

To replace the contents of a database or the incremental data slices in a database, use the import database MaxL statement with the override grammar.

For example, to replace the contents of a database, use this statement:

import database ASOsamp.Sample data
   from load_buffer with buffer_id 1
   override all data;

To replace the contents of all incremental data slices with a new slice, use this statement:

import database ASOsamp.Sample data
   from load_buffer with buffer_id 1
   override incremental data;

Note:

If the override replacement fails, Essbase continues to serve the old data set.

Viewing Incremental Data Slices Statistics

Essbase provides statistics on the size and number of incremental data slices, and the cost of querying the incremental data slices.

The time it takes for a query to access all of the incremental data slices is expressed as a percentage (between .01 and 1.0 inclusive). If a database has a main slice and multiple incremental data slices, a query statistic of 0.66 means that two-thirds of the query time was spent querying the incremental data slices and one-third was spent querying the main data slice. If the cost of querying the incremental data slices is too high, you can merge the slices.

Managing Disk Space For Incremental Data Loads

Incremental data loads on aggregate storage databases may use disk space up to two times the size of your current data files. For example, assume that the size of a database .dat file is 1 GB and the size of the incremental data load is 200 MB, for a total database size of 1.2 GB. During the incremental data load process, Essbase might use up to 2.4 GB of disk space.

In cases where databases are larger than 2 GB, you can reduce disk space utilization by setting the maximum file size of the default tablespace to no more than 2 GB..

To set the maximum file size of the default tablespace, you can use the alter tablespace MaxL statement.

Renegade Members in Aggregate Storage Data Loads

Renegade members enable continuation of an aggregate storage data load even if a specified member combination has missing or invalid members. When the data load encounters a missing or invalid member in an outline, the data load continues, with the data value of the missing or invalid member stored under the member that is tagged as the renegade member in the dimension. If a renegade member is not set in the dimension, the record is rejected. If data already exists for the renegade member, the behavior depends on whether you selected to add values or to overwrite values when creating the data load rules file.

Each dimension can have only one member assigned as a renegade member, and the renegade member must be a level-0 member.

The following data load file includes a member named SC:

Product  Measures   *Data*
NY,      Sales       100
SA,      Sales       200
SC,      Sales       300

In the following outline, no member is named SC; however, the member named SA is set as the renegade member in the Products dimension:

Products (+)
   NY (+)
   SA (+)
Measures (+)
   Sales (+)
   COGS (+)

During the data load, the data value for the member combination SC and Sales, which is 300, is loaded into renegade member SA and Sales.

In the following data load file, two records exist for SC and Sales, each with different values:

Product    Measures    *Data*
NY,        Sales        100
SA,        Sales        200
SC,        Sales        250
SC,        Sales        300

Both values for SC and Sales (250 and 300) are loaded into SA and Sales. If you selected to add values, the value in the cell is 550 (250 + 300). If you selected to overwrite values, then the value in the cell is the last one loaded; in this case, 300.

The following examples illustrate the behavior of renegade members using the following data load file:

Months    Transaction Type    Customer    Product    Price
Jan,      Sale,               Discard1,   Product1   300
Jan,      Sale,               Discard1,   Discard2   300
Jan,      Sale,               Customer1,  Discard2   300

Discard1 and Discard2 do not exist in the outline.

Example 1:

If the Customer dimension has the Customer1 member tagged as renegade, and the other dimensions do not have renegade members, only the first record is loaded into the following intersection:
```
Jan    Sale    Customer1(Ren)    Product1    300
```
The other two records are rejected because the Product dimension does not have a renegade member. The rejected records are logged in the renegade member log file.
Example 2:

If the Product dimension has the Product1 member tagged as renegade, and the other dimensions do not have renegade members, only the last record is loaded into the following intersection:
```
Jan    Sale    Customer1    Product1(Ren)    300
```
The other two records are rejected, because the Customer dimension does not have a renegade member. The rejected records are logged in the renegade member log file.
Example 3:

If the Customer and Product dimensions both have renegade members (Customer1 and Product1), all records are loaded into the following intersection:
```
Jan    Sale    Customer1(Ren)    Product1(Ren)    900 (or 300 if overwrite is enabled)
```

Example 4:

In example 4, the Customer dimension has RenMember1 tagged as renegade, and the Product dimension has RenMember2 tagged as renegade. Using the following data load file, all records are loaded because both the Customer and Product dimensions have renegade members.

Customer1 and Product1 are not renegade members. "Discard1" and "Discard2"do not exist in the outline.

Data load file:

Months  Transaction Type  Customer    Product   *Data*
Jan,     Sale,            Discard1,   Product1   300
Jan,     Sale,            Discard1,   Discard2   300
Jan,     Sale,            Customer1,  Discard2   300

The values specified in the data load file for discard members are instead automatically loaded into the designated renegade members:

Loaded data:

Months  Measures   Customer          Product           Price
Jan     Sale       RenMember1(ren)   ProductR          300
Jan     Sale       RenMember1(ren)   RenMember2(ren)   300
Jan     Sale       CustomerR         RenMember2(ren)   300

Logging for renegade members is not enabled by default. To enable logging, use the RENEGADELOG configuration setting, which, when set to TRUE, enables logging of members loaded into a renegade member intersection.

Note:

Renegade members can be referenced in calculation scripts. Renegade members are not supported in tabular data loads or spreadsheet update operations.

Renegade members can be referenced in calculation and report scripts. Renegade members are not supported in tabular data loads or spreadsheet update operations.

Using Smart View

In Smart View, the submit command is equivalent to using the incremental data load functionality with the override grammar.

While performing a send operation, new requests for lock, unlock, and retrieve and lock will wait until the send operation is completed.

Data Source Differences for Aggregate Storage Data Loads

While processing data source records for loading values into aggregate storage databases, Essbase processes source data records only for the level 0 dimension intersections where the member does not have a formula.

The following example shows a data source with records for only level 0 intersections. The last field contains data values; the other fields are level 0 members of their respective dimensions.

Jan, Curr Year, Digital Cameras, CO, Original Price, 10784
Jan, Prev Year, Camcorders, CO, Original Price, 13573

Essbase ignores records that specify upper-level members and, at the end of the data load, displays the number of skipped records.

For example, the following record would be skipped because member Mid West is a level 1 member:

Jan, Curr Year, Digital Cameras, Mid West, Original Price, 121301

Sorting data sources is unnecessary because Essbase Server reads and sorts records internally before committing values to the database.

Rules File Differences for Aggregate Storage Data Loads

Rules file specifications for loading values to aggregate storage databases reflect the aggregate storage data load process.

For block storage data loads, through the rules file, you choose for each data source whether to overwrite existing values, add values in the data source to existing values, or subtract them from existing values.

For aggregate storage data loads using the aggregate storage data load buffer, you make this choice for all data load sources that are gathered into the data load buffer before they are loaded to the database.

Clearing Data from an Aggregate Storage Database

You can selectively clear data or clear all data from an aggregate storage database.

Also see Clearing Aggregations.

Clearing Data from Specific Regions of Aggregate Storage Databases

You can clear data from a specified region in an Essbase 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 can do physical or logical clears.

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 below.

Figure 38-2 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;
```
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 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.

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.

Clearing All Data from an Aggregate Storage Database

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

alter database appname.dbname reset;

Copying an Aggregate Storage Application

To copy all of the data in an aggregate storage application, you must merge all incremental data slices into the main database slice. Data in unmerged incremental data slices is not copied.

Combining Data Loads and Dimension Builds

When using the aggregate storage data load buffer, you can combine data sources and rules files to add members to the outline and to load data values to the level 0 cells. Regardless of the order in which you specify the files, Essbase makes the outline changes and then loads the data values.

Note:

Oracle recommends that you separate dimension build operations from data load operations, because some dimension builds clear data, which could lead to data loss. See Building Dimensions in Aggregate Storage Databases.