Dimension Hierarchies in
OBIEE:Level-Based Hierarchy and Parent-Child Hierarchy
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
Parent-Child Hierarchies
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.
