About Hierarchies and Levels

A hierarchy shows relationships among groups of columns in a dimension table. For example, quarters contain months and months contain days. Hierarchies enable drilling in reports.

A dimension table can have one or more hierarchies. A hierarchy typically begins with a total level, then has child levels, working down to the lowest detail level.

All hierarchies for a given dimension must have a common lowest level. For example, a time dimension might contain a fiscal hierarchy and a calendar hierarchy, with Day as the common lowest level. Day has two named parent levels called Fiscal Year and Calendar Year, which are both children of the All root level.

All levels, except the total level, must have at least one column specified as the key or display column. However, it’s not necessary to explicitly associate all of the columns from a table with levels. Any column that you don’t associate with a level is automatically associated with the lowest level in the hierarchy that corresponds to that dimension table.

There’s no limit to the number of levels you can have in a hierarchy. The total number of levels isn’t by itself a determining factor in query performance. However, be aware that for extremely complex queries, even a few levels can impact performance.