After you determine the granularity of the fact table,
it is easy to identify the primary dimensions for the data model because
each component that defines the granularity corresponds to a dimension.
The following figure shows the relationship between the
granularity of the fact table and the dimensions of the data model. Figure 1: The granularity of the fact table corresponds
to the dimensions of the data model
With the dimensions (customer, product, geography, time)
for the data model in place, the schema diagram begins to take shape.
Tip: At this point, you can add additional dimensions to the
primary granularity of the fact table, where the new dimensions take
on only a single value under each combination of the primary dimensions.
If you see that an additional dimension violates the granularity because
it causes additional records to be generated, then you must revise
the granularity of the fact table to accommodate the additional dimension.
For this data model, no additional dimensions need to be added.
You
can now map out dimension elements and hierarchies for each dimension.
The following figure shows the relationships among dimensions, dimension
elements, and the inherent hierarchies. Figure 2: The
relationships between dimensions, dimension elements, and the inherent
hierarchies
In most cases, the dimension elements need to express
the lowest possible granularity for each dimension, not because queries
need to access individual low-level records, but because queries need
to cut through the database in precise ways. In other words, even
though the questions that a data warehousing environment poses are
usually broad, these questions still depend on the lowest level of
product detail.
Product dimension
The dimension elements for
the product dimension are product, product line, and vendor:
Product has a roll-up hierarchical relationship with product line
and with vendor. Product has an attribute of product name.
Product line has an attribute of product line name.
Vendor has an attribute of vendor.
Customer dimension
The dimension element for
the customer dimension is customer, which has attributes of customer,
name, and company.
Geography dimension
The dimension elements
for the geography dimension are district, state, and region:
District has a roll-up hierarchical relationship with state, which
has a roll-up hierarchical relationship with region.
District has an attribute of district name.
State has an attribute of state name.
Time dimension
The dimensional elements
for the time dimension are day, month, quarter, and year.
Day has a roll-up hierarchical relationship with month, which
has a roll-up hierarchical relationship with quarter, which has a
roll-up hierarchical relationship with year.
