Databases Reference
In-Depth Information
Translating Fact Groups
A fact group often translates to a fact table. However, from an implementation
perspective, you may decide to split a fact group into more than one physical
fact table. This is typically done if the facts come from different sources or the
timing of the data's availability differs. In these cases, splitting the fact group
may offer efficiencies during the staging process. Regardless of how the facts
are physically stored, the first thing to understand is the dimensionality and
grain.
Each fact group will translate to a fact table. The primary key from each
dimension that applies to the fact group will be stored on the fact table. After
the keys, each fact becomes a column on the fact table. Figure 7-27 shows the
Call Center Calls fact group translated into the logical table.
DATE KEY
TIME KEY
CUSTOMER KEY
EMPLOYEE KEY
CALL TRANSACTION KEY
CALL OUTCOME KEY
Call Minutes
Wait Minutes
Number of Call Transactions
Number of Calls
Figure 7-27
Call Center Calls Fact Table
NOTE
A primary key is a unique identifier of a row in a relational table. When
the primary key is used as a link to another table, it is called a
foreign key
in the
second table.
The logical key of the fact table is comprised of all the foreign keys from
the dimension tables included in that fact group. For the Call Center fact table
(refer to Figure 7-27), the logical key is comprised of the Date Key, Time Key,
Customer Key, Employee Key, Call Transaction Key, and Call Outcome Key.
In general, there is no need to create a separate surrogate key for the fact table.
This would not be used for processing queries.
Physical Database Design
Minimal design work is needed to move between a logical design and a
physical database design. This is typically the step where performance con-
siderations are taken into account. Because a dimensional model addresses
