Physical Database Design

92 CHAPTER 5 Selecting Materialized Views

SELECT country, st_prov, year, sum(cost), sum(sell),

sum(profit)

FROM Profit_Fact, Customer, City, St_Prov, Country,

Date, Month, Year

WHERE Profit_Fact.customer_id = Customer.customer_id

AND Customer.city_id = City.city_id

AND City.st_prov_id = St_Prov.st_prov_id

AND St_Prov.country_id = Country.country_id

AND Profit_Fact.invoice_date_id = Date.date_id

AND Date.month_id = Month.month_id

AND Month.year_id = Year.year_id

GROUP BY country, st_prov, year

ORDER BY country, st_prov, year;

The query is now much more complex, requiring seven joins. Kimball and Ross

[2002] site the join complexity as one reason to avoid the snowflake schema. The que-

ries are more difficult to write, and the query optimizer may ...