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

Modeling Business Dimensions
89
Business Keys and Change Stories
As a general rule, business keys that uniquely identify dimension members and
define dimensional granularity should not change over time; they should be de-
fined as fixed value (FV) and you should simply copy their typical values into the
change story. If this is not the case and business keys have been known to change
you will need to model their CV or HV rules and help design more complex ETL
processes to identify these awkward changes and handle them appropriately when
they occur.
Business Keys (BK)
should be FV by
default
Detecting Corrections: Group Change Rules
One of the ETL complexities of handling HV updates is how to distinguish be-
tween corrections that should overwrite errors and genuine changes that should
preserve history. This is a non-issue for FV and CV attributes because they handle
correction and change alike (FV attribute updates are always corrections). Being
able to tell the difference between corrections, minor changes and major changes is
especially important when designing the ETL for large, highly volatile dimensions
such as customer because tracking every change may not even be possible, let alone
necessary. HV ETL processes
Ideally, source systems should provide reason codes for the most important HV
attribute updates. Unfortunately, explicit reason descriptions are not often avail-
able. One of the many benefits of proactive dimensional modeling is that ETL
designers can take advantage of preemptive HV definitions to request that not only
update notification but update reason notification is built into a new operational
system while it is still in the early stages of development. Source systems
If update reasons are not available, the next best thing is to define business rules
that identify important changes based on groups of attributes that should all
change at the same time. An example group change rule which tracks only “large”
changes affecting several attributes at once, might be: Group change rules
must differentiate
corrections from
changes
rarely provide
update reasons
to help detect
corrections
can help detect
corrections and
minor changes
“If customer STREET Address changes but ZIP CODE is unchanged, then handle
the update as a correction (or minor move in the same Zip code area): do not
preserve the existing address. If STREET and ZIP CODE change together, track
the customer’s address history prior to this major relocation”
You can discover these rules by asking general questions like “What attributes
change together?” or specific questions for each attribute such as “what other
attributes must change when this changes?”. You can also tie your questions to
some activity; you might ask:
When customers really move — rather than just correct
their address — which attributes should change?
To discover these
rules ask for
attributes that
change together