Data-Driven Facility Management Operations
A UK-based facility management company with a £25 million turnover sought advanced data solutions to streamline operations. Although they effectively used Joblogic software for field management, their overtime payment system relied on paper timesheets. This manual process was labour intensive, time consuming, and prone to errors. The company required an efficient, automated solution to address these issues.
Project Overview and Objectives
Our team was brought in to design an automated data solution aimed at simplifying the overtime calculation process. The goal was to extract data directly from Joblogic and automate the calculation of overtime payments, reducing manual intervention and ensuring accuracy.
Identifying the Core Challenges
The initial requirement focused on using Power BI to calculate overtime based on engineers’ start and end times. However, after reviewing the data, it became clear that the problem was more intricate than anticipated. The company needed to account for various employment contract terms, these included exceptions caused by contractual changes and the integration of TUPE engineers. The contracts specified overtime rates based on factors included daily and weekly hours, travel time and premium work periods.
The primary data sources included Joblogic reports, stored in Azure table storage, and internal Excel spreadsheets containing payroll and on-call rota information. The challenge was to integrate these diverse data streams and handle the complex logic required for accurate overtime calculations.
Methodology: Data Ingestion, Architecture, Reporting, and Automation
1. Data Ingestion
We began by establishing the initial business requirements, which included factoring in non-productive time, on-call hours, and many employment contracts. These contracts defined overtime based on factors such as premium work periods, weekly hours, and excess travel time.
A comprehensive data review highlighted inconsistency, such as jobs left open, overlapping jobs, and changes in team assignments. Additionally, the system didn’t account for large gaps in work hours when engineers were on call. A workshop was held to align the requirements with available data, resulting in a set of rules for handling errors, exclusions, and complex calculations.
2. Data Architecture
The data warehouse was built using Medallion architecture, with separate layers for optimized processing. The bronze layer handled the initial data load with daily refreshes from the source systems. Validation steps were added to filter out incorrect entries, while staging tables processed and cleaned the data.
The silver layer stored validated tables, optimized for performance using dimension tables for employee data and fact tables for overtime calculations. The gold layer aggregated data for efficient reporting and analysis.
3. Reporting
Power BI was used to visualize, distribute, and secure the processed data. A data model built from the gold layer allowed for detailed reporting on overtime by individual, team, and time period. With drill-throughs and slicers, users could navigate the data for deeper insights and quickly identify outliers. Additionally, a management report highlighted strategic metrics, such as overtime levels, travel percentages by team, and underutilized time.
4. Automation
The entire process was fully automated to run on a daily schedule. Automatic notifications were configured to alert stakeholders to any process failures, ensuring smooth and uninterrupted operations.
Results: Improved Efficiency and Employee Satisfaction
The implementation of our automated solution successfully resolved the company’s challenges in calculating overtime. By automating the process, labor costs were reduced, and payroll processing became more consistent and accurate. As a result, staff satisfaction increased significantly, as employees experienced timely and error-free payments. Additionally, the new system improved overall operational efficiency, leading to enhanced performance across teams.
Conclusion: Driving Operational Efficiency with Data-Driven Facility Operations
This case study highlights how advanced data solutions can transform facility management operations by improving data ingestion, validation, and visualization processes. The successful implementation of automated overtime calculations and seamless data integration not only resolved immediate operational issues but also set the foundation for future strategic growth.
The tools and methodologies used in this project, including Power BI, Azure SQL, and Medallion architecture, delivered value across multiple business functions. This solution demonstrates how businesses can leverage data to improve efficiency, enhance decision-making, and optimize resource management in the long term.
Responsive Analytix specializes in data analytics and business intelligence solutions tailored for small and medium-sized enterprises (SMEs) in the UK. We offer comprehensive data services including data warehouse solutions, report automation, business intelligence consultancy, and cloud-based data solutions. Our expert team transforms raw data into actionable insights, helping businesses make informed decisions, optimize operations, and drive growth. Whether you need to enhance marketing strategies, improve customer engagement, or streamline processes, we provide the analytics tools and expertise to empower your business in today’s data-driven landscape.
