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

200
Chapter 6
Who and What
A multi-level employee dimension would allow you to handle events where
groups of employees occasionally act like individual employees. For example,
sales transactions are normally assigned to individual employees but when
several members of staff are involved or the employee is unavailable (perhaps the
individual has left the company or the transaction was customer self-service)
sales facts can be assigned an EMPLOYEE KEY that represents a team, branch or
division. In Chapter 9, we will combine multi-level and multi-valued employee
dimensions to describe how joint sales commissions can be calculated.
Do not use a multi-
level dimension to
describe fixed-level
facts
The additional flexibility of multi-level dimensions can be confusing, so they
should never be used where their flexibility is unnecessary. Create separate single
and multi-level versions of a dimension to make their usage explicit. If a star
schema has a fixed level of dimensional detail, use normal (single-level) dimen-
sions with no LEVEL TYPE attributes. The presence of a LEVEL TYPE in the star
implies that facts are multi-level when that is not the case. If a fact table truly needs
a multi-level dimension you should explicitly document it by marking the dimen-
sional foreign key as ML in the fact table, as in Figure 6-28.
Figure 6-28
Documenting single
and multi-level fact
tables
Consequences
Never use a multi-
level dimension to
create facts with
mixed meanings
You should never use a multi-level dimension to change the meaning of a fact. For
example, do not store target revenue at the brand level and actual sales revenue at
the product level in the same fact table. Sales and planning are two very different
business processes, two different verbs. How would you name and easily describe
the resulting fact table? Even sticking to a single business process, do not store
summary sales for a category in a product sales fact table. Performance enhancing
summaries require their own aggregate fact tables (described in Chapter 8). If you
used a multi-level dimension to store targets, summaries and actuals, the resulting
revenue fact would not be additive across LEVEL TYPE. To avoid over-counting,
every query would have to remember to constrain to a single LEVEL TYPE—a
recipe for disaster. The multi-level product dimension works perfectly with the
page visit fact table, in Figure 6-28, because it does not change the meaning of the
facts; they are all page visits no matter if they are for a product or a category. That
is why dwell time and total pages viewed remain additive across LEVEL TYPE.