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). See Working with Attributes.
As a hierarchy of shared members. The alternate hierarchy has shared members that refer to nonshared members of previous hierarchies in the outline. The shared members roll up according to a different hierarchy from the nonshared members to which they refer. Shared members on dynamic hierarchies can have formulas. See Understanding Shared Members. Table 198 shows the hierarchies for the ASOsamp.Sample database. The Products dimension is shown in Figure 156, Aggregate Storage Outline Displaying the Alternate Hierarchy High End Merchandise on the Product Dimension.
Table 198. Example Hierarchies and Alternate Hierarchies for the Product Dimension of ASOsamp.Sample
Products, All Merchandise, Personal Electronics, Home Entertainment, Televisions | ||
Products, All Merchandise, Personal Electronics, Home Entertainment, Televisions |
Figure 156. Aggregate Storage Outline Displaying the Alternate Hierarchy High End Merchandise on the Product Dimension
The following restrictions apply when creating alternate hierarchies in aggregate storage outlines:
The nonshared instance of the member must occur in the outline before any shared instances of the member. For example, in Figure 156, Aggregate Storage Outline Displaying the Alternate Hierarchy High End Merchandise on the Product Dimension, 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 156, Aggregate Storage Outline Displaying the Alternate Hierarchy High End Merchandise on the Product Dimension, 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.
Nonshared 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 nonshared 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.
In an aggregate storage database, a shared member automatically shares any attributes that are associated with its nonshared member. This also applies to an implied shared member (for example, a member that has only one child). See Understanding Implied Sharing. You can prevent implied sharing by setting the Never Share property; see Determining How Members Store Data Values. This behavior of shared members and attributes in aggregate storage databases is different from the behavior in block storage databases. |