Aggregate Storage Database Restructuring

Database restructures may be forced by some aggregate storage database outline changes, including changes to hierarchies. A hierarchy comprises a top member and its descendants.

A dynamic hierarchy includes only one stored level. The Accounts dimension is a dynamic hierarchy.
An attribute dimension is one hierarchy. The generation 1 member is the top member of the hierarchy.
If a standard dimension is not tagged as multiple hierarchies enabled, it is one hierarchy. The generation 1 member is the top member of the hierarchy.
If a standard dimension is tagged as multiple hierarchies enabled, it contains multiple hierarchies. The generation 2 members are the top members of the hierarchies. For example, the Products dimension in ASOsamp.Sample contains two hierarchies. The top members are the generation 2 members All Merchandise and High End Merchandise.

What outlines changes affect:

Whether data must be cleared from the database before restructuring
The time and storage required to restructure the outline

Levels of Aggregate Storage Database Restructuring

To minimize the time and storage needed for database restructures, if a database outline changes frequently, analyze the outline and the types of outline changes.

Levels of restructuring for aggregate storage databases, listed from most to least expensive (in regard to time, storage, and data):

Table 40-1 Aggregate Storage Restructuring Levels

User-Outline Changes	Essbase-Restructure Level	Performance Impact
Add, delete, or move a standard dimension	Clears data and aggregate views, and performs full outline restructure	Very high User must reload input (level 0) data, select the aggregate views, and rerun the database aggregation.
Add, delete, or move a hierarchy. Change the number of stored levels in a hierarchy. See: Example: No Change in the Number of Stored Levels in a Hierarchy Example: Change in the Number of Stored Levels in a Hierarchy Change the top member of a stored hierarchy from label-only to stored or from stored to label-only. Change a dynamic hierarchy to a stored hierarchy or a stored hierarchy to a dynamic hierarchy. Change a primary or an alternate hierarchy so that it matches or no longer matches its primary or alternate hierarchy. All level 0 members of a primary hierarchy must be represented directly or indirectly (for example, a parent that is a sum of its children may represent its children) in all alternate hierarchies. The top level of the primary hierarchy must equate to the top level of each alternate hierarchy. See Example: Changes in Alternate Hierarchies.	Clears aggregate views, and performs full outline restructure	Very high Storage requirement is up to three times the size of the database file (`.dat` file). User must select the aggregate views and rerun the database aggregation.
Perform a change that is not included in other categories; for example, delete or move a member, or add a member that is not the last of its siblings	Performs full outline restructure	High Storage requirement is up to three times the size of the database file (`.dat` file).
Perform a light restructure change (described below) to an alternate hierarchy or an attribute dimension	Rebuilds all aggregate views that are based on attribute dimensions or alternate hierarchies	Low Storage requirement is up to three times the size of the affected views. Such aggregate views normally exist only if you used query tracking to select views based on usage. See Selecting Views Based on Usage.
On nonattribute dimensions without stored level 0 members (for example, all level 0 members are shared or have formulas), add a child or child branch without changing the number of levels in the hierarchy and without crossing a power of 2 boundary.	Performs light outline restructure Note: If the number of levels in the hierarchy changes, Essbase clears all aggregate views and performs a full outline restructure. Performance impact is Very High. If the number of levels in the hierarchy does not change, but adding a child or child branch crosses a power of 2 boundary, Essbase performs a full outline restructure. Performance impact is High.	Very low
On nonattribute dimensions with stored level 0 members: Add a child as the last child of a parent without crossing a power of 2 boundary (1, 2, 4, 8, 16, and so on). For example, if a parent member has three children, you may add a fourth child as the last child of the parent. Add a child branch as the last child branch of an existing parent without crossing a power of 2 boundary and without changing the number of levels in the hierarchy. Examples: Renames a member Changes a formula Changes an alias Changes a dynamic hierarchy consolidation operator (for example, from + to -)	Performs light outline restructure.	Very low
On nonattribute dimensions with stored level 0 members: Add a child that crosses a power of 2 boundary as the last child of a parent. For example, if a parent member has three children and you add a fourth and fifth child, the fifth child crosses the power of 2 boundary. See Example: Addition of Child Members. For scenarios in which adding a child branch as the last child branch of an existing parent that crosses a power of 2 boundary or changing the number of levels in the hierarchy, which triggers a full outline restructure, see Example: Addition of Child Branches.	Clears aggregate views, and performs full outline restructure	Very high

Outline-Change Examples

This section contains examples of the more complicated outline changes described in Levels of Aggregate Storage Database Restructuring.

Example: No Change in the Number of Stored Levels in a Hierarchy

In ASOsamp.Sample, the Measures dimension is tagged as accounts. Therefore, as a dynamic hierarchy, Measures includes only one stored level.

This image shows an outline of the Measures dimension with one stored level (Ratios).

Adding the child member All to Ratios does not change the number of stored levels in the Measures dimension. Saving the outline triggers a light restructure.

This image shows the effect of adding All as a child to Ratios, as described in the text preceding the image.

In ASOsamp.Sample, Income Level is a stored hierarchy dimension.

This image shows an outline of the Income Level stored hierarchy dimension, with two levels.

Adding a child member does not change the number of levels (two) in the hierarchy. Adding a seventh or eighth child member at the end is allowed; however, adding a ninth child member crosses the power of 2 boundary (see Example: Addition of Child Members), requiring a full outline restructure.

This image shows a child member added to Income Level. The effect of adding a child member is described in the text preceding the image.

Example: Change in the Number of Stored Levels in a Hierarchy

In the Product dimension in ASOsamp.Sample, renaming Photo Printers to Printers and adding child members increases the number of levels in the All Merchandise hierarchy from four to five. When the outline is saved, Essbase clears all aggregate views and performs a full outline restructure.

This image shows an outline of the All Merchandise hierarchy. The effect of adding child members, which increases the number of stored levels, is described in the text preceding the image.

Example: Changes in Alternate Hierarchies

If you delete the shared member Orange under Drinks by Category and do not delete its prototype member under Drinks, the alternate hierarchy Drinks by Category is no longer a replica of the Drinks hierarchy. When the outline is saved, Essbase clears all aggregate views and performs a full outline restructure.

If you delete the shared and prototype Orange members, the alternate hierarchy Drinks by Category remains a replica of the Drinks hierarchy. When the outline is saved, Essbase performs a full outline restructure but does not clear aggregate views.

This image shows an outline of the Drinks hierarchy and Drinks by Category alternate hierarchy. The effect of hierarchy changes is described in the text preceding the image.

Example: Addition of Child Members

In ASOsamp.Sample, adding a child member under Systems in the All Merchandise hierarchy increases the number of children under Systems to three, crossing the power of 2 boundary. When the outline is saved, Essbase performs a full outline restructure.

This image shows an outline of the All Merchandise hierarchy. The effect of adding child members is described in the text preceding the image.

However, adding a child member under Computers and Peripherals increases the number of children under Computers and Peripherals from three to four. Adding a fourth child, which must be added after the existing members, does not cross the boundary of 2 or 4. The child must be added after existing members. When the outline is saved, Essbase performs a light restructure.

This image shows an outline of Computers and Peripherals, which is in the All Merchandise hierarchy. The effect of adding child members is described in the text preceding the image.

Example: Addition of Child Branches

In ASOsamp.Sample, adding a child branch under Computers and Peripherals in the All Merchandise hierarchy increases the number of children to four. Adding this child, which must be added after the existing members, does not cross the power of 2 boundary. The new member, called Other Peripherals, has two children. Systems (which is a sibling of Other Peripherals) has two children. Adding the child branch stays within the power of 2 boundary for children of sibling members at the same level. When the outline is saved, Essbase performs a light restructure.

This image shows an outline of Computers and Peripherals, in which a child branch is added. The effect of adding a child branch and members is described in the text preceding the image.

Adding a child branch with three child members crosses the power of 2 boundary and may require that Essbase perform a full outline restructure. However, if Systems already had three members, the power of 2 boundary would be four and, at most, four children can be added to Other Peripherals without triggering a full outline restructure.