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:

Create a new aggregate storage application and database. The aggregate storage outline is created automatically when you create the database. You can use an application workbook to create it.
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

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

To convert a block storage outline to an aggregate storage outline, you can use the create outline MaxL statement.

Note:

Do not use the file system to copy a block storage outline into an aggregate storage application.

To create an aggregate storage application or database, use an application workbook or use these MaxL statements:

create application
create database

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

Figure 35-1 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.

In the Essbase web interface, you enable multiple hierarchies for a dimension as a cube information property.

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, the dimension is automatically tagged 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 Outline Differences.

In the hierarchy shown in Alternate Hierarchies, 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.

In the Essbase web interface, you specify a stored hierarchy as an information property.

You can tag the top member of the hierarchy as top of a stored hierarchy by using the import database MaxL statement.

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.

In the Essbase web interface, you specify a dynamic hierarchy as an information property.

You can tag the top member of the hierarchy as top of a dynamic hierarchy using the import data MaxL statement.

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.

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

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 referenced members in the primary hierarchy.
As a hierarchy of shared members. The alternate hierarchy has shared members that point to referenced members of previous hierarchies in the outline. The shared members roll up according to a different hierarchy from the referenced members. Shared members on dynamic hierarchies can have formulas. The following table shows the hierarchies for the ASOsamp.Sample database. The Products dimension is shown in the illustration that follows.

Table 35-1 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 35-2 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 referenced instance of the member must occur in the outline before any shared instances of the member. For example, in Figure 35-2, 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 35-2, 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.
Referenced 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 referenced 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 referenced member.

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.

The following table describes attribute query types and how Essbase calculates the query:

Table 35-2 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 illustration below, 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 35-3 Outline for Attribute Query Example

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:

Select the hierarchy that you want to query.
Navigate to find the detailed data (for example, by zooming in on the hierarchy in Smart View).

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.

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.

Viewing Compression Estimation Statistics

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, you can use the aggregate storage version of the query database MaxL statement.

See the following descriptions of 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.

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

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)

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 Aggregate Storage Outline File

The outline file (.otl) for an aggregated storage outline increases in size as the outline changes; for example, when members are added or deleted. You can decrease the outline file size by compacting it. After the outline file is compacted, the file continues to grow as before, when members are added or deleted.

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.

Note:

When a member is deleted from the outline, the corresponding record of that member in the outline file is marked as deleted but the record remains in the outline file. Compacting the outline file does not remove the records of deleted members.

To compact an outline file, you can use the alter database MaxL statement.