HR Employee Performance

High Level Design

Overview

Measuring employee performance is a very important aspect of HR functions. The data that has been used is about 4000+ employees of a company and the metrics are captured/calculated for the whole year of 2017. So, we could get an overall impression of the performance of the employees by analyzing three main metric types – Absenteeism, Overtime Hours per week and Quarterly Performance Rating.

Goals

Provide a high-level approach to analyze employees’ performance based on their in-time and out-time as well as the quarterly performance ratings given by their direct manager. Also, finding if there is a medium/strong correlation between any of the metrics and slice the data by different categories and its members. This would give the HR team an idea to understand what the pattern of employees’ behavior is.

Objectives

  • Discover the Performance related KPIs with the data we have about the 4000+ employees and the three types we had decided to analyze on – Absenteeism, Overtime Hours per Week and Performance rating
  • List the two important in the front landing page per KPI type which would give useful insight about that KPI type, which would further drill into the details of that KPI type.
  • Discover what is the correlation between each of the KPI type against another – which would give us three correlation pairs.
  • Find out which member in each category (for ex: Age Group is the category) has the maximum correlation and check if that is atleast a medium level of correlation.
  • For each KPI type, drill into the details by analyzing how the pattern is, with each category to drive insights about that one KPI type independently.  
  • The ability to filter the entire analysis by the Department, Job role, Gender and Age bucket of the employees.

KPIs Architecture

Objectives

KPIs

Measures

Data Source

Overall – Individual analysis of each KPI Type

 

Average Absenteeism %

 

AVG([EmployeeID] , ([Total PTO])/([# of unique Days in Date]-12))

Fact Emp Time, Dim Employee General

% Employees with 18+ PTOs

 

sum([EmployeeID],if(( [Total PTO] ,all([Days in Date] ))>=18,1,0)) / count([EmployeeID])

Fact Emp Time, Dim Employee General

Average Overtime Hours per week

(AVG([Weeks in DateAssociated], SUM([Total OvertimeHours])))

Fact Weekly Emp OverTime, Dim Date

% Employees working overtime per week

 

(AVG( [Weeks in DateAssociated] , ([# of unique EmployeeID], [OvertimeHours])) / ([# of unique EmployeeID]))

Fact Weekly Emp OverTime, Dim Date, Dim Employee General

Average Performance Rating

AVG([EmployeeID], (SUM([Total Overall Rating])/4) )

Fact Q Performance, Dim Employee General

Employees with 6+ Rating – All Quarters

 

sum([EmployeeID],if(([Min Overall Rating],all([Evaluation Period]))>=6,1,0)) / Count([EmployeeID])

Fact Q Performance, Dim Employee General

Correlation Pair Analysis

 

Absenteeism vs Performance

CASE

WHEN

correl([EmployeeID],[Total PerformanceRating],[Total PTO]) < 0 

THEN

-1 * correl([EmployeeID], [Total PerformanceRating], [Total PTO])

ELSE

correl([EmployeeID],[Total PerformanceRating],[Total PTO])

END

Fact Q Performance, Dim Employee General, Fact Emp Time

Absenteeism vs Weekly OT Hours

 

CASE

WHEN

correl([EmployeeID],[Average OvertimeHours],[Total PTO]) < 0

THEN

-1*correl([EmployeeID], [Average OvertimeHours], [Total PTO])

ELSE

correl([EmployeeID],[Average OvertimeHours],[Total PTO])

END

Dim Employee General, Fact Weekly Overtime, Fact Emp Time

Weekly OT Hours vs Performance

CASE

WHEN

correl([EmployeeID],[Total PerformanceRating],[Average OvertimeHours]) <0

THEN

-1*correl([EmployeeID], [Total PerformanceRating], [Average OvertimeHours])

ELSE

correl([EmployeeID],[Total PerformanceRating],[Average OvertimeHours])

END

Fact Q Performance, Dim Employee General, Fact Weekly Overtime

Detailed – Individual analysis – Absenteeism

Average Absenteeism % Male/Female

(AVG([EmployeeID] , ([Total PTO])/([# of unique Days in Date]-12)),[Gender])

Fact Emp Time, Dim Employee General

Average Absenteeism # Male/Female

(AVG([EmployeeID] , ([Total PTO])),[Gender])

Fact Emp Time, Dim Employee General

Average Absenteeism Days

AVG([EmployeeID] , ([Total PTO]))

Fact Emp Time, Dim Employee General

Number of Employees

count([Employee ID])

Dim Employee General

Employee % with 18+ PTOs

[Employee Count of 18+ PTOS]/[Number of Employees]

Fact Emp Time, Dim Employee General

Employees with 18+ PTOs

sum([EmployeeID],if(( [Total PTO] ,all([Days in Date] ))>=18,1,0))

Fact Emp Time, Dim Employee General

Detailed – Individual analysis – Overtime Hours Weekly

Average % Employees working overtime Male/Female

(AVG( [Weeks in DateAssociated] , ([# of unique EmployeeID], [OvertimeHours]) / ([# of unique EmployeeID])),[Gender])

Fact Weekly Emp Overtime, Dim Employee General

Average # Employees working overtime Male/Female

((AVG([Weeks in DateAssociated], SUM([Total OvertimeHours]))),[Gender])

Fact Weekly Emp Overtime, Dim Employee General

Employees Working Overtime per week

AVG( [Weeks in DateAssociated] , ([# of unique EmployeeID], [OvertimeHours]))

Fact Weekly Emp Overtime, Dim Employee General, Dim Dates

Average Overtime Hours per week per Employee worked

(AVG([Weeks in DateAssociated], SUM([Total OvertimeHours]))) / AVG( [Weeks in DateAssociated] , ([# of unique EmployeeID], [OvertimeHours]))

Fact Weekly Emp Overtime, Dim Employee General, Dim Dates

Average Overtime Hours per Week

(AVG([Weeks in DateAssociated], SUM([Total OvertimeHours]))) / AVG( [Weeks in DateAssociated] , ([# of unique EmployeeID], [OvertimeHours]))

Fact Weekly Emp Overtime, Dim Employee General, Dim Dates

Detailed – Individual analysis – Performance Rating

Average Performance Rating  Male/Female

(AVG([EmployeeID], (SUM([Total Overall Rating])/4) ),[Gender1])

Fact Q Performance, Dim Employee General

% Employees with 6+ Rating Male/Female

(sum([EmployeeID],if(([Min Overall Rating],all([Evaluation Period]),[Gender1])>=6,1,0)) )/ (Count([EmployeeID]),[Gender1])

Fact Q Performance, Dim Employee General

Employee Count with 6+ Rating

sum([EmployeeID],if(([Min Overall Rating],all([Evaluation Period]))>=6,1,0))

 

Employee % with 6+ Rating

sum([EmployeeID],if(([Min Overall Rating],all([Evaluation Period]))>=6,1,0)) / Count([EmployeeID])

 

Entities Relationship Diagram

Employee Performance Dashboard

Plugins & Scripts

  1. Blox
  2. The Measure Changer
  3. Distribute Widgets equally in a row

Implementation
Kit

The following resources will enable you to design your dashboard and data model with sample data and then apply it to your own data. Note that you will need to have a previously installed version of Sisense (you can use the free trial version if you’re not a customer).

Sample data and dashboard examples (direct download)

Documentation