Sign in or 
| Version | User | Scope of changes |
|---|---|---|
| Jan 29 2008, 6:32 AM EST (current) | keith_laker | 498 words added, 27 words deleted, 2 photos added |
| Jan 28 2008, 2:41 PM EST | keith_laker | 633 words added, 5 photos added |
Changes
Key: Additions Deletions
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
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:
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"Level basedBased" 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“Skip Level”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”“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 is designed to support dimensions with multiple hierarchies, as shown here:
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:
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"Level basedBased" 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“Skip Level”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:
Of course you can combine some of these structures to create more complicated relationships such as a “Ragged-Skip”“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 hierarchy not shown, buthierarchywhich whichmost isproducts supportedstruggle Oracleto OLAP,support is “Value”“Value-based" or based"Parent-Child" hierarchies, of which the typical Employee/HR table is the most common example. ThisOracle typeOLAP ofis hierarchyable containsto nosupport levelsthese andtypes isof hierarchies dealtbecause 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.
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.
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.
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.
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 hierarchy not shown, buthierarchywhich whichmost isproducts supportedstruggle Oracleto OLAP,support is “Value”“Value-based" or based"Parent-Child" hierarchies, of which the typical Employee/HR table is the most common example. ThisOracle typeOLAP ofis hierarchyable containsto nosupport levelsthese andtypes isof hierarchies dealtbecause 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.
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.
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.
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:
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.
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.
