Expert SQL Server™ 2005 Integration Services

4.2. Dimensions: The Basics

This chapter focuses on dimension ETL, but in order to understand the full picture of dimension ETL, some dimension theory is required. The best resource is to read The Complete Guide to Dimension Modeling, Second Edition, by Ralph Kimball and Margy Ross (Wiley Publications, 2002), for a complete picture of a dimension. For the purposes of this discussion, here's a high-level summary.

The dimension itself is an organized grouping of categories and properties about a particular entity. These categories and properties are called attributes, and they form hierarchies with levels and members used to slice and query a cube or fact table.

For example, the following table shows data focusing on geography. The attributes are the Country, State/Province and City, which combine together because of their relationship to one another.

Country	State/Province	City
Germany	Bavaria	Augsburg
Germany	Bavaria	Munich
Germany	Niedersachsen	Hannover
United States	California	Palo Alto
United States	California	Woodland Hills
United States	New York	Cheektowaga
United States	New York	Lake George

Naturally, this data can be used together to create a hierarchy, which provides drill paths to data and the ability to slice and dice data based on certain records. Figure 4-1 shows one of the hierarchies within this geography dimension. The members are the names of the countries, state provinces, and cities. The hierarchy is made up of levels that usually correspond to the column names used in the hierarchy. ...