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

278
Chapter 9
Why and How
Don’t tell stakeholders that any of their data is “junk”, especially when you are
modelstorming with them. If you are looking for a less pejorative term for your
non-conformed how dimensions, call them miscellaneous dimensions.
Range Band Dimension Pattern
Problem/Requirement
BI users want to
group by the facts
but need to rollup
the answers
BI users want to group by facts, such as REVENUE and ORDER QUANTITY, and
count the unique occurrences of customers, products or transactions. They need to
use a fact like a dimension and treat a dimensional attribute like a measure. Con-
verting a dimensional attribute like CUSTOMER ID into a measure can be
straightforward using COUNT(DISTINCT …) but it requires more work to turn
raw facts into good GROUP BY items. Because facts are mostly high cardinality,
continuously valued numbers, grouping by them rolls up very little data and
produces too many report rows: more data dump than readable report.
Solution
Provide a range
band dimension to
“turn facts into
dimensions”
Numeric range band dimensions such as RANGE BAND, shown in Figure 9-15,
are another type of how dimension. They are how many dimensions or “How do
you turn a fact into a dimension?” dimensions that convert continuously valued
high cardinality facts into better discrete row headers. Chapter 6 described how
high cardinality dimensional attributes should be stored as range band labels that
are more useful for grouping by. Range band dimensions allow this to be done
dynamically at query time to facts and other numeric dimensional attributes.
Figure 9-15
RANGE BAND
dimension
Range band
dimensions convert
high cardinality facts
into useful low
cardinality report
row headers
Figure 9-15 is an example of a general-purpose range band dimension that can
store any number of range band groups. The example data shows two groups: “5
Money Bands” that would be used to group REVENUE into 5 bands and “4 Age
Bands” that can be joined to a customer or employee age to group a population
into 4 bands. Figure 9-16 shows how the RANGE BAND dimension is joined to
SALES FACT to count the number of products sold in each of the 5 revenue
ranges—effectively converting the REVENUE fact into a dimension on-the-fly.
The SQL for the query would be: