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

Design Patterns for High Performance Fact Tables and Flexible Measures
243
Fully Additive Facts
(Fully) additive (FA) facts produce meaningful results when summed using any
combination of the available dimensions. For example, REVENUE in Figure 8-1
can be summed across customers, products, time, and locations—and will always
produce a correct total revenue. Additive facts are the easiest to use because there
are no special rules about which dimensions they work with, so default measures
can be quickly defined in BI tools using SQL sum()function. For this reason it is
always best to record fact information in its most additive form. (Fully) additive facts
The first rule for defining an additive fact is to use a single unit of measure. For
example, while modeling an event you may identify a quantity that is recorded in
multiple currencies that are documented as [£, $, ¥]. The corresponding fact needs
to be converted into a standard currency, otherwise the fact will not be additive
across currency. Additive facts
can be summed
using any
combination
of their available
dimensions
must use a single
standard unit of
measure
Store facts in a single unit of measure to make them additive and avoid aggrega-
tion errors. If BI applications need to view facts in different units of measure—e.g.,
report sales in local and standard currency, or product movements in shipping
crates rather than retail units—provide conversion factors. These should be
stored centrally in the data warehouse (as facts) rather than in the BI applica-
tions—because they can change.
Non-Additive Facts
Non-additive (NA) facts cannot be summed, even if they are in the same unit of
measure. For example, UNIT PRICE cannot be summed to produce a meaningful
total—even if all unit prices are recorded in dollars. Instead UNIT PRICE can be
averaged, or used to create an additive SALE VALUE (UNIT PRICE × SALE
QUANTITY) fact. BI users will likely want to use this additive measure more often
than UNIT PRICE, so it should be stored in the fact table, and if storage is an
overriding concern, the non-additive fact should derived, at query time, by just the
reports that need it. Non-additive
Percentages are non-additive; two product purchases with a discount of 50% do
not equate to a 100% discount. Because of this, percentages make terrible facts, but
they do make great measures and KPIs that BI users will want to see on reports and
dashboards. Facts like DISCOUNT should be stored as an additive monetary
amount (as in Figure 8-1), allowing BI tools to calculate the correct percentages
within the context of a report. Percentages are
Timestamps are non-additive facts, but pairs of timestamps can be subtracted to
produce duration facts that can be treated as additive or semi-additive.
facts can never be
summed to produce
meaningful answers
non-additive. Only
their additive
components should
be stored as facts