What is a Hierarchy in SQL Server Analysis Services? What are the different types of Hierarchies? Explain each one of them.

A Hierarchy is a collection of one or more related Attributes which are organized in a Parent-Child fashion. An Attribute at a higher level is a parent of an Attribute at the next level and so on. They provide a very convenient way to perform roll up/drill down analysis and helps in rolling up and drilling down the numeric data in a very effective manner.

In analogy, it is like your Bing maps. If you want to locate a particular place and if you want to do it manually (without doing a text search) then you usually first locate the Country, then locate the State within the Country, after that locate the City within the State, and finally locate the place you are looking for within the City.

Example:-

A Calendar Hierarchy might contain Attributes like Year, Quarter, Month, and Day which are organized as a Hierarchy with Year as a parent of Quarter, Quarter as a parent of Month, and Month as a parent of Day.

Here are the common types of Hierarchies:

Natural Hierarchy:-

A Natural Hierarchy is a Hierarchy in which Every Child has a Single Parent. Example: A Calendar Hierarchy.

Balanced Hierarchy:- 

A Balanced Hierarchy is a Hierarchy in which no matter through what path we traverse the Hierarchy, there is a Member at every level and every path has the same number of Levels. Example: A Calendar Hierarchy.

Unbalanced Hierarchy:-

An Unbalanced Hierarchy is a Hierarchy in which number of members and number of Levels varies across different branches of the Hierarchy. In this type of a Hierarchy, Leaf Level Members might belong to different Levels. Example: An Employee/Organization Hierarchy where in say 1 manager (Manager 1) has 2 or more people reporting to him and who belong to different Levels say L1 & L2.  On the other hand say there is another manager (at the same level as Manager 1) who has 2 or more people reporting to him and all of them belonging to the same Level say L1.

Ragged Hierarchy:- 

A Ragged Hierarchy is a Hierarchy in which irrespective of the path you use to traverse, every path has the same number of Levels but not every level is guaranteed to have members except for the Top most Level and Bottom most Level (Leaf Level). Example: A Geography Hierarchy.