About Dimension Hierarchies and Levels

A dimension hierarchy organizes dimension attributes into categories or levels, where each level is a sub-division of the level above. Every logical dimension you create has at least one hierarchy with at least one level.

A hierarchy is a way of organizing dimension attributes. Hierarchies allow data to be accessed at different levels of granularity. This allows for interactive drill-down and drill-up data exploration in the BI applications.

Each level of a hierarchy must evenly divide into (or roll up to) the level above it. For example, a Date dimension might have a hierarchy that starts with a top-level attribute of Year, sub-divided by Quarter, then Month, then Day. When you are viewing a hierarchy in the model, you'll see that the most granular level will always be on the bottom and the most general level on the top.

Figure 1. The Date dimension and its two hierarchies in the sample model Internet Sales

A dimension, by default, always has at least one hierarchy - a single-level hierarchy containing the dimension's key level. Even dimensions whose data is not hierarchical in nature still require hierarchies and levels.