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

Dimensional Design Patterns for People and Organizations, Products and Services
167
Micro and Macro-Level Change
Dimension members can experience two different types of change which will
impact how well ETL processes handle HV attributes:
Micro-level change occurs when individual dimension members experience Most Type 2 SCDs
change that is unique to them; for example, a customer changes can cope well with
CUSTOMER CATEGORY and goes from being a “Good Customer" to a individual micro-
"Great Customer". If CUSTOMER CATEGORY is an HV attribute, one row level changes
will be updated to give the old value an end date and one row will be in-
serted with the new value. Hierarchically, this is “change from below”.
Macro-level change occurs when many dimension members are changed Macro-level, global
at once; for example, every "Great Customer" becomes a "Wonderful Cus- changes are more
tomer". Rows affected: 1,000,000 updated, 1,000,000 inserted. Hierarchi- challenging. Should
cally, this is “change from above”: it’s not customers who have changed but they be handled as
CUSTOMER CATEGORY itself. The category "Great Customer" has changes or
changed to "Wonderful Customer". corrections?
For most dimensions with moderately volatile HV attributes, micro-level changes
can be easily and usefully tracked, but it is much harder to justify macro change
tracking. A single macro-level change can cause millions of historical versions of
customers to be created for little or no analytical value. In the case of every
“great customer” becoming a “wonderful customer” should this be tracked using
normal HV attribute behavior? You many need to define separate ETL processes
that treat certain macro-level changes as one-time corrections.
Solution
Rapidly changing HV customer attributes have a high cardinality many-to-many
(M:M) relationship with customer. One possible solution for tracking these attrib-
utes is to model them just as you would model other customer M:M relationships,
such as the products they consume, or the sales locations they visit. Products and
locations are of course modeled as separate dimensions and related to customers
through fact tables. The same can be done with volatile customer attributes by
moving them to their own mini-dimension.
Figure 6-1 shows CUSTOMER DEMOGRAPHICS, a customer mini-dimension
formed by relocating the volatile HV CUSTOMER attributes relating to location,
family size, income, and credit score. This mini-dimension has its own surrogate
key (DEMO KEY) which is added to customer-related fact tables to describe the
historic demographic values at the time of each fact. With fact relationships used to
track history, all the problematic HV attributes can be removed from
CUSTOMER, or changed to CV only. This would leave you with an entirely CV
CUSTOMER dimension that only grows as new customers are acquired.
Volatile HV
attributes have a
high cardinality M:M
with their dimension
They can be stored
in a separate mini-
dimension with its
own surrogate key
and related to the
dimension through
fact tables