Alternate hierarchies in dimensions and cubes

Elements in alternate hierarchies

All base elements are available to every hierarchy. Each base element must be a part of at least one hierarchy. Base elements with the same name but in different hierarchies are the same element and data is shared. This includes cell value, cell note, and attributes.

The union of all base elements of all hierarchies form the base elements of the dimension.

Unlike base elements, aggregated elements only exist in the hierarchy they are defined in. Separate hierarchies can contain aggregated elements with the same name. These elements do not share attribute values or point to the same cube data.

Aggregated elements with the same name that appear in different hierarchies are treated as different elements by the database kernel. As a result, the values calculated for such elements can vary between hierarchies.

As described above, it is possible to create aggregated elements with the same name in multiple hierarchies. But the same name cannot be used for a base element and an aggregated element. This is not allowed even if the base element and the aggregated element are in different hierarchies.

Each hierarchy has a default element. It can be set explicitly or else it is chosen automatically.

Default and implicit hierarchies

Each dimension has at least one hierarchy. There is a default hierarchy on each dimension. If a dimension has multiple hierarchies and none of them was defined as the default hierarchy, a random one is automatically selected.

If no hierarchy has been explicitly defined at dimension creation time, an implicit hierarchy is automatically added. The name of this hierarchy is the same as the name of the dimension.

A hierarchy with the same name as the name of the dimension is always the implicit hierarchy, regardless if there are multiple hierarchies and which one is the default one. This is important for MDX queries and schema requests.

Hierarchy kinds and subsets

There are two kinds of hierarchies: parent-child hierarchies and attribute-driven hierarchies. Internally, these two types of hierarchies are identical. The only difference between them is the method used to define them. Parent-child hierarchies are defined by explicitly specifying each parent-child relation in the hierarchy. Attribute-driven hierarchies are defined by treating one or more attributes of the base elements of the hierarchy as an aggregated level. Parent-child relations are automatically generated for the user.

Attribute-driven hierarchies are not updated immediately after an attribute has been changed or an element has been added to the source hierarchy. This only happens when the Dimension UpdateHierarchies function is called or when the hierarchy definition changes.

Subsets are restricted to elements from a single hierarchy only.