A hierarchy is a
cascaded series of many-to-one relationships and consists of different levels.
Example, a region
hierarchy is defined with the levels Region, State, and City.
In the Business Model
and Mapping layer, a dimension object represents a hierarchical organization of
logical columns (attributes). One or more logical dimension tables can be
associated with at most one dimension object. Common dimensions might be time
periods, products, markets, customers, suppliers, promotion conditions, raw
materials, manufacturing plants, transportation methods, media types, and time
of day. Note that dimensions exist in the Business Model and Mapping (logical)
layer and in the Presentation layer.
There are two types of logical dimensions:
1. Dimensions with level-based
hierarchies (structure hierarchies).
2. Dimensions with
parent-child hierarchies (value hierarchies).
Level-based hierarchies
are those in which members are of several types, and members of the same type
occur only at a single level.
In parent-child
hierarchies, members all have the same type. Oracle Business Intelligence also
supports a special type of level-based dimension, called a time dimension, that
provides special functionality for
modeling time series data.
Level-Based Hierarchies
A
dimension contains two or more logical levels. For example, a Time
hierarchy might have three levels for Year, Quarter, and Month. Level-based
hierarchies can also contain parent-child relationships. The recommended sequence
for creating logical levels is to create a Grand Total level and then create
child levels, working down to the lowest level.
Level-based
Dimension hierarchy levels allow:
- to perform aggregate navigation,
- to configure level-based measure
calculations,
- users from Dashboard and Answers
to drill down from one parent to a child level.
The
following are the parts of a dimension:
- Grand Total level : A special level representing the
grand total for a dimension. Each dimension can have just one Grand Total
level. A Grand Total level does not contain dimensional attributes and
does not have a level key. However, you can associate measures with a
Grand Total level. The aggregation level for those measures will always be
the grand total for the dimension.
- Level : All levels, except the Grand Total
level, need to have at least one column. However, it is not necessary to
explicitly associate all of the columns from a table with logical levels.
Any column that you do not associate with a logical level is automatically
associated with the lowest level in the dimension that corresponds to that
dimension table. All logical columns in the same dimension table have to be
associated with the same dimension.
- Hierarchy : Each dimension contains one or more
hierarchies. All hierarchies must have a common leaf level and a common
root (all) level.
- Level keys : Each logical level (except the
topmost level defined as a Grand Total level) must have one or more attributes
that compose a level key. The level key defines the unique elements in
each logical level. The dimension table logical key has to be associated
with the lowest level of a dimension and has to be the level key for that
level.
- Time dimensions and chronological keys : You can identify a dimension as a
time dimension. At least one level of a time dimension must have a
chronological key.
The following is a list
of some guidelines you should use when setting up and using time dimensions:
- Unbalanced (or ragged)
hierarchy : A
hierarchy in which all the lowest-level members do not have the same depth For example, a site can choose to
have data for the current month at the day level, previous months data at
the month level, and the previous 5 years data at the quarter level.
- For example, a Time hierarchy
might have data for the current month at the day level, the previous
month's data at the month level, and the previous 5 years' data at the
quarter level. This type of hierarchy is also known as an unbalanced
hierarchy.
Note that unbalanced
hierarchies are not necessarily the same as parent-child hierarchies.
Parent-child hierarchies are unbalanced by nature, but level-based hierarchies
can be unbalanced also.
- Skip-level hierarchy : A skip-level hierarchy is a
hierarchy where there are members that do not have a value for certain higher levels. . For example,
in the United States, the city of Washington in the District of Columbia
does not belong to a state. The expectation is that users can still
navigate from the country level (United States) to Washington and below
without the need for a state.
Hierarchy with
Unbalanced and Skip-Level Characteristics
A
parent-child hierarchy is a hierarchy of members that all have the same type.It
is a value-based hierarchy — Consists of values that define the hierarchy in a
parent-child relationship and does not contain named levels. This contrasts
with level-based hierarchies, where members of the same type occur only at a
single level of the hierarchy.
For
example, an Employee hierarchy might have no levels, but instead have names of
employees who are managed by other employees. Employees can have titles, such
as Vice President. Vice Presidents might report to other Vice Presidents and
different Vice Presidents can be at different depths in the hierarchy.
The most common
real-life occurrence of a parent-child hierarchy is an organizational reporting
hierarchy chart, where the following all apply:
- Each individual in the organization
is an employee.
- Each employee, apart from the
top-level managers, reports to a single manager.
- The reporting hierarchy has many
levels.
These conditions
illustrate the basic features that define a parent-child hierarchy, namely:
- A parent-child hierarchy is based on
a single logical table (for eg, the "Employees" table)
- Each row in the table contains two
identifying keys, one to identify the member itself, the other to identify
the "parent" of the member (for example, Emp_ID and Mgr_ID)
Multi-Level
Parent-Child Hierarchy
In level-based
hierarchies, each level is named, and occupies a position in the hierarchy that
corresponds to a real-word attribute or category that is deemed useful for
analysis. Unlike level-based hierarchies, where the number of levels is fixed
at design time, there is no limit to the depth of a parent-child hierarchy, and
the depth can change at run time due to new data.
