Case Study

Streamlining HR Analytics Through Data Engineering and Automation

DataCactus streamlined HR analytics for the British Heart Foundation by automating data extraction from BambooHR and PayScale, transforming manual tasks into efficient daily updates. Our advanced data modelling and role-based security enhancements enabled clearer, timely insights and simplified secure report distribution, empowering the charity to make strategic, data-driven decisions.

Background

Client: British Heart Foundation, a leading charitable organization based in the UK
Industry: Non-profit/Social Services
Challenge: Manual HR data extraction processes and inefficient data model limiting analytics capabilities

Challenge

British Heart Foundation had already taken significant steps towards becoming a data-driven organization by implementing Power BI for reporting and analytics. However, they faced several challenges that limited their ability to fully leverage their HR data:
• Bi-weekly manual data extractions from BambooHR and PayScale systems were consuming valuable staff time
• The repetitive nature of data collection was diverting resources from more strategic activities
• Their existing data model had constraints that limited relationship-building capabilities
• Report sharing was a manual process with access controls managed through basic filtering methods
• Data refreshes were only happening every two weeks, limiting the timeliness of insights
The organization needed a solution that would automate their data extraction processes, improve their data model architecture, and implement proper security controls while maintaining data integrity across systems.

Solution

DataCactus designed a comprehensive data engineering and automation solution to transform British Heart Foundation's HR data management processes. The solution included:

1.Automated Data Extraction Pipelines:
◦ Development of an API-based data pipeline for BambooHR to extract five key HR data tables
◦ Implementation of Robotic Process Automation (RPA) for PayScale that simulates user interactions to extract payroll data efficiently
◦ Error handling and logging mechanisms to ensure reliability

2.Advanced Data Modeling:
◦ Transition from a flat data structure to multiple star schemas
◦ Creation of dimension tables (Employees, Departments, Generations, Genders, Employment Types) and fact tables (Payroll, Leave, Turnover, Vacancies)
◦ Optimization of relationships to improve query performance and resolve ambiguities
◦ Design for future scalability and integration of additional data sources

3.Row-Level Security Implementation:
◦ Configuration of role-based security controls in Power BI
◦ Dynamic access restrictions based on organizational hierarchy
◦ Simplified report distribution while maintaining data governance

Implementation

The implementation followed a structured approach:

1.System Analysis: Thorough assessment of both HR systems to establish the optimal integration strategy for each

2.Data Pipeline Development: Creation of automated extraction processes using API integration for BambooHR and custom RPA solution for PayScale

3.Data Model Redesign: Restructuring the existing data into a more efficient and scalable star schema architecture

4.Security Configuration: Implementation of row-level security based on organizational roles and responsibilities

5.Knowledge Transfer: Documentation and training sessions to ensure internal capability building
The entire project was completed in just 16 days of development work, demonstrating the efficiency of DataCactus's approach and the power of modern data integration tools.

"DataCactus transformed our HR data processes with their efficient automation and data modeling solution. What previously took our team hours of manual work every two weeks now happens automatically each day. The new data model has made our reports more intuitive and easier to build, while the row-level security implementation has significantly simplified how we share insights securely across the organization. The project was delivered quickly and has made a real difference to how we access and use our HR data."

Head of HR Analytics, British Heart Foundation