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

Dimensional Design Patterns for Cause and Effect
as fast as possible and produce “unarguable” answers. Even queries that filter on
one specific ICD10 code produce similar fast “unarguable” answers. Only when BI
users want to analyze by multiple diagnoses do they have to consider the weighting
factors and argue about allocations. When they do, they can choose to ignore
weighting factors and look at the unweighted treatment costs, use the default
(crude) weighting factors in DIAGNOSIS GROUP (which add up to 100% for each
diagnosis group) or model their own weighting factors in swappable versions of
DIAGNOSIS GROUP and use those instead.
269
Bridge tables
provide flexible
multi-valued
reporting. Users
can choose how
they weight the
facts at query time
When a multi-valued dimension, such as DIAGNOSIS, is constrained to a single
value the multi-valued allocation problem goes away for that query and any
additive facts, such as Claim Amount, can be summed without over-counting.
Figure 9-8
Using a
multi-valued
bridge table
If diagnosis combinations seldom repeat, a simple ETL process could create a
new diagnosis group for every claim using the CLAIM ID as a DIAGNOSIS GROUP
KEY. This would avoid having to add a new foreign key to the claim facts as the
degenerative CLAIM ID would already be present. However, if combinations
frequently reoccur a more sophisticated ETL process can reuse common diagno-
sis groups (to reduce bridge table growth) by using a dedicated surrogate key for
the bridge table. This approach would be likely to have greater BI value because
frequently occurring groups could be more easily found (by ranking on
DIAGNOSIS GROUP KEY—breaking our rule on hiding surrogate keys) and their
weighting factors adjusted by hand if necessary.
Consequences
When you discover a potential multi-valued dimension you should first check that
the granularity of the facts is correct before complicating the design with a bridge
table. If this is an aggregation of the available operational details you may be able to
turn the multi-valued dimension into a normal dimension by going down to the
atomic level of detail. For example, if you modeled an invoice fact table with a
granularity of one row per invoice then PRODUCT would be a MV dimension.
Modeling the atomic invoice line items easily solves this. However, if you are
already at the atomic-level you can avoid “splitting the atom” and creating mean-
ingless (unstable subatomic) measures by using a bridge table.
Check that fact
granularity is correct
(atomic) before
using a bridge table
design