Overview
A 400-person organisation was losing talent faster than it could hire, but the HR director had no visibility into where attrition was concentrated, which roles were at risk, or whether compensation was competitive. All employee data lived in a payroll system with no reporting layer, and the only "analysis" was a quarterly headcount figure in a spreadsheet.
This engagement covered the full stack: extracting HR data from the HRIS system, building a star-schema HR data mart in SQL Server, applying SCD Type 2 to track employee history accurately, and delivering a Power BI platform with three distinct audiences — HR operations, department managers, and senior leadership — each with appropriately scoped data access.
Key Deliverables
- Attrition dashboard with voluntary/involuntary split, tenure cohort analysis, and 12-month rolling trend
- Headcount tracker showing movements (new hires, transfers, terminations) by department and month
- Salary benchmarking report comparing internal bands to role-level market ranges
- Attrition risk scoring model using tenure, performance rating, and salary gap as inputs
- Star-schema HR data mart in SQL Server with SCD Type 2 employee dimension
- RLS ensuring department managers see only their direct reports; HR sees all
Challenges & Solutions
Employee data is inherently sensitive and the client needed strict access controls without running separate reports. The solution was a combination of Power BI RLS using dynamic security (manager hierarchy from Active Directory) and a DAX pattern that blurs salary figures for anyone below HR-admin role — showing ranges rather than exact values for non-HR viewers.
Historical headcount required SCD Type 2 in the employee dimension to track role changes, department transfers, and salary adjustments over time. Without this, any point-in-time headcount or attrition calculation would be inaccurate. Building the slowly changing dimension logic in SQL Server before loading into Power BI kept the DAX clean and the model performant.