Chapter 8 : Human Resource Management

Chapter 8 : Human Resource Management

Time-Stamped Transaction Tracking in a Dimension
Thus far the dimensional models we have designed closely resemble each other
in that the fact tables have contained 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, many of the facts aren’t
additive. Most of the facts aren’t even numbers, yet they are changing all the time.


Factless Fact Table

We envision using the type 2 slowly changing dimension technique for tracking

changed profile attributes in the employee dimension. Consequently,

with every employee transaction in the fact table in Figure 8.1, we also create

a new type 2 row in the employee dimension that represents the employee’s

profile as a result of the transaction event. It continues to accurately describe

the employee until the next employee transaction occurs at some indeterminate

time in the future. The alert reader is quick to point out that we’ve

designed an employee transaction fact table and a type 2 employee dimension

table with the exact same number of rows, which are almost always

joined to one another. At this point dimensional modeling alarms should be

going off. We certainly don’t want to have as many rows in a fact table as we

do in a related dimension table.

Time-Stamped Dimension with Periodic Snapshot Facts

In addition to profiling the employee base in HR, we also need to report summary

statuses of the employee base on a regular, monthly basis. We’re interested

in counts, statistics, and totals, including such things as number of

employees, total salary paid during the month, vacation days taken, vacation

days accrued, number of new hires, and number of promotions. We want to

analyze the data by all possible slices, including time and organization. We

need to access totals at the end of each month, even when there is no transaction

activity in a given employee’s profile during that month.

As shown in Figure 8.3, the HR periodic snapshot consists of a fairly ordinary

looking fact table with three dimensions: month, employee transaction, and

organization. The month dimension table contains the usual descriptors for

the corporate calendar at the month grain. The employee transaction key in a

fact table row is the employee transaction key that was effective on the last day

of the given reporting month. This guarantees that the month-end report is a

correct depiction of all the employee profiles. The organization dimension

contains a description of the organization to which the employee belongs at

the close of the relevant month.

Audit Dimension

Keyword Outrigger Dimension

The skills group identifies a given set of skills keywords. All IT employees

who are proficient in Oracle, Unix, and SQL would be assigned the same skills

group key. In the skills group outrigger, there would be three rows for this particular

group, one for each of the associated keyword skills (Oracle, Unix, and

SQL). In this case, just two attributes are associated with each skill, description

and category, so we include these attributes in the outrigger directly.

Survey Questionnaire Data

In order to analyze questionnaire data, we create a fact table with one row for

each question on a respondent’s survey, as illustrated in Figure 8.7. There

would be two role-playing employee dimensions in the schema corresponding

to the responding employee and the reviewed employee. The survey dimension

consists of descriptors about the survey instrument. The question dimension

would provide the question and its categorization. Presumably, the same

question is asked on multiple surveys. The survey and question dimensions

can be useful handles for searching for specific topics in a broad database of

questionnaires. The response dimension contains the responses and perhaps

categories of responses, such as favorable or hostile