Chapter 15: Dimension Hierarchies

Most dimensions have one or more hierarchies. For instance, the date dimension has one hierarchy with four levels: the year level, the quarter level, the month level, and the date level. These levels are represented by columns in the date_dim table. The date dimension has one-path hierarchy as it does not have any other hierarchy than the year-quarter-month-date path. In this chapter you learn about single-path hierarchies. In addition, this chapter talks about grouping and drilling queries on dimensions with hierarchies.

Note 

Multi-path hierarchies are discussed in Chapter 16, “Multi-Path and Ragged Hierarchies.”.

Hierarchies in A Data Warehouse

I’ll start discussing dimension hierarchies by showing you how to identify hierarchies in the dimensions of a data warehouse.

To identify a hierarchy in a dimension, you must first understand the meaning of dimension columns. You can then identify two or more columns that are of the same subject. For example, the date, the month, the quarter, and the year have the same subject because they all have something to do with the calendar. Columns of the same subject form a group. A column in a group must contain at least another member of the group. As an example, in the group mentioned earlier, the month contains the date. The chain of these columns form a hierarchy. For example, the date-month-quarter-year chain is a hierarchy in the date dimension.

In addition to the date dimension, the product and customer dimensions also have hierarchies. Figure 15.1 shows the columns of the hierarchies in bold.

Figure 15.1: Schema Showing Hierarchies

Table 15.1 shows the hierarchies of the three dimensions. Note that the customer dimension has two-path hierarchies.

Table 15.1: Hierarchies in customer_dim, product_dim, and date_dim
Open table as spreadsheet

customer_dim		ptoduct_dim	date_dim
customer_street_address	shipping_address	product_name	date
customer_zip_code	shipping_zip_code	product_category	month_name
customer_city	shipping_city		quarter
customer_state	shipping_state		year