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
Data Connectors
