My first Publication Agile-Data-Warehouse-Design-eBook | Page 174

Modeling Star Schemas
Replacing the examples is an optional step; you might change them to integer
sequence numbers, as we have here, if you are using a BEAM ✲ table to explain a
surrogate key technique to the team. Alternatively, you can leave the descriptive
examples from the original event modeling unaltered so that you don’t have to
keep referring to the separate dimension tables to understand the event stories
behind the facts. Regardless of what you do to the examples, a column type of SK
documents that a fact column is an integer dimension foreign key in the physical
database schema.
153
Leave descriptive
examples for
readability or
change them to
integers to explain
SK techniques
Figure 5-12
Creating the
ORDERS FACT
table
Modeling Degenerate Dimensions
Degenerate dimensions (DD) such as ORDER ID are not replaced by a surrogate
key because they have no additional descriptive attributes that need to be refer-
enced. Degenerate transaction IDs allow stakeholders and ETL processes to tie
facts back to their original operational transactions. They also provide useful ways
of uniquely counting business events, especially in the presence of multi-valued
dimensions (covered in Chapter 9). If a fact table contains a large collection of
degenerate dimensions you should consider moving these to a new how dimension
(sometimes referred to as a junk dimension) to reduce fact table size. How dimen-
sions are also covered in Chapter 9.
Degenerate
dimensions (DD)
remain in fact tables
to tie facts back to
source transactions
and provide unique
counts
Modeling Facts
The remaining quantity columns in the fact table are defined as facts. Facts should
be modeled in their most additive form, so that they can be easily aggregated at
query time. Additivity describes how easy or possible it is to sum up a fact and get a
meaningful result. The ideal facts are fully additive (FA) ones that can be summed
using any of their dimensions. The remaining how
The three order facts in Figure 5-12 have all been defined as fully additive (FA). To
convert the raw how many details to (fully) additive facts they must be stored using
consistent additive units of measure (UOM). ORDER QUANTITY can use the
product units from the original business events, but REVENUE and DISCOUNT
which originally showed examples in numerous currencies must be transformed Full additive (FA)
many details are
converted into facts
that can be
aggregated
facts are ideal
because they can
be summed using
any dimension