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.

image from book
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



Dimensional Data Warehousing with MySQL. A Tutorial
Dimensional Data Warehousing with MySQL: A Tutorial
ISBN: 0975212826
EAN: 2147483647
Year: 2004
Pages: 149

Similar book on Amazon
The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling (Second Edition)
The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling (Second Edition)
The Data Warehouse ETL Toolkit: Practical Techniques for Extracting, Cleanin
The Data Warehouse ETL Toolkit: Practical Techniques for Extracting, Cleanin
Successful Business Intelligence: Secrets to Making BI a Killer App
Successful Business Intelligence: Secrets to Making BI a Killer App

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net