After you complete the fact table, you can decide the
dimension attributes for each of the dimension tables. To illustrate
how to choose the attributes, consider the time dimension.
The data model for the sales business process defines a granularity
of day that corresponds to the time dimension, so that each record
in the time dimension table represents a day. Keep in mind
that each field of the table is defined by the particular day the
record represents.
The analysis of the sales business process
also indicates that the marketing department needs monthly, quarterly,
and annual reports, so the time dimension includes the elements: day,
month, quarter, and year. Each element is assigned an attribute that
describes the element and a code attribute, to avoid column values
that contain long character strings. The following table shows the
attributes for the time dimension table and sample values for
each field of the table.
Table 1. Attributes
for the time dimension
time code
order date
month code
month
quarter code
quarter
year
35276
07/31/2010
7
july
3
third q
2010
35277
08/01/2010
8
aug
3
third q
2010
35278
08/02/2010
8
aug
3
third q
2010
The previous table shows that the attribute names you
assign should be familiar business terms that make it easy for end
users to form queries on the database.
The following figure
shows the completed data model for the sales business process with
all the attributes defined for each dimension table. The elements
of the Sales fact table are: product code, time code, district code,
customer code, revenue, cost, units sold, and net profit. Some of
these elements join the Sales fact table to the dimension tables.
Additional elements for each dimension table have been identified.Figure 1: The completed dimensional data model for the
sales business process
Product dimension table
The product code element joins the Sales fact table to the Product
dimension table. The additional elements in the Product dimension
table are: product name, vendor, vendor name, product line, and product
line name.
Time dimension table
The time code element joins the Sales fact table to the Time dimension
table. The additional elements in the Time dimension table are: order
date, month, quarter, and year.
Geography dimension table
The district code element joins the Sales fact table to the Geography
dimension table. The additional elements in the Geography dimension
table are: district, state, state name, and region.
Customer dimension table
The customer code element joins Sales fact table to Customer dimension
table. The additional elements in the Customer dimension table are:
customer name and company.
