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

152
Chapter 5
Figure 5-11
Splitting a when
detail into separate
Calendar and
Clock dimensions
Modeling Fact Tables
Save copies of
event tables before
you convert them
into fact tables
Rename fact tables
and record their fact
type
Once all the dimensions have been updated with surrogate keys, you can then
convert the event tables into fact tables by replacing their who, what, when, where,
and why details with dimension foreign keys, while leaving quantities (how many)
and degenerate dimension (DD) how details in place. Because you are changing
columns, not just adding new ones, you should save copies of the original event
tables for future modelstorming. The fact table versions will be used for creating
star schemas and communicating design techniques within the DW/BI team.
With the event table copies saved you can replace event names with fact table
names and change story types to fact table types. In Figure 5-12 the CUSTOMER
ORDERS discrete event has been renamed ORDERS FACTS and its story type DE
replaced with the fact table type TF for transaction fact. Chapter 8 describes each
of the fact table types in detail.
The following table codes are used to identify fact table type:
[TF] : Transaction Fact table, the physical version of discrete events
[PS] : Periodic Snapshot, the physical version of recurring events
[AS] : Accumulating Snapshot, the physical version of evolving events
Replace Event Details with Dimension Foreign Keys
Change dimensional
details to surrogate
keys by marking
them as SK
Replace all of the dimensional details with surrogate keys by renaming the columns
and changing their type to SK. In Figure 5-12 CUSTOMER, PRODUCT, ORDER
DATE, SALESPERSON and PROMOTION have been replaced by the appropri-
ately named surrogate keys, and their examples changed to surrogate key integer
values.