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

Design Patterns for High Performance Fact Tables and Flexible Measures
241
Figure 8-8
Timeline dashboard
Developing Accumulating Snapshots
When you have added all the useful evolving measures to an evolving event, it is
time to profile its data sources and, all being well, design an accumulating snapshot
fact table such as ORDER FACT [AS], shown in Figure 8-9. This is an accumulat-
ing snapshot version of the original transactional ORDER FACT [TF] from Chap-
ter 5. How you go about developing this or any accumulating snapshot depends on
the data profiling results. If profiling confirms that all the milestones have a 1:1
relationship and can be obtained from the same source system then you can build
the accumulating snapshot directly. This would be the approach for the LENDING
FACT [AS] snapshot in Figure 8-3. Because there is a 1:1 relationship between
book loans and book returns, no detail is lost, and most importantly for ETL,
because these different transactions are handled by a single operational system (for
each library) there will be no conformance issues in merging them. When milestone
ORDER FACT [AS] has a more complex 1:M or M:M relationship between its
milestones which are handled by multiple sales and logistics systems managed by
Pomegranate, its distributors and carriers. Attempting to populate this fact table
directly is a high risk, “big development up front” strategy. (Another form of BDUF
that you should avoid). It is unlikely that an accumulating snapshot with such
complex sourcing issues could be successfully delivered in one or two normal
length development sprints. So, while it’s being developed what would be demon-
strated, or validated by stakeholder prototyping? Nothing? That’s not particularly
agile. Instead each of the accumulating snapshot’s milestones can initially be
developed and delivered, on a more agile basis, as separate transaction fact tables. If milestone events
If an evolving event is modeled retrospectively, you will already have all (or most)
of the discrete event definitions for its milestones (you may have discovered
additional milestones while modeling the evolving event). These are the blueprints
for transaction fact tables that can stage the milestone events prior to merging
them in the ultimate accumulating snapshot. If you don’t yet have these, you can
model them using the techniques described in Chapters 2-4 by pulling out their
verbs from the milestone prepositions and asking: “who does each of these?” Transaction fact
events have a 1:1
relationship and are
handled by the
same operational
system,
accumulating
snapshots can be
developed directly
have more complex
relationships or are
handled by different
operation systems,
accumulating
snapshots should
be developed
incrementally
tables are used to
stage accumulating
snapshot data,
validate the design
and deliver early BI
value