Introduction
In the modern business landscape, data-driven decision-making plays a crucial role in optimizing human resources and improving employee performance. This project focuses on designing a Data Warehouse (DWH) schema for employee performance analytics using the star schema approach. The goal is to enable HR professionals and business leaders to analyze employee performance across various dimensions such as department, education level, and gender to derive meaningful insights.
Objectives of the Project
The primary objectives of this data warehouse project are:
- Designing an Entity-Relationship Diagram (ERD) to define the relationships between employee data and performance metrics.
- Implementing a star schema that optimizes query performance and enables analytical reporting.
- Developing Data Marts that facilitate reporting on performance across key employee attributes.
- Providing sample queries for real-world HR analytics use cases.
Data Warehouse Schema Design
The star schema architecture is adopted for this data warehouse, consisting of a fact table and three dimension tables:
Fact Table –
fact_employee_performance- Stores key performance metrics of employees.
- Contains fields such as
performance_id,employee_id,performance_score, andperformance_date.
Dimension Tables
dim_employee: Contains employee details such asemployee_id,first_name,last_name,birth_date,hire_date,gender,department_id, andeducation_id.dim_department: Stores department-related details (department_id,department_name).dim_education: Captures employee education levels (education_id,education_level).
These tables are linked via foreign keys, allowing for comprehensive performance analysis by department, education level, and gender.
Key Insights from the Data Mart Analysis
The Data Marts created in this project help analyze employee performance across different attributes:
1. Performance by Department
- Top Performers: Employees in Administration (53.89 avg score) and Operations (51.32 avg score) show consistently high performance.
- Mid-Level Performers: Employees in Finance, Marketing, and IT maintain stable performance around 50.
- Low Performers: R&D (46.75 avg), Legal, and Customer Service departments exhibit lower scores, indicating possible performance gaps.
Recommendation: Focus on training and restructuring underperforming departments while leveraging high-performing teams as benchmarks.
2. Performance by Education Level
- Associate Degree holders have the highest average performance score (51.57).
- Master’s Degree holders show strong individual potential, with a max score of 99.99.
- High School graduates (49.68 avg) outperform Bachelor’s degree holders (49.22 avg).
- PhD holders have the lowest average performance (46.95), possibly due to role mismatches.
Recommendation: Investigate role alignment for PhD holders, while providing targeted training for Bachelor’s and High School graduates.
3. Performance by Gender
- Males (49.57 avg) perform slightly better than females (49.33 avg), though differences are minor.
- Males contribute to 729 performance records, while females have 271, suggesting possible gender-based workforce distribution differences.
- Females show less performance variance, implying more consistent performance levels.
Recommendation: Encourage equal growth opportunities and analyze participation disparities to ensure fair representation.
4. Performance Trends Over Time
- Performance peaks in October 2020 (54.86 avg) but drops in April 2020 (42.62 avg), suggesting seasonal impacts.
- The number of performance records is increasing, indicating workforce expansion or improved tracking.
Recommendation: Focus on improving performance in low-scoring months by adjusting workload management and replicating strategies from peak months.
Conclusion
This project successfully designs a scalable and efficient Data Warehouse schema for employee performance analytics. The star schema structure enables fast queries, simplified reporting, and scalability for future enhancements. The analysis provides actionable insights for HR teams to improve workforce efficiency, optimize hiring strategies, and develop targeted employee training programs.
Query & Documentation
🔗 Find the complete query and documentation on my GitHub: