A
hierarchy is a very important part in OLAP Cube. Hierarchy is an Arrangement of
Attributes in a dimension to improve the performance of Analysis and
allows users to roll up attributes from lower level to upper
level or in another word to drill down attributes from summary levels down
to detail levels.
Natural Hierarchy – When we create a user-defined
hierarchy, we define relationship between hierarchy levels. It helps the
Analysis Services to create useful aggregations which in turn increases query
performance. When levels in a hierarchy are linked in a natural relationship
like one-to-one or many-to-one, such hierarchies are known as Natural
Hierarchies. Like in a Calendar hierarchy, Day level related to the month
level, month level to the Quarter and so on.
Non-Natural Hierarchy - In some type of hierarchies the natural
relationship is lacking between the hierarchy levels and branches of the
hierarchy descend to different levels. Such hierarchies are known as
Non-Natural or Unbalanced hierarchies. For example, the Marital Status à Gender
hierarchy is a non-natural hierarchy as Marital Status and gender attributes do
not have a relationship to each other.Ragged Hierarchies - Are the type of unbalanced hierarchies wherein, the logical parent member of at least one member is not in the level immediately above the member. In this case the hierarchy descends to different levels for different drilldown paths. Take an example of geographic hierarchy. The levels available are Country à State / Province à City. But some countries like Greece do not have provinces thus creating a ragged hierarchy.
In dimension table supporting a ragged hierarchy, the
logically missing members can be represented in using nulls or empty strings,
or they can contain the same value as their parent to serve as a placeholder.
Parent-Child Hierarchies – When an attribute in a
dimension table has the parent attribute which is related using a
self-referencing relationship, it is known as Parent-Child Hierarchy. Such
hierarchies are constructed from a single parent attribute. Please refer to the
DimEmployee dimension schema in the AdventureWorks2008 database. The
ParentEmployeeKey column in the table is related with EmployeeKey primary key
column with foreign key relationship. This means each record in a table is
related with another record in the same table through a parent-child
relationship. In this kind of structure the data can be derived using self-join.
