Data Warehousing and Online Analytical Processing - Data Mining: Concepts and Techniques - page 142

time
Dimension table

sales
Fact table

item
Dimension table

shipping
Fact table

shipper
Dimension table

time_key
item_key
branch_key
location_key
dollars_sold
units_sold

time_key
day
day_of_week
month
quarter
year

item_key
item_name
brand
type
supplier_type

item_key
time_key
shipper_key
from_location
to_location
dollars_cost
units_shipped

shipper_key
shipper_name
location_key
shipper_type

location
Dimension table

branch
Dimension table

branch_key
branch_name
branch_type

location_key
street
city
province_or_state
country

Figure 4.8 Fact constellation schema of a sales and shipping data warehouse.

and units shipped . A fact constellation schema allows dimension tables to be shared
between fact tables. For example, the dimensions tables for time, item , and location are
shared between the sales and shipping fact tables.

In data warehousing, there is a distinction between a data warehouse and a data mart.
A data warehouse collects information about subjects that span the entire organization ,
such as customers, items, sales, assets , and personnel , and thus its scope is enterprise-wide .
For data warehouses, the fact constellation schema is commonly used, since it can model
multiple, interrelated subjects. A data mart , on the other hand, is a department subset of
the data warehouse that focuses on selected subjects, and thus its scope is department-
wide . For data marts, the star or snowflake schema is commonly used, since both are
geared toward modeling single subjects, although the star schema is more popular and
efficient.

4.2.3 Dimensions: The Role of Concept Hierarchies
A concept hierarchy defines a sequence of mappings from a set of low-level concepts
to higher-level, more general concepts. Consider a concept hierarchy for the dimension
location . City values for location include Vancouver, Toronto, New York, and Chicago.
Each city, however, can be mapped to the province or state to which it belongs. For
example, Vancouver can be mapped to British Columbia, and Chicago to Illinois.
The provinces and states can in turn be mapped to the country (e.g., Canada or the
United States) to which they belong. These mappings form a concept hierarchy for the