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

Design Patterns for High Performance Fact Tables and Flexible Measures
237
Evolving Event Measures
Evolving events are implemented as accumulating snapshots. These are fact rich
fact tables because they typically combine several events, each of which brings its
own facts. These combined facts can then be used to calculate additional evolving
measures such as event counts, state counts and durations that are worth storing in
the accumulating snapshots to simplify process performance reporting.
Accumulating
snapshots are fact
rich. They contain
additional evolving
measures
Event Counts
When an evolving event has a 1:M relationship with its milestone events, define
additional event measures—such as (number of) SHIPMENTS or (number of)
DELIVERIES, in Figure 8-5—to record the number of aggregated/repeated events.
Event counts record
the number of
repeated milestones
Figure 8-5
Event and status
counts
State Counts
Each milestone date or embedded verb within an evolving event represents a state
that the event can reach. Stakeholders will often have questions about how many
orders, applicants, claims, etc. have reached a particular state. Answering these
questions can be greatly simplified by adding state counts, such as SHIPPED and
DELIVERED in Figure 8-5. These counts are 1 or 0 depending on whether a state
has been achieved or not. They can be incredibly useful because state logic can
often be more complex that you think; for example, you might imagine that
count(DELIVERY_DATE) would be an efficient way to count order items that
have reached the DELIVERED state but due to partial deliveries, it’s not quite that
simple. Instead, you have to test that DELIVERED QUANTITY = ORDERED
QUANTITY.
Event status business rules can become complex. They should be evaluated once
during ETL processing and the results stored as additive state counts to provide
simple, consistent answers for all BI queries.
State counts record
if an event has
completed a
milestone. They are
useful because
repeated milestones
mean you cannot
use milestone dates
alone to evaluate
progress