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

Modeling Business Dimensions
91
always HV but also triggers a conditional HV attribute: HV3, it would be marked
HV, HV3 rather than CV, HV3.
Effective Dating
When you have captured change stories and temporal business rules for each
attribute, add three more attributes to the dimension table: EFFECTIVE DATE,
END DATE, and CURRENT as in Figure 3-17. These additional administrative
attributes enable ETL processes to track changes and flag the current version of
each member. They effectively convert HV dimensions into minor event tables
capable of recording numerous small events.
Add administrative
attributes to each
dimension for
effective dating
Figure 3-17
Effective dating a
dimension table
With the addition of effective dating, readers who are familiar with how type 2
slowly changing dimensions are implemented will notice how closely the change
story row in a BEAM ✲ dimension matches this ETL technique. This is intentional
as BEAM ✲ models are designed to be translated into physical dimensional models
with minimal changes. It is also important that BEAM ✲ modelers do not refer to
HV attributes as type 2 SCDs or attempt to modelstorm the final piece of their
puzzle: surrogate keys, with business stakeholders. Type n SCD terminology and
surrogate keys (covered in Chapter 5) are appropriate star schema-level topics for
discussion with ETL and BI developers not stakeholders.
Change stories
demonstrate type 2
slowly changing
dimension behavior
but don’t use this
ETL jargon with
stakeholders
Documenting the Dimension Type
When you have completed a dimension (for now), add a double bar to the end of
the table just as you would for an event, and add its dimension type to the table
header. To do this you use one of the temporal short codes. If the dimension
contains at least one HV attribute mark the dimension as [HV], otherwise mark it
as [CV] to denote that it contains only CV, FV or PV, i.e., its members will not
include multiple historic versions.
Mark dimensions
that contain no HV
attributes as [CV]