Anybody who can tell what exactly the SSAS Hierarchies are ?

today when discussed ssas hierarchy performance friend , checked hierarchy types internet, finally i found there're lots of different explanation, thought have known them before now i'm bit confused explanation of different ssas hierarchy types -

• natural hierarchy
• unnatural hierarchy
• balanced hierarchy
• unbalanced hierarchy
• ragged hierarchy

i found different people have own understanding these hierarchy types

the first blog -

what natural hierarchies , why thing

in blog, author think parent-child hierarchy natural hierarchy

"variable number of levels parent attribute hierarchy in parent-child. unusual case, when single attribute unfolds multiple levels, way parent-child hierarchy build, respecting relationships between members, therefore hierarchy natural well"

the second blog -

hierarchy types in ssas and book "ssas step step " both think parent-child hierarchy unbalanced hierarchy.

the third blog -

sql server: ssas - different types of hierarchies

the author think the natural hierarchies known balanced hierarchy.  , non-natural unbalanced hierarchy.

---

so first question is - parent-child hierarchy natural hierarchy or unnatural hierarchy?

the second question - is natural hierarchy balanced hierarchy , non-natural unbalanced hierarchy?

the third questions - what ssas when defines these hierarchy dimension attributes, know when process dimension, can see select statement each attribute in process pane. want know such detailed information, because want understand why these hierarchies impact performance of processing or querying.

thanks,

simon

---

please vote if it's helpful , mark answer!

it's time close thread because there's no response these days , think got points.

the first blog more preciser, , find description natural hierarchy , unnatural hierarchy msdn.

in a natural hierarchy, attributes participating levels in hierarchy have direct or indirect attribute relationships bottom of hierarchy top of hierarchy.

in an unnatural hierarchy, the hierarchy consists of @ least 2 consecutive levels have no attribute relationships. typically these hierarchies used create drill-down paths of commonly viewed attributes not follow natural hierarchy. example, users may want view hierarchy of gender , education.

we don't need care about other hierarchy types balanced hierarchy , unbalanced hierarchy, ragged hierarchy, because natural , unnatural definitions ,and need confirm 1 natural or unnatural, have different performance story -

from performance perspective, natural hierarchies behave differently unnatural hierarchies. in natural hierarchies, hierarchy tree materialized on disk in hierarchy stores. in addition, attributes participating in natural hierarchies automatically considered aggregation candidates.

unnatural hierarchies not materialized on disk, , attributes participating in unnatural hierarchies not automatically considered aggregation candidates. rather, provide users easy-to-use drill-down paths commonly viewed attributes not have natural relationships. assembling these attributes hierarchies, can use variety of mdx navigation functions perform calculations percent of parent.

---

please vote if it's helpful , mark answer!

SQL Server  >  SQL Server Analysis Services