Home > About Dimensional Database ... > About Hierarchies > What Are Level-Based Hierar...
![]() Previous |
![]() Next |
Each level represents a position in the hierarchy. Each level above the detail level contains aggregate values for the levels below it. The members at different levels have a one-to-many parent-child relation. For example, Q1-11
and Q2-11
are the children of 2011
, thus 2011
is the parent of Q1-11
and Q2-11
.
Suppose a data warehouse contains snapshots of data taken three times a day, that is, every 8 hours. Analysts might normally prefer to view the data that has been aggregated into days, weeks, quarters, or years. Thus, the Time dimension needs a hierarchy with at least five levels.
Similarly, a sales manager with a particular target for the upcoming year might want to allocate that target amount among the sales representatives in his territory; the allocation requires a dimension hierarchy in which individual sales representatives are the child values of a particular territory.
Hierarchies and levels have a many-to-many relationship. A hierarchy typically contains several levels, and a single level can be included in multiple hierarchies.
Oracle OLAP supports these common types of level-based hierarchies:
Normal hierarchies consist of one or more levels of aggregation. Members roll up into the next higher level in a many-to-one relationship, and these members roll up into the next higher level, and so forth to the top level.
Ragged hierarchies contain at least one member with a different base, creating a "ragged" base level for the hierarchy.
Skip-level hierarchies contain at least one member whose parents are multiple levels above it, creating a hole in the hierarchy. An example of a skip-level hierarchy is City-State-Country, where at least one city has a country as its parent (for example, Washington D.C. in the United States). In relational source tables, a skip-level hierarchy may contain nulls in the level columns.
Multiple hierarchies for a dimension typically share the base-level dimension members and then branch into separate hierarchies. They can share the top level if they use all the same base members and use the same aggregation operators. Otherwise, they need different top levels to store different aggregate values. For example, a Customer dimension may have multiple hierarchies that include all base-level customers and are summed to a shared top level. However, a Time dimension with calendar and fiscal hierarchies must aggregate to separate Calendar Year (January to December) and Fiscal Year (July to June) levels, because they use different selections of base-level members.