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

174
Chapter 6
Embedded
whos can be
remodeled as
separate who
dimensions
Who and What
ACCOUNT MANAGER, should be modeled as a separate dimension using a
combination of the mini-dimension and sensible snowflaking patterns, as shown in
Figure 6-6. The ACCOUNT MANAGER attribute is implemented as a foreign key
to the EMPLOYEE dimension. This makes all the account manager’s descriptive
attributes available without bloating the CUSTOMER dimension. To prevent this
potentially volatile relationship between account managers and customers from
turning CUSTOMER into a rapidly changing dimension, the foreign key is defined
as CV, so that it represents the current account manager. The stakeholder’s re-
quirement for reporting by the HV account manager is handled by fact table
relationship. As with the mini-dimension pattern, the direct relationship can be
defined as a shortcut join, or a second view can be created on EMPLOYEE to act as
an outrigger playing the role of CURRENT ACCOUNT MANAGER.
Figure 6-6
Modeling an
embedded who
as a separate
HV dimension and
a CV outrigger
Recursive Relationship
A who within a who
of the same type is
a recursive
relationship
Looking at the PARENT COMPANY examples, in Figure 6-5, you can see that it
contains companies that are present as customers in the CUSTOMER dimension.
This represents a recursive relationship which would be drawn in ER notation, as in
Figure 6-7, with a M:1 relationship between the customer entity and itself. The
relationship documents that each customer may own one or more customers and
each customer may be owned by one customer.
Figure 6-7
M:1 recursive
relationship or
“head scratcher”
This relationship can be implemented in CUSTOMER by replacing the remaining
embedded who with another foreign key. But this time the new foreign key:
PARENT KEY will not refer to a different dimension, instead it will point back to
the primary key of CUSTOMER itself. This makes PARENT KEY a recursive
foreign key, denoted by the code RK in Figure 6-8.