Reviewing Dimension Hierarchy Types
Each ASO dimension is assigned a hierarchy type: Stored, Dynamic, or Multiple. Elements of dimensions with Stored hierarchy type are stored and aggregations are materialized in aggregate views.
Elements of Dynamic dimensions cannot be aggregated into views. In Multiple type dimensions, generation 2 (children of the top node) are set to Stored or Dynamic. For Multiple hierarchy type, you choose the hierarchy type as either Stored or Dynamic at generation 2. The first generation 2 child (usually the main hierarchy) must be of type Stored. For such hierarchies, you should edit all generation 2 members and select either Stored (preferred) or Dynamic as hierarchy type.
Stored dimensions are preferred to ensure optimal performance. Because the hierarchy types you choose also impact the number of aggregate views that can be created, Oracle recommends the following:
- Use only Stored type dimensions, if possible.
- Use Multiple, where possible, if Stored type dimension cannot be used.
- For Multiple hierarchy dimensions, set Generation 2 to Stored rather than Dynamic, if possible.
- Use Dynamic dimensions only if Multiple hierarchy type cannot be used.
- Account dimension must always be Dynamic because it is the ASO compression dimension. This cannot be changed.
Impact of Hierarchy Types in Dimension Conversion
When converting a dimension, for example, from Dynamic to Multiple, the node member (Gen1) becomes Label Only. The conversion will work if the dynamic dimension only has one child (+) at generation 2. Dimension conversion from Dynamic to Multiple may not be possible if you require a total at Gen1 (node) level.Setting Hierarchy Types
Hierarchy type (Stored, Dynamic or Multiple) is set against the top node member of each dimension. In all cases, the descendants inherit the parent's hierarchy type. Thus, Stored and Dynamic hierarchy types are set for the entire dimension. For dimensions given Multiple hierarchy type, each generation 2 member (the children of the node member) is set to either Dynamic or Stored.
Considerations for Stored hierarchy type include the following:
- Stored hierarchy type can contain consolidation operators + (addition) and ~ (ignore).
- The no-consolidation or ignore operator can be used in a Stored hierarchy type only if the member's parent is set to LABEL_ONLY.
Considerations for Dynamic hierarchy type include the following:
- Members with Dynamic hierarchy type can use any consolidation operator, including addition and ignore.
- It is possible to use formulas within Dynamic hierarchy type.
- Shared members may exist within a Stored hierarchy type in some circumstances, but may need to be in a Dynamic hierarchy (see Shared Members and Hierarchy Types).
- Avoid making large dimensions Dynamic.
Use of Formula and Hierarchy Types
While it is possible to use formulas within Dynamic hierarchy type, it is important to note that the entire dimension need not be Dynamic; only the hierarchy that the formula exists in need to be Dynamic.
Where possible, when a dimension contains formula, use Multiple as the hierarchy type. At generation 2, make only the hierarchies within the dimension that contain formula Dynamic; make the rest Stored.
The Scenario dimension, which may have Actual, Budget, Forecast, and some formula, is a good example. Often, these are generation 2 members. In this case, make Scenario hierarchy type Multiple, make generation 2 members with formula Dynamic, and all other members Stored.
Shared Members and Hierarchy Types
Shared members can affect decisions about hierarchy type within ASO databases. Whereas Stored dimensions cannot have shared members Stored hierarchies within a Multiple dimension type can have shared members.
When setting hierarchy types in Multiple dimensions, set as many hierarchies as possible as Stored type (setting at generation 2).
Shared members can exist in Stored hierarchies within Multiple dimensions if the shared member is only shared once and is shared with a level 0 member. There may, however, be the following restrictions on shared members within a Stored hierarchy requiring you to set a generation 2 hierarchy within the Multiple dimension as Dynamic:
- To ensure that values are not double-counted, a stored hierarchy cannot contain multiple copies of the same shared member.
- A Stored hierarchy cannot contain both stored and shared versions 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. In other words, a Stored hierarchy cannot contain a shared member of an upper level member or a member with a formula.
-
Shared members can exist within a Multiple dimension if each shared member is used only once and is a shared instance of a level0 member.