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

Dimensional Design Patterns for Cause and Effect
279
SELECT range_band, SUM(quantity_sold)
FROM
sales_fact, range_band
WHERE
range_band_group = “5 Money Bands”
AND
revenue BETWEEN low_bound AND high_bound
GROUP BY range_band
Range band dimensions allow BI users to define new bandings at any time—by
simply adding or changing dimension rows. The price for this flexibility will be
slower query performance because SQL between joins are difficult to optimize. If
certain facts are frequently used for range banding they can be indexed to improve
join and sort processing. Normally only the dimensional foreign keys are indexed.
Facts are usually not indexed because indexes do not speed up their aggregation.
But for range banding queries, the facts are acting like dimensional foreign keys.
Index facts that are
frequently used for
range banding
Figure 9-16
Range banding
a fact
Consequences
RANGE BAND GROUP, LOW BOUND, and HIGH BOUND form the primary
key (PK) of the RANGE BAND dimension, and must therefore be unique. You
should set up the LOW BOUND and HIGH BOUND values for each range band
with care: they should not overlap, and no gaps should exist. In addition, the
RANGE BAND names must be unique within each RANGE BAND GROUP. The
short code ND1 (No Duplicates) in Figure 9-15 has been added to these columns
to indicate that they form a no duplicates group (number 1)—the combination of
column values within the group must be unique.
Range bands must
be carefully defined.
They must be
unique with no gaps
and no overlaps
Step Dimension Pattern
Problem/Requirement
Chapter 7 covered techniques for overloading sequential events, such as flights or
web page visits, with first and last locations. These powerful dimensions not only
provide extra where information, they typically describe why a sequence of events
started and how it finished. For example, the first URL in a web visit can be con-
verted into a REFERRAL why dimension that describes the banner ad or search
string that triggered each click, and the last URL can become a how dimension that
describes each click by its outcome; for example, “Just browsing” or “Big shopping
trip”. Armed with this additional why and how information, BI users will often
want to analyze the position of all the intervening events relative to these pivotal
cause and effect events.
BI users want to
understand
sequential behavior
by analyzing events
relative to the cause
and effect events
within a sequence