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

230
Chapter 8
How Many
Figure 8-2
Periodic snapshot
fact table
Periodic snapshots
have fewer
dimensions than
transaction fact
tables but more
facts
Periodic snapshots
are typically loaded
on an insert-only
basis
Some monthly
periodic snapshots
can be updated on
a daily basis, to
improve ETL
processing and
provide period-to-
Although periodic snapshots share many dimensions with their corresponding
transaction fact tables, they will generally have fewer of them—because some will
be lost when transactions are rolled up to a daily or monthly level. Periodic snap-
shots will typically have more facts than transaction fact tables. Their design is
more open-ended—limited not by what is captured on a transaction, but only by
the imagination of the BI stakeholders. Adding new facts to a transaction fact table
is rare—the operational systems would have to be updated to capture more infor-
mation. But periodic fact tables are more frequently refactored with additional
facts as BI stakeholders become more creative in defining measures and key per-
formance indicators (KPIs).
Like transaction fact tables, periodic snapshots are typically maintained on an
insert-only basis. For example, daily stock levels for each product at each location,
shown in Figure 8-2 will be inserted into the STOCK FACT table at the end of each
day. Most monthly snapshots are maintained the same way—with new facts
inserted at the end of each snapshot period (month). However, for some monthly
snapshots, such as a customer account snapshot for a bank, there are benefits in
updating them on a nightly basis:
Stagger the ETL Workload: If ETL processing waits until the end of the
month it has to aggregate a whole month’s worth of transactions for each ac-
count. This makes the last night of the month a particularly heavy night: if
ETL fails, information for the whole of the last month will be unavailable.
However, if ETL is run nightly for the snapshot, it has only to insert or update
a day’s worth of transactions for only the accounts that had activity on that
day and if it fails the table is only one day out of date.
date measures
Provide Month-to-Date Facts: Although a monthly snapshot can be useful
for trending historical customer activity, it is on average 15 days out of date. If
it contains an extra month-to-date row for each customer account it can be
used to support additional operational reporting requirements.
Load monthly (and quarterly) snapshots on a nightly basis to improve ETL per-
formance and support period-to-date reporting.