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

248
Chapter 8
How Many
When heterogeneous products have many heterogeneous facts, even if they share
a common granularity, a monolithic fact table design may not be ideal. If the facts
come from different operational systems with different access methods and
maintenance cycles, separate core and custom fact tables will be easier to build
and maintain, and can be a better fit with BI user groups.
Factless Fact Pattern
Factless fact tables
record events where
there is nothing to
measure except the
event occurring
While some fact tables can have too many facts, others can contain none. Factless
fact tables are used to track events where there is nothing to measure except the
event occurrence itself. For example, SEMINAR ATTENDANCE FACT in Figure
8-13 contains one row for every prospect (and existing customer) who attends a
sales seminar to hear about Pomegranate products. Prospects do not pay for the
privilege, nor are the seminar costs notionally allocated to them, so there are no
monetary facts. The only thing to measure is the number of people who attend,
which can be obtained by simply counting the rows in the fact table.
Figure 8-13
Factless fact table
Factless fact tables
can be used as
coverage tables to
record dimensional
relationships in the
absence of other
events
Factless fact tables are also used as coverage tables to track dimensional relation-
ships in the absence of other events; for example, a promotion coverage table that
records products on promotion—regardless of sales, or a monthly healthcare
eligibility snapshot that records the fact that a person is covered by a medical plan
that month. Coverage fact tables are often used in combination with transaction
fact tables to answer questions about what didn’t happen (but should have); e.g.,
“Which products were promoted but didn’t sell?” or “How many people were
covered but didn’t claim?”