The aim of this page is to provide a glossary of common OLAP and data warehousing terms and expressions. This list is for all terms beginning with h

Hierarchy

Most dimensions will have at least one hierarchy, but Oracle OLAP does also support completely flat dimensions where no hierarchy exists. Although this is rare it does occur in some cases, but it is always wise to have an “All Members” level for these types of dimensions as this will allow business users to pivot these types of dimensions out of their query by selecting that top level. Otherwise their queries will always be pinned to a single dimension member within the page dimension.

A hierarchy defines a set of parentage relationships between all or some of a dimension's members:

• Used for rollups of data.
• Used for end-user navigation; e.g., drill-down.

While multiple hierarchies are supported each member can have only one parent within each hierarchy. Lets look at some basic examples:

In the first image we have a traditional "Level Based" hierarchy where each child has a parent at the next level up in the hierarchy. Although the number of children at each node may, and usually does, differ between nodes. The second image shows another type of level based hierarchy that is some times referred to as a “Skip Level” hierarchy. This is where a leaf node links to a higher-level parent above its next most obvious level. Oracle database can support skip-level relationships within relational hierarchies, however, this is limited to skipping to only one specific level. Oracle OLAP is able to support skip-levels across multiple levels, as seen here:

Oracle OLAP is able to manage these types of relationships quickly and easily because all types of hierarchies are effectively stored as parent-child relationships. A derivation of the skip-level hierarchy is the “Ragged” hierarchy. This is where leaf-nodes are located at different levels within the hierarchy. Obviously this can have an impact on the data loading and aggregation plans, however, Oracle OLAP is more than capable of handling this type of scenario in just the same way as any other level based hierarchy.





Working with Hierarchies - Parents and Children
Since hierarchies can be implemented in different ways and data can be present at different levels users need additional intelligent semantics to allow them to work with these hierarchical structures. An additional structural concept that accrues from multi-dimensional hierarchies relates to parents and children. By constructing hierarchies within a dimension, additional metadata is created that allows users to select values using conditions based on relationships. The following terms can be used to create a dimensional selection:

Selection	Description
Parent	Returns the dimension members from the next level up in the hierarchy. In the previous example if the user asked for the parent of a City the query would return a County
Children	Returns the dimension members from the next level down in the hierarchy. In the previous example if the user asked for the children of a County the query would return Cities
Siblings	Returns all the dimension members from the same level in the hierarchy. In the previous example if the user asked for the siblings of a specific County the query would return all the Counties in that hierarchy
Ancestors	Returns the dimension members from the all levels above the selected member in the hierarchy. In the previous example if the user asked for the ancestors of a specific City the query would return the County and State related to the selected City and also All Regions as that is the top level in the hierarchy.
Descendants	Returns the dimension members from the all levels below the selected member in the hierarchy. In the previous example if the user asked for the descendants of a specific State the query would return all the Counties and Cities.
First Ancestor	Returns the dimension members from the highest level in the hierarchy above the selected member. In the previous example if the user asked for the first ancestor of a specific City the query would return All Regions as that is the top level in the hierarchy.
Last Descendant	Returns the dimension members from the lowest level in the hierarchy below the selected member. In the previous example if the user asked for the last descendant of a specific State the query would return the just the cities within that State, as City is the lowest level in the hierarchy.