The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling, 3rd Edition

Chapter 9

Human Resources Management

This chapter, which focuses on human resources (HR) data, is the last in the series dealing with cross-industry business applications. Similar to the accounting and finance data described in Chapter 7: Accounting, HR information is disseminated broadly throughout the organization. Organizations want to better understand their employees' demographics, skills, earnings, and performance to maximize their impact. In this chapter we'll explore several dimensional modeling techniques in the context of HR data.

Chapter 9 discusses the following concepts:

• Dimension tables to track employee profile changes
• Periodic headcount snapshots
• Bus matrix for a snippet of HR-centric processes
• Pros and cons of packaged DW/BI solutions or data models
• Recursive employee hierarchies
• Multivalued skill keyword attributes handled via dimension attributes, outriggers, or bridges
• Survey questionnaire data
• Text comments

Employee Profile Tracking

Thus far the dimensional models we have designed closely resemble each other; the fact tables contain key performance metrics that typically can be added across all the dimensions. It is easy for dimensional modelers to get lulled into a kind of additive complacency. In most cases, this is exactly how it is supposed to work. However, with HR employee data, a robust employee dimension supports numerous metrics required by the business on its own.

To frame the problem with a business vignette, let's assume you work in the HR department ...