Design Considerations for Aggregate Storage Outlines

This topic lists key design considerations when designing the outline for your Essbase aggregate storage (ASO) cube. How queries perform has much to do with how you plan the outline. Use the recommendations here when considering which hierarchies to implement as stored, dynamic, or alternate hierarchies (attribute dimensions and shared members).

For an implemented example of the design recommendations, see the ASOsamp.Basic cube, available in the gallery.

Designing the ASO Outline

Consider the following information when designing an aggregate storage outline:

Use stored hierarchies (rather than dynamic hierarchies) as much as possible.
Use 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.

Attribute Dimensions and ASO Cubes

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

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

Only the addition (+) consolidation operator is available within attribute dimensions.
For a given attribute dimension, all associations must be with one level of the base dimension. For example, in the ASOsamp.Basic cube, 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.Basic database, Essbase treats the Store Manager attribute dimension as if the Store Manager dimension were a stored alternate hierarchy of the Stores dimension.

When query tracking is enabled, Essbase considers queries on attribute dimension data, and may include attribute dimension members in aggregate view selections. Refer to Select Views Based on Usage.

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 cubes is different from the behavior in block storage cubes.

Attribute Queries on ASO Cubes

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 37-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 37-4 Outline for Attribute Query Example

Attribute Queries and Multiple Hierarchies

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.