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

Dimensional Design Patterns for People and Organizations, Products and Services
Multi-level dimensions contain additional rows that represent all the multiple
levels within their hierarchies that are needed to describe mixed-level facts. For
example, a multi-level product dimension contains records for each product and
additional records for each brand, subcategory, and category if facts need all these
levels. Figure 6-27 shows a multi-level PRODUCT dimension, denoted by the code
ML, that contains example additional rows that represent entire categories (SKs -1
and -2), a subcategory (SK -3), and a brand (SK -4). The complete table would
contain one additional row for every value at every level needed.
199
A multi-level
dimension (ML)
contains additional
rows that represent
level values within its
hierarchy
Figure 6-27
Multi-level
Product
dimension
ML: Multi-Level dimension that contains additional members representing
multiple levels in its hierarchy. Also used to document an event detail or di-
mensional foreign key that represents a multi-level dimension.
Multi-level dimensions also contain an additional attribute LEVEL TYPE that
documents the meaning of each row in the dimension. The majority of rows will be
normal members that represent individual products (or employees in the case of a
multi-level employee dimension). Their LEVEL TYPE defaults to the name of the
dimension itself, whereas the additional rows will be labeled after the level attribute
in the hierarchy they represent. LEVEL TYPE is useful for ETL processes that
manage the use of these additional records, and can also be used by queries that
want to constrain on specific level facts only. LEVEL TYPE can be ignored by most
queries that simply want to roll up all the facts to a particular level. For example, a
query using PRODUCT [ML] could group by CATEGORY and count web page
visits to get the total pages viewed for each category; the figures would automati-
cally include pages for individual products, brands, and subcategories within each
category, as well as pages for the categories themselves.
You capture multi-level dimension requirements when stakeholders tell group
themed event stories using example values that normally appear at higher levels
in hierarchies; e.g., they give you “MI6” when you were expecting "Bond”.
Multi-level
dimensions contain
a LEVEL TYPE
attribute which
documents the
meaning of each
row