What are dimension hierarchies?

A dimension hierarchy describes a hierarchical relationship among two or more dimension members.

Individual dimension members might be related to each other in a hierarchical way. For example, a specific day belongs to a particular month, which in turn is within a particular year. To reflect such relationships, dimension members are organized into dimension hierarchies. Hierarchies allow you to drill deeper into the data, to view more detailed information.

A dimension hierarchy can use ordered levels to organize and aggregate data. For example, the Time dimension might have a hierarchy to aggregate data from the Month level to the Quarter level to the Year level. Figure 3-6 presents an example of a dimension hierarchy for the Time dimension, which shows how data is ordered by Month, Quarter, and Year. The cell that is highlighted in the cube represents sales of Product D in Cincinnati in 2003. Along the Time dimension, that cell can be broken out into quarters, and each quarter can be broken out further into months.

Figure 3-6 Sample dimension hierarchy

Description of the illustration hierarchy1.gif

Figure 3-7 presents another view of the hierarchy for the Time dimension. Data for the year 2003 can be broken into four quarters, and each quarter can be broken into three months.

Figure 3-7 Alternative view of a time hierarchy

Description of the illustration hierarchy2.gif

A dimension can have more than one hierarchy. For example, a Time dimension might have different hierarchies if a company has a Fiscal year that does not correspond to a Calendar year. One hierarchy might be Calendar Year->Calendar Quarter->Month while another is Fiscal Year->Fiscal Quarter->Month. Where several dimension hierarchies exist for the same dimension, one dimension hierarchy must be specified as the default hierarchy.