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

244
Chapter 8
Non-additive facts
can be aggregated
using other functions
such as min, max or
average
How Many
Percentages and unit prices can easily be converted into additive facts, but other
quantities cannot. These facts have to be clearly documented as non-additive along
with their compatible alternative methods of aggregation for creating useful meas-
ures. For example, TEMPERATURE NA is a non-additive fact that can be aggre-
gated using functions such as min, max, and average.
Semi-Additive Facts
Semi-additive facts
are harder to work
with than additive or
non-additive facts
Semi-additive facts
can be summed but
not across their non-
additive dimension(s)
To fully document a
semi-additive fact
the SA fact code is
used in conjunction
with at least one NA
dimension code
Additive facts are easy to work with—they can be summed with impunity. Non-
additive facts require a little more creativity to aggregate, but after an appropriate
measure formula has been found they too are relatively straightforward to deal
with: you simply never sum them up. Semi-additive facts are more problematic.
A semi-additive (SA) fact can be summed up some of the time but you can’t sum it
up all of the time. To be more precise: a semi-additive fact cannot be summed
across at least one dimension: its non-additive dimension. For example, yesterday’s
STOCK LEVEL cannot be added to today’s STOCK LEVEL. It is non-additive
across the time dimension. But STOCK LEVEL is additive across other dimen-
sions. It can be summed for all stores and/or all products (apples and pomegran-
ates?) to give a correct total stock level, as long as the query is constrained to a
single day—a single value of the non-additive dimension.
Semi-additive facts are fully documented by marking them as SA and their non-
additive dimension(s) as NA. If there is a single semi-additive fact in a fact table or
if all semi-additive facts have the same non-additive dimension(s) this is sufficient.
However, if there are multiple semi-additive facts with differing non-additive
dimensions, the SA and NA codes are linked by numbering, to pair each SA fact to
its NA dimension(s). For example, Figures 8-2 and 8-10 show the BEAM ✲ table
and matching enhanced star schema for STOCK FACT, a daily periodic snapshot
of in-store inventory. Both show STOCK LEVEL SA1 is non-additive across
STOCK DATE KEY NA1, whereas ORDER COUNT SA2 is non-additive across
PRODUCT KEY NA2. This semi-additive fact documentation can be used to
correctly define measures in BI tools and some multidimensional databases. SQL
doesn’t natively understand that some numbers are semi-additive, this can cause
averaging and counting issues for the unwary BI developer.
Averaging Issues
Semi-additive facts
can be averaged but
not by using AVG( )
Although semi-additive facts cannot be summed over their non-additive dimen-
sions, they can often be averaged (carefully) over them. Unfortunately the SQL
AVG() function may not be up to the job; for example, if stakeholders ask:
What was the average stock of Advanced Laptops
in the SW region last week?