SQL
In his excellent book, Star Schema, Christopher Adamson begins Chapter 7, “Hierarchies and Snowflakes,” with the following language:
It is possible to describe a dimension table as a series of parent-child relationships among groups of attributes. Days make up months, months fall into quarters, and quarters fall into years, for example. This chapter explores these attribute hierarchies and their implications for the design and use of dimensional databases.
Page 147. This understanding appears fully consistent with the definition of found in MSDN:
Attribute hierarchy
An attribute hierarchy is a hierarchy of attribute members that contains the following levels:
- A leaf level that contains each distinct attribute member, with each member of the leaf level also known as a leaf member.
- Intermediate levels if the attribute hierarchy is a parent-child hierarchy.
- An optional (All) level (IsAggregatable=True) containing the aggregated value of the attribute hierarchy’s leaf members, with the member of the (All) level also known as the (All) member.
http://msdn.microsoft.com/en-us/library/ms144884.aspx
It may seem intuitively obvious what the phrase “Attribute Hierarchy” means, and the descriptions given by Mr. Adamson and the definition given in MSDN probably fit neatly with your intuition. If you follow your intuition, however, other documentation for Microsoft SQL Server Analysis Services (SSAS) becomes confusing, and you are unlikely to leverage features of the product that will make it more useful and responsive.
Microsoft Documentation
To cut to the chase, what you need to know is this: In most of Microsoft’s documentation (other than the definition shows above) and within the user interface of Business Intelligence Development Studio (“BIDS”), “attribute hierarchy” means a hierarchy with exactly one attribute. Intuitively, a hierarchy with only one attribute may not seem like much of a hierarchy at all to most people. By default, however, it does contain two levels: The All level and the level under the All level with (by default) the same name as the attribute.
MSDN defines an additional type of hierarchy, the “user-defined hierarchy.” It is defined in the following way (Attribute Hierarchies, from MSDN attributes and attribute hierarchies) :
User-defined hierarchy
A user-defined hierarchy is a balanced hierarchy of attribute hierarchies that is used to facilitate browsing of cube data by users. User-defined hierarchies do not add to cube space. Levels in a user-defined hierarchy can be hidden under certain circumstances and appear unbalanced.
Clearly, this definition contemplates the possibility of more than two levels created from more than one attribute, and this definition of “user-defined hierarchy” seems to be the same as our intuitive understanding of “attribute hierarchy.” But they are in fact different within the Microsoft world.
So… Why should we care?
Why do we care? Because in SSAS, individual attributes contained in dimensions have properties that only make sense if a stricter definition of attribute hierarchy is used–a definition that refers to hierarchy with exactly one attribute level and (usually) an All level. Look at the properties window for a single attribute contained in a dimension:
Notice that there are several properties of the attribute that have “Attribute Hierarchy” contained within them and that these properties are not properties of a user-defined hierarchy. The description contained in gray-highlighted box at the bottom of the properties window only makes sense if “attribute hierarchy” and “user-defined hierarchy” mean different things.
Developers define user-defined hierarchies in the “Hierarchies” panel of the Dimension Structure tab.
In conclusion, be aware that when you are reading Microsoft documentation and using the SSAS interface, “attribute hierarchy” and “user-defined hierarchy” are two very distinct things, and failing to understand this will result in confusing properties that only apply to attribute hierarchies to user-defined hierarchies.