Natural and Un-natural Hierarchy in SSAS

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.

A example of hierarchy is like employees and Departments in an organization. The hierarchy start with Department and then goes one level down up to employee names. So every employee falls into anyone department.
Creating a Hierarchy is import to improve the performance of analysing a cube, but Creating a Natural Hierarchy is more importent then creating just a hierarchy in any manner called as unnatural hierarchy. Natural Hierarchy is simply a Attribute relationship in such a manner where relation between Attributes are natural. they create levels between attributes like Country -> State -> City. you are not forcing this relation, natural hierarchy support one to many relationship. In natural hierarchy, it defines parent child relationship by the relationships between members.

As per MSDN :
With a natural hierarchy, if you define attribute relationships between the attributes that make up the levels, Analysis Services can use an aggregation from one attribute to obtain the results from a related attribute. If there are no defined relationships between attributes, Analysis Services will aggregate all non-key attributes from the key attribute.

Un natural Hierarchy is user defined relationship between Attributes where there is no direct relation between Attributes but you are forcing to group them. for example Employee --> Gender. here in this can address the reporting drill down or drill through needs.

in Un natural hierarchy, it defines parent Child relationship by relationship of member to the key Attribute. Un natural Hierarchy support many to many relationship

No comments:

Post a Comment