Oracle Olap Terminology-H

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:
Oracle Olap Terminology-H - Oracle Wiki

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 Terminology-H - Oracle Wiki
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.

Oracle Olap Terminology-H - Oracle Wiki
Of course you can combine some of these structures to create more complicated relationships such as a “Ragged-Skip” level hierarchy. These more complex structures are also supported.

The last type of hierarchy shown above is a simple flat hierarchy, which as explained earlier may or may not be an ideal type of dimension to model depending on how your business users plan to build queries. In all these cases, the hierarchy is defined based on levels.

One type of hierarchywhich most products struggle to support is “Value-based" or "Parent-Child" hierarchies, of which the typical Employee/HR table is the most common example. Oracle OLAP is able to support these types of hierarchies because it able todeal with this type of structure as a pure parent-child relationship. In this case the level names are converted into attributes to help business users define the queries.

Oracle Olap Terminology-H - Oracle Wiki

Across all these types of hierarchies there are some simple rules that need to be followed. It is recommended you create at least one top level on each of your hierarchies. Although some types of dimensions, such as time, will require multiple top levels such as Years.
Oracle Olap Terminology-H - Oracle Wiki

What you cannot do is have a child owned by multiple parents within the same hierarchy as shown below. In this case, you would need to create two separate hierarchies to manage the relationships separately.

Oracle Olap Terminology-H - Oracle Wiki


The interesting part here, is the basic design of the dimension and its related levels, hierarchies and attributes is largely consistent across all these different types of structures. The only real different is between level and value-based relationships where value-based dimensions do not contain levels. Fortunately, the dimension loading routines within Analytic Workspace Manager manage these types of dimension structures transparently.

Oracle OLAP is designed to support dimensions with multiple hierarchies, as shown here:

Oracle Olap Terminology-H - Oracle Wiki




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:


SelectionDescription
ParentReturns 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
ChildrenReturns 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
SiblingsReturns 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
AncestorsReturns 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.
DescendantsReturns 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 AncestorReturns 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 DescendantReturns 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.

These new query methods provide significant power to end user allowing them to create dynamic, self-maintaining queries that are driven directly from the structure of the data warehouse. This query methodology also reflects the way users expect their data to be structured and the way in which they expect to navigate the corresponding structures.


keith_laker
keith_laker
Latest page update: made by keith_laker , Jan 29 2008, 6:32 AM EST (about this update About This Update keith_laker Updated entry for hierarchy - keith_laker

498 words added
27 words deleted
2 images added

view changes

- complete history)
Keyword tags: OLAP terminology
More Info: links to this page

Anonymous  (Get credit for your thread)


Started By Thread Subject Replies Last Post
keith_laker OLAP Phrases and Terminology 0 Jan 28 2008, 2:41 PM EST by keith_laker
keith_laker
Thread started: Jan 28 2008, 2:41 PM EST  Watch
If you can think of any additional phrases or terminology you would like to see added to this page, feel free to make updates or make a posting and I will research the subject for and create an entry for you.

Regards

Keith Laker
Oracle EMEA Consulting

OLAP Blog: http://oracleOLAP.blogspot.com/
OLAP Wiki: http://wiki.oracle.com/page/Oracle+OLAP+Option
DM Blog: http://oracledmt.blogspot.com/
OWB Blog : http://blogs.oracle.com/warehousebuilder/
OWB Wiki : http://wiki.oracle.com/page/Oracle+Warehouse+Builder
DW on OTN : http://www.oracle.com/technology/products/bi/db/11g/index.html
1  out of 3 found this valuable. Do you?    
Keyword tags: OLAP terminology
keith_laker OLAP Phrases and Terminology 0 Jan 28 2008, 2:25 PM EST by keith_laker
keith_laker
Thread started: Jan 28 2008, 2:25 PM EST  Watch
If you can think of any additional phrases or terminology you would like to see added to this page, feel free to make updates or make a posting and I will research the subject for and create an entry for you.

Regards

Keith Laker
Oracle EMEA Consulting

OLAP Blog: http://oracleOLAP.blogspot.com/
OLAP Wiki: http://wiki.oracle.com/page/Oracle+OLAP+Option
DM Blog: http://oracledmt.blogspot.com/
OWB Blog : http://blogs.oracle.com/warehousebuilder/
OWB Wiki : http://wiki.oracle.com/page/Oracle+Warehouse+Builder
DW on OTN : http://www.oracle.com/technology/products/bi/db/11g/index.html
2  out of 4 found this valuable. Do you?    
Keyword tags: OLAP terminology

Related Content

  (what's this?Related ContentThanks to keyword tags, links to related pages and threads are added to the bottom of your pages. Up to 15 links are shown, determined by matching tags and by how recently the content was updated; keeping the most current at the top. Share your feedback on Wetpaint Central.)
Site pages
Top Contributors