Databases Reference
In-Depth Information
three queries. Sometimes, less is more. There is a small drawback, the user queries now
take 7 seconds of disk I/O. If this is not acceptable in rare cases, then another possibility
is to create the Profit_Fact table first and then create the other materialized views from
the Profit_Fact table rather than from the base tables directly. Since the Profit_Fact table
is much smaller than the base tables, the creation time for the other materialized views
can be greatly reduced. We leave the I/O time calculations as an exercise.
5.3
Exploiting Grouping and Generalization
The health of the company depends on monitoring both short- and long-term trends.
Daily snapshots are the pulse, but we also need monthly and yearly reports. Likewise,
monitoring profitability by customer is very useful in focusing our efforts, but knowl-
edge at the level of state or province and country is needed to make broader decisions.
We can easily augment the fact table from Figure 5.5 to support these additional que-
ries. Figure 5.6 shows the addition of two tables to facilitate the more general queries.
The Calendar table contains month and year columns that can be used in “group by”
clauses to obtain more general trends from the Profit_Fact table. The state_province and
country columns of the Customer table can be used in “group by” clauses to gain strate-
gic knowledge in geographical terms.
Figure 5.6
Profit fact table with Calendar and Customer dimension tables.
If you are familiar with data warehousing, then you will recognize Figure 5.6 as a
simple star schema with two-dimension tables, namely the Calendar and the Customer
tables. This chapter to this point is a progression leading up to the star schema. Hope-
fully the progression clarifies the reasoning behind star schemas, and why they can be
efficient designs. Chapter 14 covers the star schema and the dimensional design
approach in more depth. If you are unfamiliar with star schemas, it will help to keep in
mind the discussion from this chapter when you reach Chapter 14.
We offer two example queries that illustrate how the dimension tables can be used
to group data into more general results. Then we cover the product graph, which enu-
merates and relates the possible combinations of groupings along each dimension.
