Hierarchies in ASO Cubes

Dimensions in Essbase outlines contain one or more hierarchies of related levels and members. Hierarchies are stored or dynamic. Stored hierarchies aggregate quickly, based on outline structure. Dynamic hierarchies are calculated at retrieval time, using formulas and solve order. An alternate hierarchy is a structure of attribute or shared members.

The Time dimension in the ASOsamp.Basic database includes the hierarchies MTD, QTD, and YTD:

Figure 37-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 cube, hierarchies can be stored or dynamic.

A dimension may contain both stored and dynamic hierarchies. Each type has its own advantages and restrictions. To use multiple hierarchies in a dimension (even if they are all stored), you must enable multiple hierarchies for that dimension.

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 tagged as a dynamic hierarchy).

Note:

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

In the Essbase web interface, you enable multiple hierarchies for a dimension using a member property.

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

In the hierarchy shown below, 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.

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

In the Essbase web interface, you specify a stored hierarchy using a member 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 or 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 top of a stored or dynamic hierarchy. The first hierarchy in the dimension must be stored.

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 and Solve Order in ASO Cubes.

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 using a member 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 as an alternate hierarchy, one useful query is a crosstab report of attribute dimension members by base dimension members. For example, a crosstab report of product sales information could show size attributes (such as small and large) on columns and products on rows. If you use shared members as an alternate hierarchy, you cannot create the equivalent crosstab report query of the shared members by prototype members.
As a hierarchy of shared members. The alternate hierarchy has shared members that point to prototype members of previous hierarchies in the outline. The shared members roll up according to a different hierarchy than the prototype members. Shared members on dynamic hierarchies can have formulas. The following table shows the hierarchies for the ASOsamp.Basic database. The Products dimension is shown in the illustration that follows.

Table 37-1 Example Hierarchies and Alternate Hierarchies for the Product Dimension of ASOsamp.Basic

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 37-3 Aggregate Storage Outline Displaying the Alternate Hierarchy High End Merchandise on the Product Dimension

Alternate Hierarchy Restrictions for Shared Members

The following restrictions apply for alternate hierarchies in aggregate storage outlines:

The prototype instance of the member must occur in the outline before any shared instances of the member. For example, in the above outline, 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 the above outline, 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.
Prototype 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 prototype 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 cube, a shared member automatically shares any attributes that are associated with its prototype member.