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

186
Chapter 6
Who and What
Employee Dimensions
HV Employee
dimensions are
typically Type 2
SCDs
After customers, employees are the next most interesting who for BI. Thankfully,
because there are usually far fewer employees than customers, the Type 2 SCD
technique can work well for tracking the majority of employee HV attributes. But
employee dimensions are not without their challenges. More descriptive informa-
tion may be known and recorded about employees and the departments they work
in. If that information is tracked historically it can lead to additional BI require-
ments to analyze the organization, as represented by its employees, using current,
previous, historical and year-end descriptions.
In The Data Warehouse Toolkit, Second Edition, Ralph Kimball, Margy Ross
(Wiley, 2002) Chapter 8, “Human Resource Management” covers many of the
basic issues involved in supporting Type 2 SCD Employee dimensions.
Hybrid SCD View Pattern
Problem/Requirement
HV/CV attribute
requirements
Employee attributes have been defined as HV/CV so that stakeholders can use the
historically correct values for “as was” reporting by default but can also use current
values for “as is” reporting; for example, the stakeholder question
What were the annual expenses by employee location
for the last 5 years?
!
requires the HV location where employees were based when they incurred the
expenses. Whereas the question
What are the total expenses over the last 5 years for every
employee currently based in the London office?
!
doesn’t care where employees were based in the last 5 years, it only needs their CV
location to filter on.
Solution
Create separate HV
and CV swappable
dimensions
Create and maintain an HV version of EMPLOYEE using Type 2 SCD ETL proc-
essing to satisfy the default reporting requirements. Create a separate current value
swappable dimension (CV SD) from the HV employee dimension. Figure 6-17
shows how a CV swappable version of EMPLOYEE can be defined by joining the
HV EMPLOYEE dimension to a copy of itself constrained to current employee
definitions. In the example every version of James Bond is joined to the current
James Bond. The resulting CV SD dimension initially appears rather wasteful,
containing 3 identical Bonds but on closer examination you notice that each Bond
has a different surrogate key value.