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

Dimensional Design Patterns for Cause and Effect
273
Pivoted Dimension Pattern
Problem/Requirement
A number of Pomegranate products are highly configurable (e.g. the POMCar).
For marketing and manufacturing purposes, BI users would like to analyze cus-
tomer option choices, particularly which options are frequently chosen together,
and which options are added to base products that already had certain other
“options as standard”. The options themselves are also products and services
stored in the PRODUCT dimension—some can be sold standalone, others cannot.
Figure 9-13 shows a proposed enhancement to the order processing star schema to
handle the option analysis requirements. It includes an OPTION PACK [MV]
bridge table that allows the PRODUCT dimension to play the additional roles of
custom ordered option (by adding OPTION PACK KEY to ORDERS FACTS) and
standard option (by adding a STANDARD PACK KEY foreign key to PRODUCT).
BI users want to
analyze event
combinations
Figure 9-13
OPTION PACK
bridge table added
to ORDER FACTS
By creating two role-playing views (OPTION and STANDARD OPTION) it is easy
to construct a query for the users’ first question type:
How many products with option 4 as standard were
customized by adding option 5?
Their second question type:
What were the most popular customized products ordered
with option 2 or option 3?