Loading, Calculating, and Retrieving Aggregate Storage Data

In This Section:

Introduction

Preparing Aggregate Storage Databases

Calculating Aggregate Storage Databases

Retrieving Aggregate Storage Data

The information in this chapter applies only to aggregate storage databases and is not relevant to block storage databases. Also see Comparison of Aggregate and Block Storage.

Introduction

The most common processes for working with database information include maintaining the outline, loading data values to the database, calculating values, and retrieving database information. Performing these tasks with aggregate storage databases is different from performing them with block storage databases.

Examples in this chapter refer to the outline in Figure 163, Sample Aggregate Storage Outline.

Figure 163. Sample Aggregate Storage Outline

This image shows the outline of the ASOSamp.Sample database.

The simplified aggregate storage outline in Figure 163, Sample Aggregate Storage Outline is not completely expanded. A plus sign (+) node at the left of a member name indicates that the member has children that are not displayed.

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 files for building aggregate storage outlines must define only outline properties and field types that apply to aggregate storage outlines. See Table 192, Outline Differences Between Aggregate Storage and Block Storage.

After converting a block storage outline to aggregate storage, update the rules files by associating them to the aggregate storage version of the outline. See “Associating an Outline with an Editor” in the Oracle Essbase Administration Services Online Help.

Field Type Differences for Aggregate Storage Dimension Builds

The solve order field type is available only on aggregate storage databases.

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

  To set field information, see “Setting Field Types” in the Oracle Essbase Administration Services Online Help.

Data Prep Editor Dialog Boxes for Aggregate Storage

As you edit rules files for aggregate storage databases, some dimension build rules file options that apply only to block storage databases are displayed in Data Prep Editor dialog boxes. Table 203 lists block storage rules file settings that do not apply to aggregate storage outlines. Entries in rules files for these options are ignored when the rules file is processed.

Table 203. Aggregate Storage Dimension Build Rules File Differences

Rules File Location in the Administration Services Interface

Dimension Build Rules File Options That Do Not Apply to Aggregate Storage Databases

Dimension Build Settings dialog box, Global Settings tab

Data configuration options

Dimension Properties dialog box, Dimension Properties tab

Dimension types option: Country

Two-Pass calculation option

Data storage options:

  • Dynamic Calc and Store

  • Dynamic Calc

All configuration options

Dimension Properties dialog box, Accounts Dimension tab

None of the options on this tab apply.

Field Properties dialog box, Dimension Build Properties tab

Field type options:

  • Currency name

  • Currency category

Currency functionality does not apply to aggregate storage databases.

See the Oracle Essbase Administration Services Online Help.

Data Source Differences for Aggregate Storage Dimension Builds

Data sources for modifying aggregate storage outlines should not include field values that apply only to block storage outlines. Table 204 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. See Using the Data Source to Work with Member Properties.

Table 204. Valid Consolidation Properties for Members of Aggregate Storage Outlines

Code

Description

%

Express as a percentage of the current total in a consolidation (applies only to members of a dynamic hierarchy)

*

Multiply by the current total in a consolidation (applies only to members of a dynamic hierarchy)

+

Add to the current total in a consolidation (applies only to members of a dynamic hierarchy)

-

Subtract from the current total in a consolidation (applies only to members of a dynamic hierarchy)

/

Divide by the current total in a consolidation (applies only to members of a dynamic hierarchy)

~

Exclude from the consolidation (applies only to members of a dynamic hierarchy or members beneath Label Only members in a stored hierarchy)

O

Tag as label only

N

Never allow data sharing

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)

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. See Developing Formulas on Aggregate Storage Outlines.

Building Alternate Hierarchies in Aggregate Storage Databases

To build shared members in an aggregate storage outline, select the autoconfigure setting in the Dimension Build Settings dialog box in Administration Services, and then make sure that Essbase is set up to allow sharing (clear Do Not Share in the Dimension Build Settings dialog box in Administration Services or omit the Never allow Data Sharing property in the rules file). When autoconfigure is selected and sharing is enabled, Essbase automatically creates duplicate members under a new parent as shared members. See Building Shared Members by Using a Rules File.

Note:

There are restrictions on using the duplicate generation (DUPGEN) method to build alternate hierarchies in aggregate storage outlines. See Table 195, Data Load Differences Between Aggregate Storage and Block Storage.

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

  • 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:

  1. Any new queries are temporarily blocked from starting (the queries wait).

  2. Existing queries finish running.

  3. Data changes from the exclusive operation are committed to the database.

  4. Queries that are waiting proceed.

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

For general information about loading data, see Performing and Debugging Data Loads or Dimension Builds.

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 an aggregate storage databases, use a tool:

Tool

Topic

Location

Administration Services

Performing a Data Load or Dimension Build for Aggregate Storage Databases

Oracle Essbase Administration Services Online Help

MaxL

alter database

import data

Oracle Essbase Technical Reference

ESSCMD

IMPORT

LOADDB

Oracle Essbase Technical Reference

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. See Aggregations.

Note:

You cannot export data when loading data into a database.

Incrementally Loading Data Using a Data Load Buffer

Using the import database 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 (see Resolving Cell Conflicts). 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. See the Oracle Essbase Technical Reference.)

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:

  1. 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;
  2. 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, such as .xls files, text files, and SQL relational sources. Specify a rules file if the data source requires one.

    The following example loads three 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;
    import database ASOsamp.Sample data 
       from server excel data_file 'file_3.xls' 
       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.

  3. Use the import database 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:

  1. 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;
  2. 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;
    
  3. 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, 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;

If you plan to run concurrent send operations, use the ASOLOADBUFFERWAIT configuration setting and the alter database MaxL statement with the wait_for_resources grammar. ASOLOADBUFFERWAIT applies to the creation of aggregate storage data load buffers with the wait_for_resources option, and applies to allocations, custom calculations, and lock and send operations.

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 option 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;

In Administration Services Console, you can set options to ignore missing values and zero values, and to aggregate duplicate cells by using the value of the last cell loaded, on the Data Load dialog box. See “Data Load Dialog Box” in the Oracle Essbase Administration Services Online Help.

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.

Note:

When using Administration Services Console to load data into an aggregate storage database, only a single data load buffer is used.

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 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 205. Data Load Buffer Information

FieldDescription

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.

In Administration Services Console, you can set an option to create a slice on the Data Load dialog box. See “Data Load Dialog Box” in the Oracle Essbase Administration Services Online Help.

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

When the data for a new data slice is in the data load buffer, Essbase scans the list of incremental data slices and considers whether to automatically merge them with the new slice. To qualify for an automatic merge, a slice must be smaller than 100,000 cells or smaller than two times the size of the new slice. Slices larger than 5,000,000 cells are never automatically merged. For example, if a new slice contains 300,000 cells, incremental data slices that contain 90,000 cells and 500,000 cells are automatically merged with the new cell. An incremental data slice that contains 700,000 is not.

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. See Clearing Data from Specific Regions of Aggregate Storage Databases.

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;

In Administration Services Console, you can merge data slices. See “Merging Incremental Data Slices (Aggregate Storage Databases)” in the Oracle Essbase Administration Services Online Help.

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. See Merging Incremental Data Slices.

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.

In Administration Services Console, you can set the option to replace the contents of the database or data slices in the Data Load dialog box. See “Replacing Database or Data Slice Contents (Aggregate Storage Database)” in the Oracle Essbase Administration Services Online Help.

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.

To view the incremental data slice statistics, see “Viewing Aggregate Storage Statistics” in the Oracle Essbase Administration Services Online Help.

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. 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. See Working with Tablespaces.

  To set the maximum file size of the default tablespace, use a tool:

Tool

Topic

Location

Administration Services

Managing Tablespaces

Oracle Essbase Administration Services Online Help

MaxL

alter tablespace

Oracle Essbase Technical Reference

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.

See the Oracle Smart View for Office User's Guide.

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.

To use a rules file that was defined for a block storage outline with an aggregate storage outline, first associate the rules file with the aggregate storage outline. See “Associating an Outline with an Editor” in the Oracle Essbase Administration Services Online Help.

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

    Figure 164. Physically Clearing a Region of Data

    This image illustrates how input cells are physically removed when clearing data using the physical method.

    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.

    Figure 165. Logically Clearing a Region of Data

    This image illustrates how a new data slice with compensating values is created when clearing data using the logical method.

    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):

    This image shows an outline, as described in the text preceding the image.

    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):

    This image shows an outline, as described in the text preceding the image.

    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.

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, either use:

  • The alter database MaxL statement with the reset grammar:

    alter database appname.dbname reset; 
  • Administration Services Console

    See “Clearing Data” in the Oracle Essbase Administration Services Online Help.

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.

See Merging Incremental Data Slices.

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:

Although dimension builds and data loads for aggregate storage databases can be combined into one operation in Administration Services, 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.

Aggregate storage database values are calculated through the outline structure and MDX formulas. When a data load is complete, all the information needed to calculate an aggregate storage database is available. When a retrieval request is made, Essbase Server calculates the needed values by consolidating the values loaded for level 0 members and calculating formulas. Values calculated for retrievals are not stored.

To improve retrieval performance, Essbase can aggregate values and store them ahead of time. However, aggregating and storing all values can be a lengthy process that requires disk space for storage. Essbase provides an intelligent aggregation process that balances time and storage resources. See Aggregating an Aggregate Storage Database.

To prepare an aggregate storage database for retrieval, you create the outline and load the level 0 values. Then you calculate the database by aggregating, and storing additional values, with the remaining values to be calculated when retrieved.

Note:

If a database needs calculation scripts to handle special calculations and data dependencies, consider altering the database design or making it a block storage database.

See Table 193, Calculation Differences Between Aggregate Storage and Block Storage.

Outline Factors Affecting Data Values

The hierarchical structure of an aggregate storage outline determines how values are rolled up. Level 0 member values roll up to level 1 member values, level 1 member values roll up to level 2 member values, and so on.

Consolidation operators assigned to members of dynamic hierarchies define the operations used in the roll-up: add (+), subtract (-), multiply (*), divide (/), percent (%), no operation (~), and (^) never consolidate. For an explanation of operators, see Table 16, Consolidation Operators.

Note:

Members of stored hierarchies can have only the addition (+) or the no-consolidation (~) operator.

For more complex operations, you can provide MDX formulas on members of dynamic hierarchies. MDX formulas are written in the same format as MDX numeric value expressions. See Developing Formulas on Aggregate Storage Outlines.

Block Storage Calculation Features That Do Not Apply to Aggregate Storage Databases

The following characteristics of calculating block storage databases do not apply to aggregate storage databases:

  • Calculation script calculations

  • Dynamic Calc and Dynamic Calc and Store member storage properties

  • Block storage formula syntax and predefined Essbase functions in formulas

  • Custom-defined calculation functions and custom-defined calculation macros

  • Formulas on members of dimensions other than members of aggregate storage dynamic hierarchies

  • Preloaded values for member intersections above level 0

  • Two-pass calculations tags

  • Block storage performance features such as Intelligent Calculation

Calculation Order

Aggregate storage calculation order and block storage calculation order differ. For aggregate storage databases, Essbase calculates data in the following order:

  1. Aggregates members of stored hierarchies and attribute dimensions. The order in which members and dimensions are aggregated is optimized internally and changes according to the nature of the database outline and the existing aggregations. Because the aggregation is additive, the order in which Essbase aggregates the dimensions and members does not affect the results.

    Because the internal aggregation order for an aggregate storage database is not predictable, any inherent rounding errors are also not predictable. These rounding errors are expected behavior in computer calculation and are extremely small in relation to the data values concerned.

  2. Calculates dynamic hierarchy dimension members and formulas. The order in which members and formulas are evaluated is defined by the solve order property, which you can set for each member or dimension. Calculation order may affect calculation results.

Solve Order Property

The concept of solve order applies to query execution. When a cell is evaluated in a multidimensional query, the order in which the calculations should be resolved may be ambiguous. To remove ambiguity, you can use the solve order property to specify the required calculation priority.

Note:

It is good practice to specify the solve order for each member by setting the solve order property at the member level or at the dimension level. Members without formulas that do not have a specified solve order inherit the solve order of their dimension. Members with formulas that do not have a specified solve order have a solve order of zero.

  To specify the solve order for a member or a dimension, use a tool:

Tool

Topic

Location

Administration Services

Specifying the Calculation Order for Members and Dimensions in Aggregate Storage Databases

Oracle Essbase Administration Services Online Help

MaxL

solve_order parameter in the With section of an MDX query

Oracle Essbase Technical Reference (see MDX, Grammar Rules, With Specification)

The value of the solve order property determines the priority with which Essbase calculates the formulas. The formulas on the members that have a specified solve order are calculated in order from the lowest solve order to the highest. (See Example Using the Solve Order Property). You can specify a solve order between 0 and 127. The default is 0.

You can specify the solve order at the member level or at the dimension level. Essbase uses the following information to define calculation precedence:

  1. Member solve order

  2. Dimension solve order (members without formulas for which you do not specify a member solve order inherit the solve order of their dimension. Members with formulas for which you do not specify a member solve order have a solve order of zero.)

    If multiple members have the same solve order, the members are evaluated in the reverse order in which their dimensions occur in the database outline. The member that occurs later in the outline takes precedence.

    The tie situation calculation order is different for calculated members defined in an MDX query for block storage databases. See the Oracle Essbase Technical Reference.

    Note:

    When a member formula is dependant on the value of another member, the member with the formula must have a higher solve order than the member or members on which it depends. For example, in the ASOsamp.Sample database outline in Figure 167, ASOsamp.Sample Database Showing the Measures, Years, and Time Dimensions, Avg Units/Transaction depends on the value of Units and of Transactions. Avg Units/Transaction must have a higher solve order than Units and Transactions.

Example Using the Solve Order Property

The following example is based on the ASOsamp.Sample database. To remove ambiguity in query results, the example uses the solve order property to specify the required calculation priority.

The spreadsheet query in Figure 166, Results from Spreadsheet Query of ASOsamp.Sample database Showing the Variance Between Two Ratios (C12) retrieves data for the number of units sold and the number of transactions for January of the current year and for January of the previous year. The Variance member shows the difference between the current year and the previous year. The Avg Units/Transaction member shows a ratio of the number of units sold per transaction.

Figure 166. Results from Spreadsheet Query of ASOsamp.Sample database Showing the Variance Between Two Ratios (C12)

This image shows the results of a spreadsheet query, as described in the text preceding the image.

Figure 167, ASOsamp.Sample Database Showing the Measures, Years, and Time Dimensions shows the database outline for these members and the formulas applied to the Variance and Avg Units/Transaction members.

Figure 167. ASOsamp.Sample Database Showing the Measures, Years, and Time Dimensions

This image shows an outline, as described in the text preceding the image.

When calculating the variance of the average units per transaction (cell C12 in Figure 166, Results from Spreadsheet Query of ASOsamp.Sample database Showing the Variance Between Two Ratios (C12)), the result could be the variance between the two ratios, or the result could be the ratio of the two variances. The result depends on whether Essbase gives precedence to the formula on Variance or the formula on Avg Units/Transaction.

The value of the solve order property, which is attached to the members in the database outline, determines the priority with which Essbase evaluates the formulas. The formula on the member that has the higher solve order takes precedence.

In the example, if the Variance member has a higher solve order than the Avg Units/Transaction member, then the formula on the Variance member takes precedence and the result is the variance between two ratios. This is the case in the ASOsamp.Sample database, because the solve order of the Variance member is 20 and the solve order of the Avg Units/Transaction member is 10. The formula on Variance takes precedence, because the Variance member has the higher solve order. The result for cell C12 of the query in Figure 166, Results from Spreadsheet Query of ASOsamp.Sample database Showing the Variance Between Two Ratios (C12) is the variance between the two ratios, as shown in Table 206:

Table 206. Using the Solve Order Property to Specify the Variance Between Two Ratios

Member

Solve Order

Formula

Result of Intersection of Variance and Avg Units/Transaction (cell C12 in Figure 166, Results from Spreadsheet Query of ASOsamp.Sample database Showing the Variance Between Two Ratios (C12))

Variance

20

Curr Year - Prev Year

Current year average units/transaction - previous year average units/transaction

0.94894382 (cell C6) - 0.954252111 (cell C9) = -0.005308291 (cell C12)

Avg Units/Transaction

10

Units/Transactions

Alternatively, if you change the ASOsamp.Sample database, and you give the Avg Units/Transaction member a higher solve order than the Variance member, then the formula on the Avg Units/Transaction member takes precedence, and the result is the ratio of two variances, as shown in Table 207 and in Figure 168, Results from Spreadsheet Query of ASOsamp.Sample Database Showing the Ratio of Two Variances (C12):

Table 207. Using the Solve Order Property to Specify the Ratio of Two Variances

Member

Solve Order

Formula

Result of Intersection of Variance and Avg Units/Transaction (cell C12 in Figure 168, Results from Spreadsheet Query of ASOsamp.Sample Database Showing the Ratio of Two Variances (C12))

Variance

10

Curr Year - Prev Year

Variance (current year to previous year) of units / variance of transactions

10585 (cell C10) / 11340 (cell C11) = 0.933421517 (cell C12)

Avg Units/Transaction

20

Units/Transactions

Figure 168. Results from Spreadsheet Query of ASOsamp.Sample Database Showing the Ratio of Two Variances (C12)

This image shows the results of a spreadsheet query, as described in the text preceding the image.

Aggregating an Aggregate Storage Database

Aggregate storage databases require no separate calculation step after data values are loaded into the level 0 cells of the outline. From any point in the database, users can retrieve and view values that are aggregated dynamically, for the current retrieval only. Aggregate storage databases are smaller than block storage databases, enabling quick retrieval of data values.

As databases grow, retrievals must process more data values to satisfy the calculation needs of the queries. For faster retrieval, Essbase enables you to precalculate data values and store those values in aggregations. If a database size nears one million aggregate cells, you should strongly consider performing an aggregation. Depending on database usage and the usage environment, you can achieve performance benefits by precalculating smaller databases as well. You can use either Administration Services Console or MaxL to calculate an aggregate storage database.

Understanding Aggregation-Related Terms

The following topics describe terms that are integral to an explanation of aggregate storage database calculation.

Aggregate Cells

Cells for level 0 intersections across dimensions, without formulas, are called input cells. Data values can be loaded to them. Higher-level cells involving members of the accounts dimension or dynamic hierarchies are always calculated at retrieval. All other higher-level intersections across dimensions are aggregate cells. For example, for the outline in Figure 163, Sample Aggregate Storage Outline, Price Paid > Curr Year > 1st Half > Portable Audio > CO is an aggregate cell; Original Price > Curr Year > Jan > Camcorders > CO is another aggregate cell. Values for aggregate cells must be rolled up from lower-level values.

Aggregate cell values are calculated for each request, or they can be precalculated and stored on disk.

Aggregate Views

When Essbase defines which aggregate cells to precalculate and store, it works with aggregate views. An aggregate view is a collection of aggregate cells. The collection is based on the levels of the members within each dimension.

For example, consider one aggregate view for the outline in Figure 163, Sample Aggregate Storage Outline. This aggregate view includes aggregate cells for the following dimension levels:

  • Measures dimension, level 0

  • Years dimension, level 0

  • Time dimension, level 1 of hierarchy 0

  • Product dimension, level 2 of hierarchy 0

  • Geography dimensions, level 0

The example aggregate view is shown as 0, 0, 1/0, 2/0, 0.

Each dimension is shown, left to right, in its sequence in the outline. If a dimension contains hierarchies, the notation specifies the member level within its hierarchy. Hierarchies within a dimension are numbered top-down, starting with hierarchy 0.

The 0, 0, 1/0, 2/0, 0 aggregate view contains aggregate cells that include the following member intersections:

Original Price, Curr Year, Qtr1, Personal Electronics, CO 
Original Price, Curr Year, Qtr1, Personal Electronics, KS 
Original Price, Curr Year, Qtr1, Home Entertainment,   CO 
Original Price, Curr Year, Qtr1, Home Entertainment,   KS 
Original Price, Curr Year, Qtr2, Personal Electronics, CO 
Original Price, Curr Year, Qtr2, Personal Electronics, KS 
Original Price, Curr Year, Qtr2, Home Entertainment,   CO 
Original Price, Curr Year, Qtr2, Home Entertainment,   KS 
Original Price, Curr Year, Qtr3, Personal Electronics, CO 
Original Price, Curr Year, Qtr3, Personal Electronics, KS 
Original Price, Curr Year, Qtr3, Home Entertainment,   CO 
Original Price, Curr Year, Qtr3, Home Entertainment,   KS 
Original Price, Curr Year, Qtr4, Personal Electronics, CO 
Original Price, Curr Year, Qtr4, Personal Electronics, KS 
Original Price, Curr Year, Qtr4, Home Entertainment,   CO 
Original Price, Curr Year, Qtr4, Home Entertainment,   KS 
Original Price, Prev Year, Qtr1, Personal Electronics, CO 
Original Price, Prev Year, Qtr1, Personal Electronics, KS 
Original Price, Prev Year, Qtr1, Home Entertainment,   CO 
Original Price, Prev Year, Qtr1, Home Entertainment,   KS 
and so on...
Aggregations

Aggregations are consolidations, based on outline hierarchy, of level 0 data values. An aggregation contains one or more aggregate views. Essbase provides an intelligent aggregation process that selects aggregate views to be rolled up, aggregates them, and then stores the values for the cells in the selected views. If an aggregation includes aggregate cells dependent on level 0 values that are changed through a data load, the higher-level values are automatically updated at the end of the data load process.

The term aggregation is used for the aggregation process and the set of values stored as a result of the process.

Aggregation Scripts

Each aggregation script is a file that defines a particular selection of aggregate views to be materialized. The Administration Services Aggregation Design Wizard enables you to create aggregation scripts and store them in the database directory as text files with the .csc extension. See Working with Aggregation Scripts.

Performing Database Aggregations

You can use either Administration Services Aggregation Design Wizard or MaxL statements to perform an aggregation. The aggregation process has two phases:

  • Aggregate view selection.

  • Calculation and storage of values for the selected aggregate views. This phase is also called the materialization of the aggregation.

During the aggregate view selection phase, Essbase analyzes how calculating and storing various combinations of aggregate views might affect average query response time. As input to the analysis, you can define physical storage and performance requirements. You can also track data usage and provide the information to the analysis process. See Selecting Views Based on Usage.

Based on their usefulness and resource requirements, Essbase creates a list of aggregate views. Included with each view in the list is storage and performance information that applies when that aggregate view plus all other aggregate views listed above it are stored. You can choose to aggregate the listed views, select and aggregate a subset of the listed views, or rerun the selection process with different input criteria. You can also add to an aggregation the materialization of new views that are based on new selection criteria. See Fine-Tuning Aggregate View Selection.

Whether or not you materialize the selection, you can save the selection of aggregate views as an aggregation script. Aggregation scripts provide flexibility and can save time because they enable you to bypass the selection process if the same selection is needed again. See Working with Aggregation Scripts.

After the selection process is finished, the selected aggregate views are calculated when you materialize the selected aggregate views into an aggregation.

The following process is recommended for defining and materializing aggregations:

  1. After the outline is created or changed, load data values.

  2. Perform the default aggregation.

    Optional: Specify a storage stopping point.

  3. Materialize the suggested aggregate views and save the default selection in an aggregation script.

  4. Run the types of queries for which the aggregation is designed.

  5. If query time or aggregation time is too long, consider fine-tuning the aggregation. See Fine-Tuning Aggregate View Selection.

  6. Optional: Save the aggregation selection as an aggregation script.

    See Working with Aggregation Scripts.

  To perform a database aggregation selection or materialization, use a tool:

Tool

Topic

Location

Administration Services

Calculating Aggregations to Improve Retrievals

Oracle Essbase Administration Services Online Help

MaxL

execute aggregate process

execute aggregate selection

execute aggregate build

Oracle Essbase Technical Reference

Note:

Aggregation Design Wizard enables running aggregation processes in the background. When you run an Administration Services process in the background, you can continue to use Administration Services Console for other activities simultaneously as the background process is running.

Also see Optimizing Aggregation Performance.

Fine-Tuning Aggregate View Selection

The default selection of aggregate views proposed by Essbase provides excellent performance. However, accepting all aggregate views in the selection list does not guarantee optimum performance. For the default selection, Essbase analyzes stored hierarchies and assumes an equal chance that any aggregate cell will be retrieved. Essbase cannot account for external factors such as the amount of available memory at the time of a query. Available memory can be affected by such factors as the cache memory definition at retrieval time, or the memory other concurrent processes require.

You may want to track which data is most queried and include the results and alternate views in the aggregate view selection process. See Selecting Views Based on Usage.

As you tune and test aggregations, consider the following points:

  • Improving retrieval performance can increase disk storage costs and the time it takes to materialize the aggregation.

  • Tracking queries may result in a set of proposed aggregate views that provide better performance for some queries than for others. Selecting proposed aggregate views can considerably improve performance time of some queries with others experiencing little improvement—but never worse—as long as query type and frequency are close to the type and frequency of queries performed during the tracking period.

  • Optimizing aggregations may require an iterative, fine-tuning process.

To estimate the size of aggregate views, you can use the ASOSAMPLESIZEPERCENT configuration setting in essbase.cfg to specify the number of sample cells Essbase uses. The sample size is specified as a percentage of input-level data. The default, and minimum, sample size is 1 million (1,000,000) cells. See the Oracle Essbase Technical Reference.

Essbase provides information to help you select and store the right balance of aggregate views for your database. Weigh this information against what you know about your database retrieval requirements and environment. Use the following information to help you select aggregate views for an aggregation:

  • The maximum storage requirement

    You can specify a storage limit for selecting aggregate views in two ways:

    • When the aggregation selection is initiated, you specify a maximum storage stopping value. Aggregate views are selected until the specified storage limit is reached or there are no more views to select.

      In Administration Services, the number you specify is the amount of storage in MB; in MaxL, the number is a factor times the size of the level 0 stored values. See the Oracle Essbase Administration Services Online Help and the Oracle Essbase Technical Reference.

    • After each analysis of the database, Essbase displays information about the level 0 input cell view followed by a list of suggested aggregate views. Displayed by each aggregate view is a storage number that includes that aggregate view and all other aggregate views it depends on. You can consider this storage number as you select the aggregate views to be included in the aggregation.

  • The relative “Query Cost” performance improvement

    The Query Cost number that is displayed by each aggregate view in the list projects an average retrieval time for retrieving values from the associated aggregate view. The default view selection estimates the cost as the average of all possible queries. When using query tracking, the estimated cost is the average for all tracked queries. The cost number for a specific aggregate view can be different in different selection lists; for example, aggregate view 0, 0, 1/0, 2/0, 0 can show a different query cost in the default selection list than it would show in a selection that includes tracked queries in the analysis.

    To compute the percentage improvement, divide the query cost value for the aggregate view into the query cost value shown for storing only level 0 input cells.

  • Tracked usage

    Before running an aggregate view selection, you can turn on query tracking to determine which data is retrieved most often. After some period of database activity, you can have Essbase include the usage statistics in the aggregation analysis process. See Selecting Views Based on Usage.

  • Aggregation time

    The time it takes to perform an aggregation after the selection process completes increases for each aggregate view materialized. To determine actual aggregation time, you must perform the aggregation.

The following process is recommended for fine-tuning aggregations:

  1. Perform the default aggregations described in Performing Database Aggregations.

  2. Save the default selection in an aggregation script. See Working with Aggregation Scripts.

  3. Turn on query tracking. See Selecting Views Based on Usage.

  4. Have users perform their usual queries against the database or perform the batch query operations for which the aggregation is being designed. Queries from all query tools are tracked.

  5. After sufficient time to capture data retrieval requirements, perform another aggregation including tracked data.

  6. Analyze the proposed list of aggregate views to be stored, and select the aggregate views that you determine provide the best balance of system resources and retrieval performance.

  7. Materialize the selected aggregate views and, if desired, save the selection in an aggregation script.

  8. Working with aggregation scripts and various selection criteria, repeat the process until you think you have the optimum selection of aggregate views for your situation.

Note:

To optimize aggregations for different database retrieval situations, such as for generating reports or user queries, you may need to repeat the tuning process, creating an aggregation script for each situation. See Working with Aggregation Scripts.

Selecting Views Based on Usage

Essbase enables you to capture retrieval statistics against a database. You can then use these statistics to build aggregations tailored to retrieval patterns in your company. Also, Essbase includes alternate hierarchies in its analysis of the database when usage information is used in the aggregate view selection process.

Database usage for periodic reporting may be different than for ongoing user retrievals. To optimize different retrieval situations, consider tracking situational usage patterns and creating aggregation scripts for each situation.

Before you begin the aggregation selection process, ensure that query tracking is on and that it has been on long enough to capture representative usage.

  To track data query patterns, use a tool:

Tool

Topic

Location

Administration Services

Tracking Query Data for Aggregation View Selection

Oracle Essbase Administration Services Online Help

MaxL

alter database

Oracle Essbase Technical Reference

Query tracking holds query usage information in memory. Performing any of the following operations clears query usage information.

  • Loading or clearing data

  • Materializing or clearing an aggregation

  • Turning off query tracking

Query tracking remains on until you turn it off, stop the application, or change the outline.

Understanding User-Defined View Selection

By default, Essbase uses internal mechanisms to decide how to create aggregations. User-defined view selection provides a way for you to influence default view selection and view selection based on query data. See Selecting Views Based on Usage.

Administrators may apply view selection properties to stored hierarchies to restrict Essbase from choosing certain levels for aggregation.

Note:

Secondary hierarchies are either shared or attribute hierarchies.

Property

Effect

Default

On primary hierarchies, Essbase considers all levels. It does not aggregate on secondary hierarchies unless alternative roll-ups are enabled.

Consider all levels

Considers all levels of the hierarchy as potential candidates for aggregation. This is the default for primary hierarchies, but not for secondary hierarchies.

Do not aggregate

Does not aggregate along this hierarchy. All views selected by Essbase are at the input level.

Consider bottom level only

Applies only to secondary hierarchies. Essbase considers only the bottom level of this hierarchy for aggregation.

Consider top level only

Applies only to primary hierarchies. Considers only top level of this hierarchy for aggregation.

Never aggregate to intermediate levels

Applies to primary hierarchies. Selects top and bottom levels only.

Note:

The bottom level of an attribute dimension consists of the zero-level attribute members. When a secondary hierarchy is formed using shared members, the bottom level comprises the immediate parents of the shared members.

Essbase considers only views that satisfy the selected view selection properties.

You should be familiar with the dominant query patterns of databases before changing default properties; preventing selection of certain views will make queries to those views slower while improving the speed of other queries. Similarly, enabling Consider All Levels on a secondary hierarchy may speed queries to that hierarchy while making other queries slower.

  To define view selection properties, use a tool:

Tool

Topic

Location

Administration Services

Setting View Selection Properties

Oracle Essbase Administration Services Online Help

MaxLexecute aggregate process and execute aggregate selectionOracle Essbase Technical Reference

Query Hints

Query hints tell Essbase what types of queries likely will occur. For example, to indicate queries at the bottom level of the time dimension, you can specify one member at the bottom level of time, such as January. This tells Essbase that any member at the bottom level of time likely will be queried. Essbase gives members indicated in query hints priority when selecting views, optimizing common queries.

If no member is specified for a dimension, it means that any member of that dimension may be used in a query. For example, using a query hint of (Sales, 100, East) on Sample.Basic means that profit margin measures for level 1 products at level 1 markets is a common type of query, regardless of Year and Scenario dimensions, which were omitted.

Usage-based view selection overrides query hints. See Selecting Views Based on Usage. User-defined view selection overrides query hints if there is a conflict between the two. See Understanding User-Defined View Selection

Asterisks “*” indicate that no member is selected for a dimension.

Hint

Use Case

(Jan, *, *, *, *,)

Frequent queries to the bottom level of time.

(Qtr1, *, *, *, Cola)

Queries often include members both at the bottom level of product and the quarterly level of time.

(Jan, *, *, NY, *) (Qtr1, *, *, NY, *)

Analysis at the state level of Market tends to stay at the Quarterly level or below.

Numerous hints can exist in the same outline. Any view that fits at least one hint is given more weight than views that fit none.

Query Hints cannot contain dynamic, label-only, or shared members.

  To apply query hints, use a tool:

Tool

Topic

Location

Administration Services

Query Hints

Oracle Essbase Administration Services Online Help

Working with Aggregation Scripts

Each aggregation script represents a specific aggregate view selection against a database.

Aggregation scripts can save you time. For example, after loading new data values you need not perform another aggregate view selection. You can speed the aggregation process by using the selection stored in an aggregation script to materialize the aggregation.

Aggregation scripts also give you flexibility. You can use them to save aggregate view selections optimized for different retrieval situations; for example, you can use one script to optimize retrievals in month-end reporting and another for daily retrieval requirements.

Aggregation scripts for a database become invalid when the selection it contains is invalid for the database. Create aggregation scripts when you create aggregations. Do not manually modify aggregation script files, which may cause unpredictable results. For information about when you must create aggregations and aggregation scripts, see Replacing Aggregations.

Creating Aggregation Scripts

Saved aggregation scripts enable you to split up the total aggregation process. You can materialize an aggregation at a different time than when the aggregate views for the aggregation are selected. The aggregation script contains information derived during the aggregate view selection phase.

  To create an aggregation script, use a tool:

Tool

Topic

Location

Administration Services

Calculating Aggregations to Improve Retrievals

Aggregation Design Wizard

Oracle Essbase Administration Services Online Help

MaxL

query database

execute aggregate selection

Oracle Essbase Technical Reference

Aggregation scripts are stored in the database directory as text files with the .csc extension and are valid as long as the dimension level structure in the outline has not changed. For information about when aggregation selections are invalid, see Replacing Aggregations. To avoid the potential clutter of invalid aggregation script files, use the Aggregation Design Wizard or manually delete aggregation scripts when they are no longer useful.

Executing Aggregation Scripts

Executing an aggregation script materializes the aggregate views specified within it. Although you can create multiple aggregation scripts, only one aggregation can be materialized at a time.

  To execute an aggregation script, use a tool:

Tool

Topic

Location

Administration Services

Calculating Aggregations to Improve Retrievals

Aggregation Design Wizard

Oracle Essbase Administration Services Online Help

MaxL

execute aggregate build

Oracle Essbase Technical Reference

Note:

When Aggregation Design Wizard displays the list of existing aggregation scripts, it lists all files with the .csc extension in the database directory. Only valid aggregation script files can be executed.

Optimizing Aggregation Performance

To possibly improve aggregation performance time, you can use the CALCPARALLEL configuration setting to increase the number of threads. Because each thread handles a task of building an aggregate view, the number of threads you define establishes how many aggregate views can be built concurrently. Even on single-CPU computers, increasing the CALCPARALLEL configuration setting may improve performance where building aggregates is I/O-bound, such as for databases too large to fit in memory. Because threads must share aggregate storage cache, the cache size must be sufficiently large to support the number of threads defined. Otherwise, increasing the number of threads could degrade aggregation performance time.

Note:

For small databases, the performance of building aggregate views in Essbase 9.3.1 and later versions may be slower than Essbase versions earlier than 9.3.1. However, Essbase 9.3.1 should perform better for databases larger than a few hundred million cells, especially on computers with more than two processors and where the CALCPARALLEL configuration setting has been chosen appropriately.

See the Oracle Essbase Technical Reference.

Clearing Aggregations

At times you might want to manually clear aggregations from the disk; for example, to make the disk space available for disk-intensive operations. Clearing aggregations clears all data, except level 0 values, from the database, releasing the disk area for other use. After aggregations are cleared, queries calculate retrieved values dynamically from level 0 values.

For information about when Essbase Server automatically clears aggregated data, see “Database restructure” in Table 192, Outline Differences Between Aggregate Storage and Block Storage.

  To clear aggregations, use a tool:

Tool

Topic

Location

Administration Services

Clearing Data from Aggregate Storage Databases

Oracle Essbase Administration Services Online Help

MaxL

alter database

Oracle Essbase Technical Reference

Replacing Aggregations

You can replace an aggregation by clearing the existing aggregation and materializing a different selection of aggregate views. You can perform a new aggregate view selection and materialization process, or you can run an aggregation script. Consider replacing the aggregation in the following situations:

  • To fine-tune the selection of aggregate views to improve performance. See Fine-Tuning Aggregate View Selection.

  • To create aggregations optimized for different database retrieval situations, such as for generating reports or user queries.

  • To optimize an aggregation after significant growth in database size. Gradually, as the size of a database increases, an aggregation can become less efficient. Consider replacing an aggregation when performance degradation is noticeable or when the database size increases to about 150% of its original size.

  • To optimize aggregations for new or different operational environments, such as memory and disk resource availability changes.

You must replace an aggregation and associated aggregation scripts after the number of levels in a dimension has been changed or one or more dimensions have been added or removed from the outline. See Performing Database Aggregations and Working with Aggregation Scripts.

Performing Time Balance and Flow Metrics Calculations in Aggregate Storage Accounts Dimensions

The topics in this section explain how to perform time balance and flow metrics calculations on aggregate storage databases.

Using Time Balance Tags in Aggregate Storage Accounts Dimensions

You can set Time Balance properties on aggregate storage Accounts dimensions to provide built-in calculations along the Time dimension. This saves time and performance overhead of using member formulas to achieve time-balance functionality.

The following time-balance properties are supported on stored or formula-bearing Accounts dimension members:

  • TB First, TB Last, TB Average

  • SKIP NONE, SKIP MISSING

Consider a stored measure such as Headcount in a human-resources application. Within a Year-Quarter-Months hierarchy, Headcount data is loaded at the month level.

This image shows an outline in which Headcount is tagged with the TB Last and Skip Missing time-balance properties.

The desired yearly or quarterly Headcount value is not the sum of its months; rather, it should be the last recorded value within a time period.

Tagging Headcount as TB Last with SKIPMISSING means that, for Year 2005, its value is the last nonempty value of the headcount for its months. If Dec has a nonmissing Headcount value, then that value will be returned; otherwise, the Nov value will be checked and returned if nonmissing.

If a formula-bearing member has a time balance tag, the formula is executed only for level 0 Time members, and the Time dimension is aggregated according to the time balance tag.

The time balance tags provide a built-in calculation along the Time dimension. To perform other time-based calculations using formulas, such as period-to-date and rolling averages, you can create a dimension called TimeView and write all time-based formulas on that dimension. Doing so enables you to use Time Balance calculation functionality without losing the ability to perform other time-based calculations.

Using Flow Tags in Aggregate Storage Accounts Dimensions

A Flow tag can be assigned to Accounts dimension members bearing formulas.

The following example describes the problem to be solved with flow metrics. Assume you have Sales and Additions figures for all 12 months. You want to perform an aggregation to populate each month’s beginning inventory.

Table 208. Inventory Calculation

 SalesAdditionsInventory
Jan5150
Feb6346
Mar4243
Apr7041
...   

You would use an MDX formula on the Beginning Inventory member in order to calculate its value. Without flow metrics, to obtain each month’s beginning inventory, the calculator engine would have to reiterate the MDX formula exponentially.

Inventory = SUM(MemberRange(Jan:Time.CurrentMember), (Additions - Sales)) + Beg_Inventory

To optimize the illustrated example, assign the Inventory member the formula (Addition – Sales), and tag the member as Flow.

Restrictions on Alternate Hierarchies

If alternate hierarchies are used in the aggregate storage time dimension, the following restrictions apply when using Flow and TB tags on the Accounts dimension:

  1. The shared level among alternate time hierarchies must be level 0.

  2. The order of members at shared level among alternate time hierarchies must be the same in all alternate hierarchies.

Aggregating Time-Balance Tagged Measures

The MDX Aggregate function can be used to aggregate measures tagged with time balance tags. See the Oracle Essbase Technical Reference.

Effect of Attribute Calculations on Time Balance Measures in Aggregate Storage Databases

The following calculation logic applies if

  1. The aggregate storage outline contains a time dimension or date-time dimension with one or more attribute or linked-attribute dimensions.

  2. You perform queries on time balance tagged measures.

If the above cases are both true, MDX Aggregate() semantics are used to evaluate the cells.

For example, consider a scenario in which:

  • Year is a time-date dimension with a day level hierarchy.

  • Holiday is an attribute dimension on Year, with each date tagged with Holiday_TRUE or Holiday_FALSE.

  • Opening Inventory is tagged as TBFirst.

The value of (Year, Holiday_TRUE, [Opening Inventory]) is evaluated according to the following MDX logic:

Aggregate( {Set of dates that are holidays in Year}, [Opening Inventory])

The topics in this section describe how Essbase retrieves data from aggregate storage databases.

Also see Table 196, Query Differences Between Aggregate Storage and Block Storage.

Attribute Calculation Retrievals

Aggregate storage applications support only the Sum member of the Attribute Calculations dimension. If you specify any other member name from the Attribute Calculations dimension, such as Min or Avg, an error is returned. See Understanding the Attribute Calculations Dimension.

Retrieval Tools Supporting Aggregate Storage Databases

Essbase provides the following programs and tools that enable data retrieval from aggregate storage databases:

  • MDX Queries

  • Report Writer which is run through Administration Services Console or the export data using report_file MaxL statement

Note:

Commands that support block-storage-only features (for example, the <SPARSE Report Writer command) cannot be used with aggregate storage databases. MDX queries fully support aggregate storage features.