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

Plugins & Scripts

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