Comparison of Aggregate and Block Storage

In This Section:

Introduction

Inherent Differences

Outline Differences

Calculation Differences

Partitioning Differences

Data Load Differences

Query Differences

Feature Differences

Introduction

Essbase provides an aggregate storage kernel as a persistence mechanism for multidimensional databases. Aggregate storage databases enable dramatic improvements in both database aggregation time and dimensional scalability. The aggregate storage kernel is an alternative to the block storage kernel. Aggregate storage databases typically address read-only, “rack and stack” applications that have large dimensionality, such as the following applications:

  • Customer analysis. Data is analyzed from any dimension, and there are potentially millions of customers.

  • Procurement analysis. Many products are tracked across many vendors.

  • Logistics analysis. Near real-time updates of product shipments are provided.

A sample application (ASOsamp), a data file, and a rules file are provided to demonstrate aggregate storage functionality.

Aggregate storage applications, which differ from block storage applications in concept and design, have limitations that do not apply to block storage applications. The following sections describe the differences.

Inherent Differences

Table 192. Inherent Differences Between Aggregate Storage and Block Storage

Inherent Differences

Aggregate Storage

Block Storage

Storage kernel

Architecture that supports rapid aggregation, optimized to support high dimensionality and sparse data

Multiple blocks defined by dense and sparse dimensions and their members, optimized for financial applications

Physical storage definition

Through the Application Properties window, Tablespaces tab in Administration Services

Through the Database Properties window, Storage tab in Administration Services

Create database

Migrate a block storage outline or define after application creation

Note:

Do not use the file system to copy a block storage outline into an aggregate storage application. Use the migration wizard in Administration Services to migrate the outline.

Define after application creation

Copy database

Not supported

Supported

Databases supported per application

One

Several (one recommended)

Application and database names

See Naming Restrictions for Applications and Databases. Names reserved for tablespaces, cannot be used as application or database names:

  • default

  • log

  • metadata

  • temp

See Naming Restrictions for Applications and Databases.

Application and database information display

Displayed in the Application Properties window and the Database Properties window in Administration Services.

Information not supported by or relevant to aggregate storage applications is not shown. For a description of aggregate storage-specific information, see the Oracle Essbase Administration Services Online Help for the Application Properties window and Database Properties window.

Displayed in the Application Properties window and the Database Properties window in Administration Services

Configuration settings (essbase.cfg)

For a list of the settings that apply to aggregate storage databases, see the Oracle Essbase Technical Reference.

For a list of the settings that do not apply to block storage databases, see the Oracle Essbase Technical Reference.

Outline Differences

Table 193. Outline Differences Between Aggregate Storage and Block Storage

Outline Functionality

Aggregate Storage

Block Storage

Dense or sparse dimension designation

Not relevant

Relevant

Multiple hierarchies enabled, dynamic hierarchy, or stored hierarchy designation

Relevant

Irrelevant

Accounts dimensions and members on dynamic hierarchies

Support with the following exceptions:

  • No two-pass calculation (however, for information on specifying the calculation order, see Calculation Order)

  • No association of attribute dimensions with the dimension tagged Accounts

  • Additional restrictions for shared members. See Alternate Hierarchies.

Full support

Members on stored hierarchies

Support with the following exceptions:

  • Support for the ~ (no consolidation) operator (underneath label-only members only) and the + (addition) operator

  • Cannot have formulas

  • Restrictions on label only members (See Member storage types.)

  • No Dynamic Time Series members

  • Stored hierarchy dimensions cannot have shared members. Stored hierarchies within a multiple hierarchies dimension can have shared members. See Stored Hierarchies.

Full support

Member storage types

Support with the following exceptions:

  • Dynamic Calc and Store not relevant

  • On stored hierarchies, two limitations if a member is label only:

    • All dimension members at the same level as the member must be label only

    • The parents of the member must be label only.

Note:

On dynamic hierarchies, ability to tag any member as label only

Note:

On conversion from a block storage database, attribute dimension members are tagged as Dynamic Calc. On standard dimension members Dynamic Calc tags are converted and tagged as stored members, which changes the Members Stored value on the Dimensions tab of the Database Properties window in Administration Services.

Support for all member storage types in all types of dimensions except attribute dimensions

Ragged hierarchies and hierarchies with more than 10 levels

Support, with possible performance impact

Support

Outline validation

  • When database is started

  • When outline is saved

  • When block storage outline is converted to aggregate storage outline

  • When user requests

  • When outline is saved

  • When user requests

Outline paging

Support

No support

Database restructure

Levels of restructure; see Aggregate Storage Database Restructuring

Levels of restructure; see Optimizing Database Restructuring

Calculation Differences

Table 194. Calculation Differences Between Aggregate Storage and Block Storage

Calculation Functionality

Aggregate Storage

Block Storage

Database calculation

Aggregation of the database, which can be predefined by defining aggregate views

Calculation script or outline consolidation

Formulas

Allowed with the following restrictions:

  • Must be valid numeric value expressions written in MDX; cannot contain the % operator—replace with expression: (value1 / value2) * 100

  • No support for Essbase calculation functions

  • On dynamic hierarchy members, formulas are allowed without further restrictions

Support for Essbase calculation functions

Calculation scripts

Not supported

Supported

Attribute calculations dimension

Support for Sum

Support for Sum, Count, Min, Max, and Average

Calculation order

Member formula calculation order can be defined by the user using the solve order member property

Defined by the user in the outline consolidation order or in a calculation script

Partitioning Differences

Table 195. Partitioning Differences Between Aggregate Storage and Block Storage

Partitioning Functionality

Aggregate Storage

Block Storage

Partitioning

Support with the following restrictions:

  • No outline synchronization

Fully supported

Data Load Differences

Table 196. Data Load Differences Between Aggregate Storage and Block Storage

Data Load Functionality

Aggregate Storage

Block Storage

Cells loaded through data loads

Only level 0 cells whose values do not depend on formulas in the outline are loaded

Cells at all levels can be loaded (except Dynamic Calc members)

Update of database values

At the end of a data load, if an aggregation exists, the values in the aggregation are recalculated

No automatic update of values. To update data values, you must execute all necessary calculation scripts.

Data load buffers

The loading of multiple data sources into aggregate storage databases is managed through temporary data load buffers

Not supported

Atomic replacement of the contents of a database

When loading data into an aggregate storage database, you can replace the contents of the database or the contents of all incremental data slices in the database

Not supported

Data slices

Aggregate storage databases can contain multiple slices of data. Data slices can be merged

Not supported

Dimension build for shared members

Full support for parent-child build method. Duplicate generation (DUPGEN) build method limited to building alternate hierarchies up to generation 2 (DUPGEN2).

Support for all build methods

Loading data mapped to dates

In a date-time dimension, you can load data into level 0 members using supported date-format strings instead of member names

Date-time dimension type is not supported

Query Differences

Table 197. Query Differences Between Aggregate Storage and Block Storage

Query Functionality

Aggregate Storage

Block Storage

Report Writer

Supported, except for commands related to sparsity and density of data

Fully supported

Spreadsheet Add-in

Supported, with limited ability to change data (write-back)

Fully supported

API

Supported

Supported

Export

Support with the following restrictions:

  • Export of level 0 data only (no upper-level export)

  • No columnar export

Supported

MDX queries

Supported

Supported

Queries on attribute members that are associated with non-level 0 members

Returns values for descendants of the non-level 0 member.

See also Design Considerations for Attribute Queries.

Returns #MISSING for descendants of the non-level 0 member

Queries on attribute members and shared members

A shared member automatically shares the attribute associations of its nonshared member

A shared member does not share the attribute associations of its nonshared member

Query logging

Not supported

Supported

Query performance

Considerations when querying data from a dimension that has multiple hierarchies.

See Query Design Considerations for Aggregate Storage.

Hierarchies not relevant

Feature Differences

Table 198. Feature Differences Between Aggregate and Block Storage

Features

Aggregate Storage

Block Storage

Aliases

Supported

Supported

Currency conversion

Not supported

Supported

Data mining

Not supported

Supported

Hybrid analysis

Support with the following restriction: queries that contain a relational member and an Essbase member with a formula in the same query are not supported.

For example, if California is a relational member, and the member Profit has a formula, the following report script returns an error:

Jan 
California 
Profit 
!

Supported

Incremental data load

Supported

Supported

LROs

Not supported

Supported

Time balance reporting

Support with the following restrictions:

  • Skip Zeros is not supported

  • Time dimension must contain only stored hierarchies

  • Shared members must be at level zero

Supported

Triggers

After-update triggers supported

On-update triggers and after-update triggers supported

Unicode

Supported

Supported

Variance reporting

Not supported

Supported

Date-time dimension type and linked attribute dimensions

Supported

Not supported

User ability to change data (write-back)

Transparent partition technique used to enable limited write-back

Fully supported