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

Dimensional Design Patterns for People and Organizations, Products and Services
169
Figure 6-2
Creating a
mini-dimension
The CURRENT DEMO KEY also allows queries to ask questions using current
demographic descriptions; for example, the stakeholder question “how many high
income customers are there?” (with no further qualification it must mean currently
high income) can be answered by joining CUSTOMER and CUSTOMER
DEMOGRAPHICS directly without having to go through the fact table. This uses a
shortcut join which is not compatible with fact related queries. For BI tools that do
not support shortcut joins or for queries that need both current and historic
demographics, a view can be created on the mini-dimension to play the role of
CURRENT DEMOGRAPHICS as in Figure 6-2. This customer dimension outrig-
ger could be used to answer interesting questions like “How many customers who
are currently married, last purchased from us when they were single?”
A mini-dimension surrogate key should be added to all fact tables associated with
the main dimension, where it represents a historical value foreign key (HV, FK).
The mini-dimension surrogate key should also be added to the main dimension as
a current value foreign key (CV, FK) to support ETL processing and CV “as is”
reporting.
Consequences
Mini-dimensions increase the size of fact tables by adding foreign keys. If many
high cardinality HV attributes must be tracked, they may need to be separated into
multiple mini-dimensions, to control both main and mini-dimension size. Each
mini-dimension that you create will contribute an extra foreign key and index to
the fact tables.
A mini-dimension
foreign key (CV, FK)
allows the mini-
dimension to be
used to answer
current value
questions