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.
Table 15.1 shows the hierarchies of the three dimensions. Note that the customer dimension has two-path hierarchies.
| customer_dim | product_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 | |