We've created each of our previous perspectives with the objective of becoming a data-driven business. Robert Kaplan and David Norton considered the measurements in the learning and growth of employees as the infrastructure to enable ambitious objectives in the financial, customer, and internal-business process perspectives. We need to invest resources in our human capital so that they are capable of using data to help themselves and work at their optimal level. In this chapter, our goal is to learn more about our employees and help them be more effective.
First, we are interested in which factors make our team more productive. We use data from our Human Resource Management System (HRMS) to calculate metrics, such as headcount, salary, vacation days, sick days, and turnover by pertinent dimensions, such as job function, functional area, and demographics. Then, we compare these measurements with financial metrics, such as sales and gross profit.
Our organization's success is based on how well we develop our human talent. Therefore, we use additional metrics to measure performance and training. Along with our enterprise perspective, we also empower our employees to measure themselves. We look to achieve mutual success in helping them establish their own goals and measure their own performance with their own personal perspective.
We will cover the following topics as we develop our QlikView human resources perspective:
- HR data model
- Personnel productivity
- Personal behavior analysis
Let's begin by reviewing the HR data model that we will create from our HRMS system.
The HR data model is a transactional fact table with discrete events. This includes the employee-related events along with a few financial events that help us measure productivity. We record the following events in this model:
- Employee payroll
- Employee absences
- Employee training
- Employee hiring and dismissals
- General Journal (GJ) entries related to sales, costs, and expenses
Here is a representation of this model:
We combine these events into one fact table and use the same type of master calendar as in Chapter 2, Sales Perspective. The data model includes a new dimension table called Employees. Let's review the 7Ws of our HR data model.
|
Dimensions | ||
|---|---|---|
|
7Ws |
Fields |
Comments |
|
Who |
|
This is the focus of our HR data model. We include various attributes that are related to their role in the company and general demographic information here. |
|
When |
|
This is where we include a single master calendar that describes the exact date of an event. |
|
Where |
|
We cannot usually measure sales by employee unless they are a sales representative. So, along with describing an employee's geographical location, we also use |
|
What |
|
We store the event type of each fact table row in order to determine what event it represents. One row could record an absence, a training day, a hiring, a dismissal, or a paid salary. |
|
What |
|
This is a dimension that we first saw Chapter 3, Financial Perspective. |
|
Why |
|
This is where we store why an employee was absent and whether it was due to sickness, vacation, or unexcused. |
|
Metrics | ||
|
7Ws |
Fields |
Comments |
|
How Many |
|
Several events, such as vacation, training, hiring, and dismissals, are measurements only because of the fact that they occurred on a given date. Therefore, we create a field that measures the event using the number, |
|
How Much |
|
We measure how much each employee earns on a monthly basis. We also define our headcount as the number of employees that receive pay. We create a field called |
|
How Much |
|
We measure this in the same way as we did in Chapter 3, Financial Perspective. |
Over time, employees will learn and grow and they will earn promotions or transfer to different departments in the company. Some HRMS systems, such as SAP HR, contain tables that conserve history and tell us when an employee has changed their job position or department. Other systems may just contain an employee's current information and not save any record of their past job positions or departments.
Descriptive information that may change over time is called Slowly Changing Dimensions (SCD). Other examples include reassigning customers to new sales representatives, rearranging customers groups, or rearranging product groups. We need to understand the effect that SCDs can have on our analysis and how business users expect to visualize this data. The following are the most common types of SCD's (Kimball and Ross 2013):
|
SCD Type |
SCD Description |
|---|---|
|
Type 0: Retain Original |
This dimension attribute value never changes. |
|
Type 1: Overwrite |
This is when we erase history and overwrite the old attribute value with the new one in the dimension table. |
|
Type 2: Add New Row |
This is when we conserve history and store the new attribute value in a new row in the dimension table. |
Often, users need SCD types 1 and 2 in order to answer all of their questions. For example, we may want to assess each office's current employee knowledge. So, we use SCD Type 1 to analyze the amount of training taken by employees regardless of whether some of this training was taken while assigned to other offices. On the other hand, we may want to analyze which office is investing more in training. In this case, we would need to use SCD Type 2 so that we can take into account where employees were working when they were trained.
Therefore, it is worthwhile to allow advanced users the option to compare both types in one application. We'll store Type 1 dimension attributes in fields that contain the word "Current" (for example, Employee Current Job Title) and Type 2 dimension attributes in fields that contain the word "Historical" (for example, Employee Historical Job Title).
We can easily create a SCD Type 1 field by assigning the current HRMS attribute value to the appropriate field in the script. The creation of SCD Type 2 is more complicated. We have to link the value in the _KEY_Date field in the Facts table to the attribute value's valid date interval defined by the [Valid Start Date] and [Valid End Date] fields in the Employees dimension table. We use the same intervalmatch() function that we used in Chapter 4, Marketing Perspective, to create the relationship between the Facts and Employees tables in our HR data model. For more information on how to handle SCD in a QlikView data model, read Henric Cronström's blog post on the subject at https://community.qlik.com/blogs/qlikviewdesignblog/2013/06/03/slowly-changing-dimensions.
Tip
If the ERP or HRMS doesn't save the dates that a dimension attribute is valid and overwrites the values, we can use QVDs to conserve an attribute's history. The start and end dates are created in the script after we detect that a change has been made. A quick way to discover attribute value changes is using the hash function described in Barry Harmsen's blog (http://www.qlikfix.com/2014/03/11/hash-functions-collisions/).