Aggregate Storage Applications, Databases, and Outlines

In This Section:

Introduction

Process for Creating Aggregate Storage Applications

Creating Aggregate Storage Applications, Databases, and Outlines

Developing Formulas on Aggregate Storage Outlines

Using a Transparent Partition to Enable Write-Back for Aggregate Storage Databases

The information in this chapter applies to aggregate storage applications, databases, and outlines and includes information on how these processes differ from block storage processes.

Also see:

Introduction

Aggregate storage applications and databases and block storage applications and databases differ in concept and design. Some block storage outline features do not apply to aggregate storage. For example, the concept of dense and sparse dimensions does not apply. See Comparison of Aggregate and Block Storage.

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

Process for Creating Aggregate Storage Applications

This topic provides a high-level process for creating an aggregate storage application.

  1. Create an aggregate storage application, database, and outline.

    See Creating Aggregate Storage Applications, Databases, and Outlines.

  2. Use tablespaces to optimize data storage and retrieval.

    See Managing Storage for Aggregate Storage Applications.

  3. Specify the maximum size of the aggregate storage cache.

    See Managing the Aggregate Storage Cache.

  4. Load data into the aggregate storage database. A data load can be combined with a dimension build.

    See Preparing Aggregate Storage Databases. You can preview a subset of the data in Administration Services. See “Previewing Data” in the Oracle Essbase Administration Services Online Help.

  5. Precalculate chosen aggregations to optimize retrieval time.

    See Calculating Aggregate Storage Databases.

  6. View database statistics.

    See “Viewing Aggregate Storage Statistics” in the Oracle Essbase Administration Services Online Help.

  7. If required, enable write-back by using the Aggregate Storage Partition Wizard.

    See Using a Transparent Partition to Enable Write-Back for Aggregate Storage Databases.

  8. View data using Oracle tools (for example, Smart View) or third-party tools.

Creating Aggregate Storage Applications, Databases, and Outlines

You must create an aggregate storage application to contain an aggregate storage database. An aggregate storage application can contain only one database. You can create an aggregate storage application, database, and outline in the following ways:

  • Convert a block storage outline to an aggregate storage outline, and then create an aggregate storage application to contain the converted database and outline.

    Essbase supports the following scenarios for converting block storage outlines to aggregate storage outlines:

    • Non-Unicode block storage outline to non-Unicode aggregate storage outline

    • Non-Unicode block storage outline to Unicode aggregate storage outline

    • Unicode block storage outline to Unicode aggregate storage outline

    The following conversion scenarios are not supported:

    • Unicode block storage outline to non-Unicode aggregate storage outline

    • Aggregate storage outline to a block storage outline

  • Create an aggregate storage application and database. The aggregate storage outline is created automatically when you create the database.

For information on loading dimensions and members into an aggregate storage outline, see Building Dimensions in Aggregate Storage Databases and Loading Data into Aggregate Storage Databases.

Aggregate storage application and database information differs from block storage information, and specific naming restrictions apply to aggregate storage applications and databases. See Table 191.

  To convert a block storage outline to an aggregate storage outline, use a tool:

Tool

Topic

Location

Administration Services

Converting Block Storage Outlines to Aggregate Storage

Aggregate Storage Outline Conversion Wizard

Oracle Essbase Administration Services Online Help

MaxL

create outline

Oracle Essbase Technical Reference

Note:

Do not use the file system to copy a block storage outline into an aggregate storage application. Use the Aggregate Storage Outline Conversion Wizard in Administration Services to convert the outline.

  To create an aggregate storage application or database, use a tool:

Tool

Topic

Location

Administration Services

Creating Applications

Creating Databases

Oracle Essbase Administration Services Online Help

MaxL

create application

create database

Oracle Essbase Technical Reference

When creating aggregate storage applications, databases, and outlines, consider the differences between aggregate storage and block storage and issues specific to aggregate storage. See the following sections, and also see Aggregate Storage Time-Based Analysis.

Hierarchies

In aggregate storage outlines and block storage outlines, dimensions are structured to contain one or more hierarchies of related levels and members within the levels. For example, the Time dimension in the ASOsamp.Sample database includes the hierarchies MTD, QTD, and YTD, as shown in Figure 155, Outline Showing Multiple Hierarchies and Members on the Time Dimension.

Figure 155. Outline Showing Multiple Hierarchies and Members on the Time Dimension

This image shows an outline in which the Time dimension is enabled for multiple hierarchies.

In an aggregate storage database, you can create two types of hierarchies:

  • Stored

  • Dynamic

The two types of hierarchies have different advantages and restrictions. A dimension may contain both types of hierarchies. To use multiple hierarchies in a dimension (even if they are all stored hierarchies), you must enable multiple hierarchies for that dimension.

  To enable multiple hierarchies for a dimension, tag the dimension member as multiple hierarchies enabled using a tool:

Tool

Topic

Location

Administration Services

Defining Hierarchies in Aggregate Storage Outlines

Oracle Essbase Administration Services Online Help

MaxL

import database

Oracle Essbase Technical Reference

When you tag a dimension member as multiple hierarchies enabled, it is automatically tagged label only.

If you do not tag the dimension as multiple hierarchies enabled, Essbase automatically tags the dimension as a stored hierarchy (except the dimension tagged as Accounts, which is automatically tagged as a dynamic hierarchy).

Note:

The first hierarchy in a multiple hierarchies enabled dimension must be a stored hierarchy.

Stored Hierarchies

Members of stored hierarchies are aggregated according to the outline structure. Because aggregate storage databases are optimized for aggregation, the aggregation of data values for stored hierarchies is very fast. To allow this fast aggregation, members of stored hierarchies have the following restrictions:

  • Stored hierarchies can have the no-consolidation (~) operator (only underneath label only members) or the addition (+) operator.

  • Stored hierarchies cannot have formulas.

Stored hierarchies have restrictions on label only members. See Table 192, Outline Differences Between Aggregate Storage and Block Storage.

In Figure 156, Aggregate Storage Outline Displaying the Alternate Hierarchy High End Merchandise on the Product Dimension, the All Merchandise hierarchy and the High End Merchandise hierarchy are stored hierarchies. The All Merchandise member and the High End Merchandise member are the tops of the hierarchies and are both tagged as top of a stored hierarchy.

  To specify a stored hierarchy, tag the top member of the hierarchy as top of a stored hierarchy using a tool:

Tool

Topic

Location

Administration Services

Defining Hierarchies in Aggregate Storage Outlines

Oracle Essbase Administration Services Online Help

MaxL

import database

Oracle Essbase Technical Reference

The following members can be tagged as top of a stored hierarchy:

  • A dimension member (generation 1). If a dimension member is tagged as top of a stored hierarchy, the entire dimension is considered a single stored hierarchy, and no other member in the dimension can be tagged as top of a stored hierarchy or top of a dynamic hierarchy.

  • The children of the dimension member (generation 2). If a generation 2 member is tagged as top of a stored hierarchy, all generation 2 members in the dimension also must be tagged as either top of a stored hierarchy or top of a dynamic hierarchy. The first hierarchy in the dimension must be a stored hierarchy.

The dimension tagged as accounts is automatically considered a dynamic hierarchy. You cannot specify the accounts dimension as a stored hierarchy.

Dynamic Hierarchies

To evaluate a dynamic hierarchy, Essbase calculates, rather than aggregates, the members and formulas. The order in which members and formulas are evaluated is defined by the solve order property. See Calculation Order.

At the time of retrieval, Essbase calculates the required member combinations and calculates any required outline member formulas. Because dynamic hierarchies are calculated, the data retrieval time may be longer than for data retrieved from stored hierarchies. However, when you design your database, dynamic hierarchies provide the following advantages:

  • They can contain any consolidation operator.

  • They can have formulas.

  To specify a dynamic hierarchy, tag the top member of the hierarchy as top of a dynamic hierarchy using a tool:

Tool

Topic

Location

Administration Services

Defining Hierarchies in Aggregate Storage Outlines

Oracle Essbase Administration Services Online Help

MaxL

import database

Oracle Essbase Technical Reference

The following members can be tagged as top of a dynamic hierarchy:

  • A dimension member (generation 1). If a dimension member is tagged as top of a dynamic hierarchy, the entire dimension is considered a single dynamic hierarchy, and no other member in the dimension can be tagged as top of a dynamic hierarchy or top of a stored hierarchy.

  • The children of the dimension member (generation 2). If a generation 2 member is tagged as top of a dynamic hierarchy, all generation 2 members in the dimension must also be tagged as either top of a dynamic hierarchy or top of a stored hierarchy. The first hierarchy in the dimension must be a stored hierarchy.

Note:

If a member has the no-consolidation operator (~) on all its children, the member must be tagged label only.

The dimension tagged accounts is automatically considered a dynamic hierarchy. You cannot specify the accounts dimension as a stored hierarchy.

Essbase cannot select dynamic hierarchy members for an aggregate view. See Aggregating an Aggregate Storage Database.

Alternate Hierarchies

An alternate hierarchy may be modeled in either of the following ways:

  • As an attribute dimension, which uses attributes to classify members logically within the dimension (for example, a Product dimension can have attributes such as Size and Flavor). See Working with Attributes.

    Note:

    If you use an attribute dimension to create an alternate hierarchy, you can create a crosstab report query of members in the attribute dimension with members in the base dimension. For example, a crosstab report of product sales information could show size attributes (such as small and large) as column headings and products as row headings. If you use shared members to create an alternate hierarchy, you cannot create an equivalent crosstab report query of the shared members with the nonshared members in the primary hierarchy.

  • As a hierarchy of shared members. The alternate hierarchy has shared members that refer to nonshared members of previous hierarchies in the outline. The shared members roll up according to a different hierarchy from the nonshared members to which they refer. Shared members on dynamic hierarchies can have formulas. See Understanding Shared Members. Table 198 shows the hierarchies for the ASOsamp.Sample database. The Products dimension is shown in Figure 156, Aggregate Storage Outline Displaying the Alternate Hierarchy High End Merchandise on the Product Dimension.

Table 198. Example Hierarchies and Alternate Hierarchies for the Product Dimension of ASOsamp.Sample

Product

Hierarchy

Alternate Hierarchy (containing shared members)

Flat Panel

Products, All Merchandise, Personal Electronics, Home Entertainment, Televisions

Products, High End Merchandise

HDTV

Products, All Merchandise, Personal Electronics, Home Entertainment, Televisions

Products, High End Merchandise

Figure 156. Aggregate Storage Outline Displaying the Alternate Hierarchy High End Merchandise on the Product Dimension

This image shows an outline in which the Products dimension includes the High End Merchandise alternate hierarchy.

The following restrictions apply when creating alternate hierarchies in aggregate storage outlines:

  • The nonshared instance of the member must occur in the outline before any shared instances of the member. For example, in Figure 156, Aggregate Storage Outline Displaying the Alternate Hierarchy High End Merchandise on the Product Dimension, the member HDTV occurs in the All Merchandise hierarchy before it occurs as a shared member in the alternate hierarchy of High End Merchandise.

  • The first hierarchy in a dimension where multiple hierarchies are enabled cannot contain a shared member.

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

  • To ensure that values are not double-counted, a stored hierarchy cannot contain multiple copies of the same shared member. For example, a stored hierarchy cannot contain a shared member and any of its ancestors. In Figure 156, Aggregate Storage Outline Displaying the Alternate Hierarchy High End Merchandise on the Product Dimension, you cannot add the shared member “Televisions” as a child of “High End Merchandise,” because doing so would make “Televisions” a sibling of its children, shared members “Flat Panel” and “HDTV,” causing the values of “Flat Panel” and “HDTV” to be added twice.

  • Nonshared instances of a member must be in the same dimension as the shared member (same for block storage outlines).

  • A stored hierarchy cannot contain a nonshared instance and a shared instance of the same member.

  • A stored hierarchy can contain a shared instance of a dynamic hierarchy member only if the dynamic hierarchy member is a level 0 member without a formula.

Note:

In an aggregate storage database, a shared member automatically shares any attributes that are associated with its nonshared member. This also applies to an implied shared member (for example, a member that has only one child). See Understanding Implied Sharing. You can prevent implied sharing by setting the Never Share property; see Determining How Members Store Data Values. This behavior of shared members and attributes in aggregate storage databases is different from the behavior in block storage databases.

Attribute Dimensions

This topic provides information on the differences between aggregate storage and block storage databases with regard to attribute dimensions. To use the information in this topic, you should be familiar with attribute dimension concepts for block storage databases. See Working with Attributes.

The following information applies to attribute dimensions when used on aggregate storage databases:

  • Only the addition (+) consolidation operator is available for attribute dimensions.

  • For a given attribute dimension, all associations must be with one level of the base dimension. For example, in the ASOsamp.Sample database, associations for the Store Manager attribute dimension are with level 0 of the Stores dimension. The following restrictions apply to attribute associations:

    • Level 0: You can associate attributes with any level 0 member of a dynamic or stored hierarchy that does not have a formula.

    • Non-level 0: You can associate attributes only to upper level members in the primary stored hierarchy.

Attribute dimensions do not have hierarchy types. You cannot specify an attribute dimension as a dynamic or stored hierarchy. Essbase treats attribute dimensions as stored alternate hierarchies of the base dimension. For example, in the ASOsamp.Sample database, Essbase treats the Store Manager attribute dimension as if the Store Manager dimension were a stored alternate hierarchy of the Stores dimension.

When using query tracking, Essbase considers queries on attribute dimension data and may include attribute dimension members in aggregate view selections. See Selecting Views Based on Usage and Calculating Aggregate Storage Databases.

Note:

Queries on attribute members that are associated with non-level 0 members return values for descendants of the non-level 0 member. This behavior of queries on attribute members in aggregate storage databases is different from the behavior in block storage databases.

Design Considerations for Attribute Queries

When selecting and building views based on attribute query data, some queries on attribute data are always dynamically calculated at the time of retrieval, which may affect query performance.

Every query involving attribute dimension members must also include at least one member from the base dimension. If the query involves a single attribute dimension and a sum-of-all dimension member, Essbase aggregates the query data, potentially improving query performance. In other cases, Essbase must calculate the query at the time of retrieval.

Table 199 describes attribute query types and how Essbase calculates the query:

Table 199. Attribute Queries and Calculation Performance

Attribute Query Type

Query Calculation Type

Query involves a sum-of-all base dimension member and members from one attribute dimension.

Essbase can aggregate query data, potentially improving query performance.

Query involves any member of the base dimension and members from multiple attribute dimensions.

Essbase calculates the query at the time of retrieval based on the level 0 input data.

Query involves any child member of the base dimension member (or dimension member that is tagged as label-only) and members from one attribute dimension.

Essbase calculates the query at the time of retrieval based on the level 0 input data, or on data from aggregations on the base dimension.

In the outline displayed in Figure 157, Outline for Attribute Query Example, RealDimension is the sum of all its descendents (it is not tagged as label-only). If a query involves one or more members from a single attribute dimension (for example, AttributeDimension1), crossed with the base dimension member (RealDimension), Essbase can build aggregate cells for the data, potentially improving query performance.

The following queries, however, are always calculated at the time of retrieval:

  • Any query requesting data for members from an attribute dimension (for example AttributeDimension1) and any of the children of RealDimension is calculated dynamically at retrieval time based on the level 0 input data or on data from aggregations.

  • Any query requesting data from multiple attribute dimensions (for example AttributeDimension1 and AttributeDimension2) and a base member dimension (for example RealDimension) is calculated dynamically at retrieval time based on level 0 input data.

    Figure 157. Outline for Attribute Query Example

    This image shows an outline used as a attribute query example, as described in the text preceding the image.

Design Considerations for Aggregate Storage Outlines

This topic lists the key design considerations when you create aggregate storage database outlines. For an example of implementing these design considerations, see the ASOsamp.Sample database. Consider the following information when designing an aggregate storage outline:

  • Use stored hierarchies (rather than dynamic hierarchies) as much as possible.

  • Use alternate hierarchies (shared members) only when necessary.

  • Minimize the number of hierarchies. (For example, each additional stored hierarchy slows down view selection and potentially increases the size of the aggregated data).

  • If a hierarchy is a small subset of the first hierarchy, consider making the small hierarchy a dynamic hierarchy. Considerations include how often the hierarchy data is queried and the query performance impact when it is dynamically queried at the time of retrieval.

  • The performance of attributes is the same as for members on a stored hierarchy.

  • The higher the association level of an attribute to the base member, the faster the retrieval query. (See also, Design Considerations for Attribute Queries).

Query Design Considerations for Aggregate Storage

When querying data from a dimension that has multiple hierarchies, query performance may improve if you query the data in the following way:

  1. Select the hierarchy that you want to query.

  2. Navigate to find the detailed data (for example, by zooming in on the hierarchy in Oracle Hyperion Essbase Spreadsheet Toolkit).

Including dynamic hierarchy members and stored hierarchy members in the same query may require a large internal memory cache, which decreases query performance.

64-bit Dimension Size Limit for Aggregate Storage Database Outline

An aggregate storage database outline cannot exceed 64-bits per dimension.

The number of bits needed by a dimension is the maximum number of bits used by any level 0 child, including the level 0 children in alternate hierarchies and associated attribute dimensions. For the purposes of member numbering, attribute dimensions are treated as alternate hierarchies of their base dimensions.

In general, the formula to determine the number of bits required for any member in a dimension can be expressed as:

#_bits_member’s_parent + log(x)

where x is the number of children of the parent.

For example, if the member’s parent is member A, which requires 5 bits, and A has 10 children, the number of bits required by each child is:

5 +log(10) = 9 bits

The top member of a dimension or hierarchy usually uses 0 bits. However, when one or more top generations consist of label-only members, the label-only members do not receive member numbers (because they are not considered stored members). Therefore, if there are x members in the first non-label-only generation, those members use log(x) bits. The rest of the children below them are numbered normally.

Similarly, if a dimension or hierarchy is dynamic, only the level 0 members that are stored or shared receive member numbers. The number of bits required for those members is log(x), where x is the number of level 0 members that are stored or shared (that is, the number of level 0 members that are not formula members).

If, however, any alternate hierarchies have stored (non-shared) level 0 members, each member of every hierarchy in the dimension (including associated attribute dimensions) uses an extra log(x) bit, where x is the total number of hierarchies and associated attribute dimensions for this base dimension.

The following example uses the Products dimension in the ASOsamp.Sample database:

This image shows the Products dimension, as described in the text following the image.

The Products dimension has two hierarchies: All Merchandise and High End Merchandise, which is an alternate hierarchy. High End Merchandise has one stored level 0 member: Stored Member. The Products dimension does not have any associated attribute dimensions.

Members All Merchandise and High End Merchandise use log(2) = 1 bit.

Note:

If the alternate hierarchy High End Merchandise did not have any stored level 0 members, the top members of each hierarchy (and associated attribute dimensions) would each use 0 bits.

The calculation of the number of bits required by each level 0 children:

All Merchandise = 1 bit
   Personal Electronics, Home Entertainment, Other = 1 + log(3) = 3 bits
      Digital Cameras/Camcorders, Handhelds/PDAs, Portable Audio = 3 + log(3) = 5
         Children of Digital Cameras/Camcorders = 5 + log(3) = 7
         Children of Handhelds/PDAs = 5 + log(3) = 7
         Children of Portable Audio = 5 + log(2) = 6
      Televisions, Home Audio/Video = 3 + log(2) = 4
         Children of Televisions = 4 + log(5) = 7

         Children of Home Audio/Video = 4 + log(4) = 6
      Computers and Peripherals = 3 + log(1) = 3 **
      Systems, Displays, CD/DVD drives = 3 + log(3) = 5
         Children of Systems = 5 + log(2) = 6
   High End Merchandise = 1 bit
      Flat Panel, HDTV, Stored Member = 1 + log(3) = 3 bits

Member Computers and Peripherals has the same number of bits (3) as its parent Other.

The maximum bits used by any level 0 children in the Products dimension is 7 (Children of Digital Cameras and Children of Televisions). Therefore, Products uses 7 bits, which is less than the dimension size limit of 64 bits.

If the dimension size exceeds 64 bits:

  • Essbase generates the following error when saving the outline:

    Hierarchy [DimensionName] is too complex. It exceeds the maximum member number width of 64 bits. See application log for details.
    
  • Essbase logs messages similar to the following messages in the application log:

    Member number for member [level0member] requires [65] bits to encode
    Member [level0member] contributes [5] bits to member number
    Member [level1parent] contributes [20] bits to member number
    Member [level2parent] contributes [20] bits to member number
    Member [level3parent] contributes [20] bits to member number
    

To fix the error, use one of these recommendations:

  • If possible, delete some siblings of any of the members referenced in the messages. Reducing the number of siblings by a power of two saves one bit. For instance, assume that level0member, which contributes 5 bits to the member number, has 18 siblings, including itself. Reducing the number of siblings to 16 or fewer saves one bit because log(16) = 4. Similarly, reducing the number of siblings to 8 or fewer saves two bits.

  • Reclassify some siblings of members referenced in the messages. For example, move half of level0member’s 18 siblings to another parent that doesn’t have as many children. Alternately, create a new parent as a sibling of level1parent and move half of level1parent’s children under the new member. This approach saves one bit.

  • Combine some intermediate levels. For instance, move level0member, and all of its siblings, to be children of level2parent and then remove level1parent. This approach is more involved but it can save many bits.

  In Administration Services Console, to see the number of bits a dimension requires:

  1. From the Enterprise view or a custom view, select a database.

  2. Right-click, and select Edit and then Properties.

  3. In the Database Properties dialog box, select the Statistics tab.

    In the Aggregate Storage Statistics area, the number of levels and bits used in each dimension is displayed.

Understanding the Compression Dimension for Aggregate Storage Databases

By default, the compression dimension in an aggregate storage database is the Accounts dimension. Changing the compression dimension triggers a full restructure of the database. Essbase requires the compression dimension to be a single dynamic hierarchy. If the dimension has a different hierarchy setting, such as multiple hierarchies, it will be set to single dynamic hierarchy automatically. The original hierarchy setting is lost (setting a different dimension as compression does not return the original hierarchy setting). Attribute dimensions cannot be compression dimensions, nor can dimensions with attributes associated to them.

The choice of compression dimension can significantly affect performance. A good candidate for a compression dimension is one that optimizes data compression while maintaining retrieval performance. This topic provides information about choosing an optimal compression dimension.

Note:

The information in this topic applies to loaded databases. See Loading Data into Aggregate Storage Databases.

Maintaining Retrieval Performance

Because compression dimensions are dynamically calculated, you must take into account design considerations for dynamically calculated dimensions when choosing a compression dimension. Dynamic dimensions are calculated at the time of retrieval, so the data retrieval time is longer than for stored hierarchies.

If a dimension with a large number of level 0 members is tagged as compression, upper-level queries take longer because they require many level 0 members to be retrieved. If users will be doing many upper-level retrievals on a large dimension, it is not a good candidate for a compression dimension.

Managing Database Compression While Maintaining Retrieval Performance

Another consideration when choosing a compression dimension is how well it is expected to compress the database. The size of the compressed database changes depending on which dimension you tag as compression.

In Administration Services, you can view compression estimates and then choose a compression dimension in the same dialog box. Selecting a new compression dimension in Administration Services restructures the outline automatically.

  To view the expected level 0 size of the database for each dimension when hypothetically tagged as compression, and to choose a compression dimension, see “Selecting a Compression Dimension for Aggregate Storage” in the Oracle Essbase Administration Services Online Help.

Viewing Compression Estimation Statistics

In Administration Services and in MaxL, you can view detailed compression and query statistics. You can view the number of stored level 0 members, which affects retrieval performance; the average bundle fill and average value length, which affect compression; and the level 0 size.

  To view detailed query and compression statistics, use a tool:

Tool

Topic

Location

Administration Services

Database Properties Window

Oracle Essbase Administration Services Online Help

MaxL

Query Database (Aggregate Storage)

Oracle Essbase Technical Reference

The following sections describe each of the compression and query related statistics.

Stored level 0 members

Dimensions with a large number of stored level 0 members do not perform well if tagged Compression. As with any dynamically calculated dimension, upper-level retrievals from compression dimensions generally are slow. See Maintaining Retrieval Performance.

Average bundle fill

Compression is more effective if values are grouped together in consecutive members on dimensions or hierarchies rather than spread throughout the outline with lots of #MISSING data between values. Essbase saves memory by storing information about the location and contents of the groups rather than storing it separately for each of the members. The average bundle fill is the average number of values stored in the groups. It can vary between 1 and 16, with 16 being the best. Choosing a compression dimension that has a higher average bundle fill means that the database compresses better.

In some outlines, you can improve compression by ordering the numbers in the compression dimension so that members that are frequently populated are grouped together. When populated members are grouped together, more values fit into each bundle, increasing the average bundle fill and improving compression.

Average value length

The average value length is the average storage size, in bytes, required for the stored values in the cells. It can vary between 2 bytes and 8 bytes with 2 bytes being the best. Without compression, it takes 8 bytes to store a value in a cell. With compression, it can take fewer bytes, depending on the value length. For example, 10.050001 might take 8 bytes to store even when compressed, but 10.05 may only take 2 bytes (4 bytes to store when compressed). Dimensions with a smaller average value length compress the database better.

Rounding the data values to no more that two digits after the decimal point can reduce the average value length, improving compression.

Expected level 0 size

This field indicates the estimated size of the compressed database. A smaller expected level 0 size indicates that choosing this dimension is expected to enable better compression.

Verifying Outlines

Aggregate storage outline files have the same file extension (.otl) as block storage database outline files and are stored in an equivalent directory structure. When you save an outline, Essbase verifies it for errors. You can also verify the accuracy of an outline before you save it. Some block storage database features do not apply to aggregate storage databases, and the verification process considers the rules for aggregate storage databases. See Comparison of Aggregate and Block Storage.

  To verify an aggregate storage outline, see “Verifying Outlines” in the Oracle Essbase Administration Services Online Help.

Outline Paging

Aggregate storage database outlines are pageable. This feature may significantly reduce memory usage for very large database outlines. For aggregate storage databases, Essbase preloads part of the database outline into memory. Then, during data retrieval, Essbase pages other parts of the outline into memory as required.

When you create an aggregate storage database, the outline is created in a pageable format. When you use the Aggregate Storage Outline Conversion Wizard to convert an existing block storage outline to aggregate storage, the outline is automatically converted to a pageable format.

Paging an outline into memory enables Essbase to handle very large outlines (for example, 10 million or more members) but potentially increases data retrieval time.

Note:

Aggregate storage databases that have pageable outlines contain memory pages, and therefore their outline files may be larger than binary block storage database outline files.

Outline Paging Limits

The maximum size of a buildable outline (the number of members) depends on several factors:

  • The available memory for Essbase

  • The amount of memory in Essbase allocated for other uses

  • The amount of memory required for each member (and aliases for each member)

Table 200 shows the amount of addressable memory available for Essbase on supported 32-bit operating systems:

Table 200. Addressable Memory for 32-bit Operating Systems

Operating System

Addressable Memory

Windows 2003 Server

3 GB

Requires a setting in the boot.ini file

AIX

3.25 GB

Up to 13 (256 MB) segments. Requires setting the LDR_CNTRL environment variable to:

0xD0000000@DSA

HP-UX

2.9 GB

Requires using the following command to set the addressable memory for the Essbase server process, ESSSVR:

chatr +q3p enable ESSSVR

Redhat Linux

3.9 GB available by default

Solaris

3.9 GB available by default

Essbase uses about 40 MB of memory on startup. In addition, the various caches require the following memory allocations:

  • Outline paging cache: 8 MB

  • Aggregate storage data cache: 32 MB

  • Aggregate storage aggregation cache: 10 MB

Therefore, the initial memory footprint for Essbase is about 90 MB. In addition, memory must be allocated to process incoming query requests. Typical memory to reserve for this purpose is about 300 MB. The total memory allocated for Essbase is therefore 390 MB.

On a Windows system with 1.85 GB of addressable memory, the amount available to build and load the outline is about 1.46 GB (1.85 GB - 390 MB = 1.46 GB).

The maximum outline size depends on whether it is built using a dimension build or from an outline already loaded into Essbase.

Dimension Build Limit

To build the outline by using a dimension build, Essbase allocates about 100 bytes per member, plus the size of the member name, plus the size of all alias names for the member (up to 10 aliases are allowed).

For a sample outline (using a single byte codepage) where the average member name is 15 characters and there is one alias (of 20 characters) per member, the memory requirement for each member that is added:

100 + 15 + 20 bytes = 135 bytes

The total number of members that can be added in a dimension build is the available memory (1.46 GB, or 153,092,060 bytes) divided by the number of bytes per member (135), approximately 11 million members.

On systems with more than 2 GB of addressable memory, the outline can be larger in proportion to the extra memory that is available.

When the dimension build is complete, a dbname.otn file is saved in the database directory. The .otn file is used as input for the outline restructuring process, which replaces the old outline with the new one. During restructuring, two copies of the outline are loaded into memory, the old one (potentially empty), and the new one, so the maximum size of an outline that can be restructured depends on the size of the old outline.

In a dimension build, which starts with an empty outline, only one outline is loaded into memory.

Loaded Outline Limit

The memory requirement for an outline loaded into Essbase at runtime or during restructuring is different from the memory requirements for a dimension build. Essbase allocates about 60 bytes per member, plus the size of the member name plus 5 bytes, plus the size of all alias names for the member (up to 10 aliases are allowed) plus 5 bytes. For a sample outline where the average member name is 15 characters and there is one alias (of 20 characters) per member, the memory requirement for each member that is added:

60 + 15 + 5 + 20 + 5 bytes = 105 bytes per member

Assuming 1.46 GB of available memory, the maximum size of an outline that can be loaded is one with 14 million members (1.46 GB / 105 bytes).

The 14 million members are the sum of two outlines that are loaded during restructuring. For example, if an existing outline has 5 million members, the new outline can have a maximum of 9 million members. In an incremental dimension build, it is recommended to build the smaller dimensions first and the larger ones last to allow for a maximum outline size.

Compacting the Outline File

When you delete members from an aggregate storage outline, the corresponding records of members in the outline file (.otl file) are marked as deleted but remain in the file. The outline file continues to grow as members are deleted and added. You can minimize the outline file size by compacting the file to remove the records of deleted members. Compacting the outline file causes Essbase to restructure the outline and can take place only when no other users or processes are actively using the database. Compacting the outline does not cause Essbase to clear the data.

  To compact an outline file, use a tool:

Tool

Topic

Location

Administration Services

Compacting the Outline File

Oracle Essbase Administration Services Online Help

MaxL

alter database

Oracle Essbase Technical Reference

ESSCMD

COMPACTOUTLINE

Oracle Essbase Technical Reference

Developing Formulas on Aggregate Storage Outlines

Formulas calculate relationships between members in a database outline. If you are familiar with using formulas on block storage outlines, consider the following differences when using formulas on aggregate storage outlines:

  • Essbase provides a native calculation language (the Calc language, or Calc) to write formulas on block storage outlines. To write formulas for aggregate storage outlines, the MDX (Multidimensional Expressions) language is required.

  • Apply formulas directly to members in the database outline. For block storage databases, formulas can be placed in a calculation script. For aggregate storage databases, you cannot place formulas in a calculation script.

The current chapter concentrates on using MDX to write formulas on aggregate storage databases. For information about using MDX to write queries, see Writing MDX Queries. For information about writing formulas for block storage outlines, see Developing Formulas for Block Storage Databases. Also see the MDX section of the Oracle Essbase Technical Reference.

Using MDX Formulas

An MDX formula must always be an MDX numeric value expression. In MDX, a numeric value expression is any combination of functions, operators, and member names that does one of the following actions:

  • Calculates a value

  • Tests for a condition

  • Performs a mathematical operation

A numeric value expression is different from a set expression. A set expression is used on query axes and describes members and member combinations. A numeric value expression specifies a value.

A numeric value expression is used in queries to build calculated members, which are logical members created for analytical purposes in the WITH section of the query, but which do not exist in the outline.

The following query defines a calculated member and uses a numeric value expression to provide a value for it:

WITH MEMBER
 [Measures].[Prod Count]
AS
 'Count (
    Crossjoin (
     {[Units]},
     {[Products].children}
    )
  )', SOLVE_ORDER=1
SELECT
 {[Geography].children}
ON COLUMNS,
 {
  Crossjoin (
     {[Units]},
     {[Products].children}
    ),
   ([Measures].[Prod Count], [Products])
 }
ON ROWS
FROM
 ASOsamp.Sample

In the sample query, the WITH clause defines a calculated member, Product Count, in the Measures dimension, as follows:

WITH MEMBER
 [Measures].[Prod Count]

The numeric value expression follows the WITH clause and is enclosed in single quotation marks. In the sample query, the numeric value expression is specified as follows:

'Count ( 
    Crossjoin (
     {[Units]},
     {[Products].children}
    )
  )'

The SOLVE_ORDER property specifies the order in which members and formulas are evaluated. See Calculation Order.

Note:

For an explanation of the syntax rules used to build the numeric value expression in the example, see the documentation in the Oracle Essbase Technical Reference for the Count, CrossJoin, and Children functions.

A numeric value expression also can be used as an MDX formula to calculate the value of an existing outline member.

Therefore, rather than creating the example query, you can create an outline member on the Measures dimension called Prod Count that is calculated in the outline in the same way that the hypothetical Prod Count was calculated in the sample query.

  To create a calculated member with a formula:

  1. Create a member.

  2. Attach an MDX formula to the member.

    Assuming that you created the example Prod Count member, you would use the following formula, which is the equivalent of the numeric value expression used to create the calculated member in the example query:

    Count(Crossjoin ( {[Units]}, {[Products].children}))
  3. Verify the formula by verifying the outline.

    When you retrieve data from the aggregate storage database, the formula is used to calculate the member value.

    You can use substitution variables within formulas. For example, you could define a substitution variable named “EstimatedPercent” and provide different percentages as substitution variable values. See Using Substitution Variables.

Before applying formulas to members in the outline, you can write MDX queries that contain calculated members. When you can write an MDX query that returns the calculated member results that you want, you are ready to apply the logic of the numeric value expression to an outline member and validate and test the expression. See Writing MDX Queries. For syntax information about MDX, see the Oracle Essbase Technical Reference.

Formula Calculation for Aggregate Storage Databases

Essbase calculates formulas in aggregate storage outlines only when data is retrieved. Calculation order may affect calculation results. Whenever you use MDX formulas on multiple dimensions in an aggregate storage outline, it is good practice to set the solve order for each member or dimension. See Calculation Order.

Note:

When designing an aggregate storage database calculation, consider that aggregate storage database members with MDX formulas are dynamically calculated. The dynamically calculated members have a value of #MISSING until they are queried.

Formula Syntax for Aggregate Storage Databases

When you create member formulas for aggregate storage outlines, observe the following rules:

  • Enclose member names in brackets ([]) if they meet any of the following conditions:

    • Start with a number or contains spaces; for example, [100]. Brackets are recommended for all member names, for clarity and code readability.

    • Are the same as an operator or function name. See the Oracle Essbase Technical Reference for a list of operators and functions.

    • Include a nonalphanumeric character; for example, a hyphen (-), an asterisk (*), or a slash (/).

    Note:

    In formulas, member names starting with $ or & must be enclosed in quotation marks as well as brackets. For example, $testmember would be expressed in the formula as ["$testmember"]/100

  • Use the IIF function to write conditional tests with a single else condition. The syntax for the IIF function does not require an ELSEIF keyword to identify the else condition nor an ENDIF keyword to terminate the statement. You can nest IIF functions to create a more complex formula.

  • Use the CASE, WHEN, THEN construct to write conditional tests with multiple conditions.

  • Be certain that tuples are specified correctly. A tuple is a collection of members with the restriction that no two members can be from the same dimension. Enclose tuples in parentheses; for example, (Actual, Sales).

  • Be certain that sets are specified correctly. A set is an ordered collection of one or more tuples. When a set has multiple tuples, the following rule applies: In each tuple of the set, members must represent the same dimensions as do the members of other tuples of the set. Additionally, the dimensions must be represented in the same order. In other words, all tuples of the set must have the same dimensionality.

    See Rules for Specifying Sets.

    Enclose sets in braces, for example:

    { [Year].[Qtr1], [Year].[Qtr2], [Year].[Qtr3], [Year].[Qtr4] }

Creating Formulas on Aggregate Storage Outlines

You use Formula Editor to create formulas. Formula Editor is a tab in the Member Properties dialog box in Outline Editor. Formulas are plain text. You can type the formulas directly into the formula text area, use a predefined formula template, or you can create a formula in the text editor of your choice and paste it into Formula Editor.

You can also include formulas in a dimension build data source. See Setting Field Type Information.

  To create a formula, see “Creating Formulas for Aggregate Storage Databases” in the Oracle Essbase Administration Services Online Help.

Checking Formula Syntax

Essbase includes MDX-based syntax checking that tells you about syntax errors in formulas. For example, Essbase tells you if you have mistyped a function name or specified a nonexistent member. Unknown names can be validated against a list of function names. If you are not connected to Essbase Server or to the application associated with the outline, Essbase may connect you to validate unknown names.

Syntax checking occurs when you save a formula. Errors are displayed in the Messages panel. If an error occurs, you choose to save or not save the formula. If you save a formula with errors, you are warned when you verify or save the outline. When you calculate a formula with errors, the formula is ignored and the member is given a value of $MISSING.

A syntax checker cannot warn you of semantic errors in a formula. Semantic errors occur when a formula does not work as you expect. One way to find semantic errors in a formula is to place the numeric value expression that defines the formula into a query and run the query to verify that the results are as you expect. See Using MDX Formulas to see how to place a formula into a query.

You can use MDX Script Editor to create a query. MDX Script editor provides features such as color coding and autocompletion of MDX syntax. See “About MDX Script Editor” in the Oracle Essbase Administration Services Online Help.

Displaying Formulas

  To display a formula, use tool:

Tool

Topic

Location

Administration Services

Creating Formulas for Aggregate Storage Databases

Oracle Essbase Administration Services Online Help

MaxL

query database

Oracle Essbase Technical Reference

ESSCMD

GETMBRCALC

Oracle Essbase Technical Reference

Composing Formulas on Aggregate Storage Outlines

The following sections discuss and give examples of how to write a variety of formulas for members in aggregate storage outlines.

Basic Equations for Aggregate Storage Outlines

You can apply a mathematical operation to a formula to create a basic equation. For example, the following formula is applied to the Avg Units/Transaction member in the ASOsamp.Sample database:

[Units]/[Transactions]

The formula in Avg Units/Transaction divides the number of units by the number of transactions to arrive at the average number of units per transaction.

In aggregate storage outlines, members cannot be tagged as expense items. Therefore, functions in Calc, such as @VAR and @VARPER, which determine the difference between two members by considering expense tags, are not relevant in aggregate storage outlines.

The MDX subtraction operator can be used to calculate the difference between two members. For example, the following formula can be applied to a new member, called Price Diff, in ASOsamp.Sample, to calculate the difference between the price paid and the original price:

[Price Paid]-[Original Price]

Members Across Dimensions in Aggregate Storage Outlines

ASOsamp.Sample provides a formula on a member called % of Total. This member formula identifies the percentage of the Measures total that is produced by Transactions. The formula for % of Total:

Transactions/
(Transactions,Years,Months,[Transaction Type],[Payment Type],
Promotions,Age,[Income Level],Products,Stores,Geography)

The formula specifies a member (Transactions) divided by a tuple (Transactions, Years, ...). The formula lists a top member from every dimension to account for all Transaction data in the cube; that is, not Transaction data for the Curr Year member but Transaction data for all members of the Years dimension, not Transaction data for months in the first two quarters but Transaction for all months, and so on. In this way, the value of % of Total represents the percentage of the Measures total that are produced by Transactions.

Conditional Tests in Formulas for Aggregate Storage Outlines

You can define a formula that uses a conditional test or a series of conditional tests to determine the value for a member. Use the IIF function to perform a test with one else condition. You can nest IIF functions to create a more complex query.

The example specifies a formula for a member that represents the price the company must pay for credit card transactions, which includes a 5% charge. The following example assumes that the Credit Price member has been added to the Measures dimension of the ASOsamp.Sample database. Credit Price has the following formula, which adds 5% to Price Paid when the payment type is a credit card.

IIF (
   [Payment Type].CurrentMember=[Credit Card],
   [Price Paid] * 1.05, [Price Paid]
)

Use the CASE, WHEN, THEN construct to create formulas with multiple tests and else conditions.

The Filter function returns the tuples of the input set that meet the criteria of the specified search condition. For example, to establish a baseline (100) for all products, you can add a Baseline member and create a formula for it, as follows:

Count(Filter(Descendants([PersonalElectronics],
[Products].Levels(0)),[Qtr1] > 100.00))

Specifying UDAs in Formulas in Aggregate Storage Outlines

UDAs are words or phrases that you create for a member. For example, in Sample.Basic, top-level members of the Market dimension have the UDA Small Market or the UDA Major Market.

The Major Market example used in this topic shows how to create a formula for a member that shows the sum of sales for all major market members. The example assumes that a new member (Major Market Total) has been added to Sample.Basic.

  1. MDX provides a Boolean function, IsUDA, which Returns TRUE if a member has the associated UDA tag. The following syntax returns TRUE if the current member of the Market dimension has the UDA “Major Market”:

    IsUda([Market].CurrentMember, "Major Market")
  2. A Filter function, when used with IsUDA (as shown in the following syntax), cycles through each member of the Market dimension and returns a value for each member that has the Major Market UDA:

    Filter([Market].Members, IsUda([Market].CurrentMember, "Major Market"))
  3. The Sum function adds the values returned by the Filter function; for the Major Market example, the following formula is produced:

    Sum (Filter([Market].Members, IsUda([Market].CurrentMember, "Major Market")))

    This formula is attached to the Major Market Total member.

Using a Transparent Partition to Enable Write-Back for Aggregate Storage Databases

With a write-back partition, you can update data on-the-fly in the target block storage database while the data in the source aggregate storage database remains unchanged. Creating a write-back partition potentially decreases calculation time and reduces database size.

When creating write-back partitions, follow these guidelines:

  • Create the block storage database in a separate application from the one in which the aggregate storage database is located.

    Typically, the block storage database contains a subset of the dimensions in the aggregate storage database.

  • Create a transparent partition based on where you want the data to be stored. Make the block storage database the target and the aggregate storage database the source.

    See Designing Partitioned Applications.

    Note:

    You may want to partition on the time dimension if data for some time periods is stored in the aggregate storage database and data for other time periods is stored in the block storage database. For example, if you have actual data for January through March, which is stored in an aggregate storage database, and you want to budget for the last nine months of the year using write-back members in a block storage database.

Users query and write-back to the block storage database. Queries are processed by the block storage database or transparently by the aggregate storage database.

  To create an aggregate storage and block storage write-back partition, use a tool:

Tool

Topic

Location

Administration Services

Aggregate Storage Partition Wizard

Creating Partitions

Oracle Essbase Administration Services Online Help

MaxL

create database

create partition

Oracle Essbase Technical Reference

You need Database Manager permissions to create a partitioned application.

Figure 158, Transparent Partition Used for Analyzing Variance Between Forecast and Actual Data illustrates using a transparent partition for analyzing the variance between forecast and actual data:

Figure 158. Transparent Partition Used for Analyzing Variance Between Forecast and Actual Data

This image shows a transparent partition that is used to analyze the variance between forecast and actual data.

The following procedure is based on the aggregate storage sample database (ASOsamp.Sample), and uses the Administration Services Aggregate Storage Partition Wizard (see the Oracle Essbase Administration Services Online Help).

  To create a write-back partition:

  1. Select the ASOsamp.Sample database, which contains the actual data for the current year and for previous years.

    See Figure 159, ASOsamp.Sample Aggregate Storage Database Outline.

    Figure 159. ASOsamp.Sample Aggregate Storage Database Outline

    This image shows an outline in which the Years dimension contains current and previous year data.
  2. Create a block storage database containing the following subset of dimensions from ASOsamp.Sample: Measures, Years, Time, Products, Stores, and Geography.

  3. Edit the Years dimension to add the following members to the block storage database outline:

    • A member called Next Year, which will contain the forecast data.

    • A member called Forecast Variance. Add a formula to this member to calculate the variance between actual and forecast data.

      Figure 160. Block Storage Database Outline Showing Years Dimension Members for Calculating Variance Between Actual and Forecast Data

      This image shows a block storage database outline, as described in the text preceding the image.
  4. Delete the following member formulas:

    • Measures dimension member formulas on Avg Units/Transaction and % of Total

    • Years dimension member formulas on Variance and Variance%

    • Time dimension member formulas under QTD and YTD

    These formulas are expressions written in MDX that are copied from the aggregate storage database. MDX formula expressions cannot be interpreted in block storage databases.

  5. Link the databases with a transparent partition on the Years dimension, with the block storage database (forecast data) as the target and the aggregate storage database (actual data) as the source.

    Do not include the write-back members (Forecast and Variance) in the partitioned area.

    Note:

    When using the Administration Services Aggregate Storage Partition wizard, this step is automatic. The databases are automatically partitioned on the Years dimension because you selected the Years dimension in step 3. The write-back members are not included in the partitioned area.

    You input forecast values into the block storage database write-back members. Because the added members are outside the partitioned area, you can write to them and then calculate data and generate reports based on updated data. The transparent partition provides a seamless view of both databases.