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

Design Patterns for High Performance Fact Tables and Flexible Measures
Once you have all the events on the timeline (you may have copied the event
sequence from your event matrix), you can then start discovering durations by
pointing at the milestone pairs and asking stakeholders to name the gaps between
them. Any meaningful duration you discover should be added to the timeline, as in
Figure 8-6, which shows three important durations for an evolving order.
239
Model durations by
naming the gaps
between milestone
events
Figure 8-6
CUSTOMER
ORDERS
event timeline
showing repeating
milestones
After naming the durations on the timeline, add them to the evolving event table
with example values. As mentioned in Chapter 2, you may question the wisdom of
adding so many derived facts (DF) to the event table, but the event table is still a BI
requirements model, not yet a physical accumulating snapshot design. Its purpose
is to document the measures stakeholders will need, not dictate a physical struc-
ture. By adding a duration to the event table you are documenting its name, unit of
measurement (UoM), and value range. You are not making a decision about how,
if at all, it will be physically stored. Duration definitions can be implemented as
database views or report items in BI tool metadata layers. Add durations to the
Figure 8-7 shows three durations—PACKING TIME, DELIVERY TIME, and
DELIVERY DELAY—added to the event as derived facts. Their definitions can be
recorded using simple spreadsheet-like formula by numbering the event milestones
DT1 to DT4. For example, PACKING TIME is defined as DT2 – DT1: the interval
between ORDER DATE [DT1] and SHIP DATE [DT2]. The DTn numbering can
also be used to record the chronological order of the milestones. Number milestone
evolving event table
as derived facts, to
document their UoM
and range of values
dates DT1-DTn to
reference them in
duration definitions
Figure 8-7
Duration measures