1.1 Purpose
The HR Analytics Dashboard provides insights into key HR metrics, including employee performance, compensation, engagement, turnover, and training. The dashboard aims to help HR professionals and management make data-driven decisions to optimize workforce management.
1.2 Scope
The dashboard is designed for HR executives, managers, and decision-makers to monitor HR performance across various departments, locations, and employment durations. It supports trend analysis, benchmarking, and strategic planning.
1.3 Audience
- HR Managers
- Senior Leadership Team
- Department Heads
- Business Analysts
2. Dashboard Features & Functionalities
2.1 Executive Summary
- Overview of key HR metrics
- Employee retention rate trends
- Satisfaction and engagement scores
2.2 Performance Analytics
- Performance Rating by Employment Duration
- Performance Rating by Department
- Trend Analysis of Performance Ratings
- Top and Bottom Performers
- Correlation between Exits and Performance Ratings
2.3 Compensation Analysis
- Average Salary by Department
- Compensation Distribution
- Bonus to Salary Ratio by Department
- Top Job Titles by Compensation
- Retention Rate vs Compensation
- Average Cost Per Hire by Department
2.4 Employee Turnover
- Exit Trends by Year
- Top Reasons for Employee Exits
- Retention Rate by Employment Duration
- Employee Turnover Rate by Department
2.5 Engagement & Training
- Average Satisfaction and Engagement Scores
- Training Completion Rates by Year
- Job Titles with Highest Training Hours
- Training Hours vs Engagement Score
2.6 Employee Details & Demographics
- Workforce Gender Distribution
- Workforce by Age Group
- Workforce by Employment Duration
- Workforce by Education Level
- Workforce by Office Location
3. Data Sources & Integration
- HRMS (Human Resource Management System)
- Payroll System
- Employee Satisfaction Surveys
- Training Management System
- Attendance & Leave Management System
4. User Requirements
4.1 Functional Requirements
- Ability to filter data by year, location, department, employment duration
- Interactive visualizations and graphs for better insights
- Exportable reports for executive summaries and presentations
- Automated data refresh to ensure up-to-date insights
4.2 Non-Functional Requirements
- Performance: Ensure the dashboard loads within 5 seconds for optimal user experience
- Security: Implement role-based access control for data confidentiality
- Scalability: Capable of handling increasing employee data as the company grows
5. Key Performance Indicators (KPIs)
- Employee Retention Rate
- Average Performance Rating
- Compensation Growth Trends
- Employee Turnover Rate
- Satisfaction & Engagement Scores
- Training Completion Rate
6. Assumptions & Constraints
- Data must be regularly updated from connected HR systems
- User access will be managed based on roles and permissions
- Some historical data might not be available for trend analysis
7. Project Completion Method
7.1 SQL Queries
- Extract HR data from multiple sources such as HRMS, payroll, and attendance systems.
- Transform and clean data for accuracy and consistency.
- Example SQL Query to retrieve employee details:
SELECT EmployeeID, Name, Department, Title, Salary, RetentionRate FROM EmployeeData WHERE ActiveStatus = 'Yes'; - Queries to calculate key metrics such as retention rate, turnover, and average salary.
7.2 Power Query
- Connect Power BI to SQL databases and other data sources.
- Perform ETL (Extract, Transform, Load) operations such as:
- Data cleansing (removing duplicates, handling missing values)
- Data merging from different sources
- Creating calculated columns
- Example Power Query transformation:
let Source = Sql.Database("ServerName", "DatabaseName"), EmployeeTable = Source{[Schema="dbo", Item="EmployeeData"]}[Data], FilteredRows = Table.SelectRows(EmployeeTable, each [ActiveStatus] = "Yes") in FilteredRows
7.3 DAX (Data Analysis Expressions)
- Create calculated measures and columns for better analysis.
- Example DAX formula for calculating Employee Retention Rate:
Retention Rate = DIVIDE( COUNT(EmployeeData[EmployeeID]), COUNT(EmployeeData[EmployeeID]) + COUNT(EmployeeExits[EmployeeID]) ) - DAX measures to analyze trends, performance ratings, and compensation.
8. Conclusion
This HR Analytics Dashboard provides a comprehensive view of HR performance, helping management make informed decisions to improve employee satisfaction, retention, and overall business efficiency. The dashboard will continue evolving based on business needs and user feedback.
sample data files for the HR Analytics Dashboard, including:
- Employee Data (CSV) – Basic employee details, department, salary, and performance rating.
- Employee Attendance (CSV) – Absenteeism, sick leaves, and engagement scores.
- Employee Compensation (CSV) – Salary, bonuses, and retention rates.
- Employee Turnover (CSV) – Employees who exited, reasons for leaving, and turnover trends.
- Training & Engagement (CSV) – Training hours, completion rates, and engagement scores.
Developed by Muhammad Zahid
