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

52
Chapter 2
Derived quantities
help define BI
reporting
requirements
Derived Quantities
Some modelers may question the need for modeling duration quantities. If time-
stamps are present then durations can be calculated rather than stored. This is true,
but BEAM ✲ tables are BI requirements models for documenting data and report-
ing requirements not physical storage models. By documenting durations and
other derived measures as event details you have the opportunity to capture their
business names and document their maximum and minimum values (in stories),
which can be used as thresholds for dashboard alerts and other forms of condi-
tional reporting.
BEAM✲ event tables do not translate column for column into physical fact tables.
When an event table is physically implemented as a star schema the majority of
its non numeric details will be replaced by dimensional foreign keys, and some of
its quantities can be replaced by BI tool calculations or database views. This
process is covered in Chapter 5.
Why?
Time to ask “Why?”
Capturing why details is the next step in modeling the event. As with the other “W”
questions you ask a why question using the main clause of the event:
Why do CUSTOMERs order PRODUCTs?
might be a little open ended but
Why do CUSTOMERs order PRODUCTs in these
quantities on these dates at these locations?
Why details often
explain quantity
variations
will focus the stakeholders on identifying the causal factors that specifically explain
variations in event quantities. The why details you are looking for can include
promotions, campaigns, special events, external marketplace conditions, regulatory
circumstances or even free-form text reasons for which data is readily available. If
the stakeholders respond with:
Product promotions.
Try to discover
typical and
exceptional why
stories
you would expand the event table as shown in Figure 2-11, and add example stories
that illustrate typical and exceptional circumstances. Notice that the typical pro-
motion is “No promotion” and that the why detail has prompted the stakeholders
to supply an additional DISCOUNT quantity. Chapter 9 provides detailed coverage
on modeling why details as causal dimensions.
If event stories show wide quantity variations, point this out as you model why
details. Ask stakeholders if there are any reasons that would explain these varia-
tions? If causal descriptions are well recorded they may also lead you to discover
additional quantities.