what is hierarchy and types of hierarchy ?

      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.