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

Dimensional Design Patterns for People and Organizations, Products and Services
189
CV/PV : Current and previous value requirement.
CVn : Current Value attribute linked to a PVn previous value attribute.
PVn : Previous Value attribute. Always linked to a CVn attribute.
Figure 6-18
Implementing a
previous value
attribute
PV attributes can be used to hold initial or “as at specific date” values; for exam-
ple, INITIAL TERRITORY PV1 or YE2011 TERRITORY PV1.
Previous Value Attribute Consequences
Defining a small number of hard-coded PV attributes can work well but maintain-
ing large sets of hard-coded PV attributes within a dimension is cumbersome for
both ETL and BI. Instead, define an HV only dimension and provide PV (and CV)
attributes through hot swappable dimension views.
Human Resources Hierarchies
Organization reporting structures are another example of variable-depth hierar-
chies. These human resources (HR) hierarchies can be even more challenging than
customer ownership hierarchies due to their high level of interconnection. Em-
ployees are far more related to one another than customers are: all employees
ultimately work for the same parent, the CEO. This results in a HR hierarchy map
containing a single large volatile hierarchy, rather than thousands or millions of
small relatively stable ones. This, coupled with greater availability of data, and
requirements to track history more precisely, can make HR hierarchies the most
difficult hierarchies to implement in the data warehouse.
HR hierarchy maps
can be challenging
because employees
are highly
interconnected