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

Modeling Business Dimensions
85
CV attributes enable the data warehouse to produce the same results as existing
operational reports — often an initial acceptance criteria for stakeholders.
Unfortunately problems arise when current values are the only descriptions avail-
able for DW/BI systems, which by definition, must support accurate historical
comparisons. CV attributes may be capable of answering questions such as:
“Where are the customers, now, who bought … in the last three years?” or “What
are the top selling products this year vs. last year?” But they cannot answer: “Where
were those customers and what were they like when they bought our products?” or
“Exactly what were products like (how were they described and categorized) at the
time they were purchased?” These questions cannot be answered because dimen-
sional history is lost when CV attributes are updated (overwritten). With current values
Another limitation of current value only solutions is that they cannot reproduce
previous historical analyses. The same report with exactly the same filters will often
yield different results when run later — even though every detailed event remains
unaltered — because the reference data used to group, sort and filter the events has
changed when it should not have. This is a common bane of reporting from opera-
tional sources that stakeholders do not want repeated in the data warehouse. Current value only
It’s not all bad news for CV attributes. Even though they are historically incom-
plete/incorrect, they can be useful for certain types of historical comparisons that
recast history: deliberately pretend everything was described as it is now. For
example, a sales manager who wants to compare channel sales for this year versus
last year, may need to pretend that today’s channel structures also existed last year,
in order to make the comparison. This is exactly what CV channel description
attributes will do. Current value
only, dimensional
history is lost and
many potentially
important BI
questions cannot
be answered
correctly
designs make it
impossible to
reproduce reports
when dimensions
change
attributes can
usefully recast
history
Corrections and Fixed Value Attributes
Current values are also entirely appropriate when mistakes are corrected and
previous erroneous values should never be used again. For example, when a cus-
tomer or employee’s date of birth changes in an operational database, we know it
hasn’t really changed (from one corrected date to another), it must be a correction.
We assume that the most recent (current) value is correct as someone has gone to
the trouble of making the update. Date of Birth is an example of a fixed value (FV)
attribute that cannot change but can be corrected (fixed when it’s not right). FV
attributes have a strict 1:M relationship over time with the dimension. All other
attributes have potentially a M:M relationship over time with the dimension, which
is ignored (treated as M:1) in the case of CV attributes.
Current values are
historically correct
for fixed value (FV)
attributes that do not
change over time
but can be corrected