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

Dimensional Design Patterns for People and Organizations, Products and Services
201
Parts Explosion Hierarchy Map Pattern
Problem/Requirement
Stakeholders need to analyze product sales down at the level of the components
that went into the products using product bill of materials (BOM) data. The bill of
materials for a product can be represented as a variable-depth hierarchy of compo-
nents within components; for example, Figure 6-29 shows the BOM hierarchy for a
new product sold by employee James Bond. It reveals that the “POMCar” is made
up of an “off the shelf” Aston Martin DB5 and an enhanced safety pack, that is
itself made up of a number of interesting gadgets. A bill of materials like this is
typically stored in an operational system using M:M recursive structures that allow
the components of a product or service to be made up of other products and
services.
A product bill of
materials represents
a variable depth
hierarchy of
components
Figure 6-29
Bill of materials
for a POMCar
Solution
A BOM can be represented by the PARTS EXPLOSION hierarchy map in Figure 6-
30. This is a reverse hierarchy map which joins to product facts (and the product
dimension) by its parent key (PRODUCT KEY) as in Figure 6-31, allowing the
facts to be rolled down to or filtered on child components. It contains a SUB
ASSEMBLY flag that indicates “Y” if a component is made up of other identifiable
components and QUANTITY, which records the number of components that go
into the finished product. This is similar to a distant weighting factor in that it
needs to be adjusted in the hierarchy map based on its parent quantities. For
example, a single defense system contains 4 motion sensors, but a POMCar con-
tains 2 defense systems, so the quantity of motion sensors it contains is 8.
A reverse hierarchy
map joins to fact
tables by its parent
key and allows facts
to be allocated to
child levels
Figure 6-30
PARTS
EXPLOSION
hierarchy map
Add a cost or revenue recovery weighting factor to a BOM hierarchy map to
allocate revenue facts to product components (or the sub-products of a bundled
offering) based on their quantity and unit value.