BI for a US-based Machinery Maintenance Entity with 25K+ Employees

Customer

The Customer is a US-based machinery maintenance entity with 25,000+ employees.

Challenge

With the goal to improve HR and performance management, the Customer wanted a BI solution that was to:

  • aggregate employees’ personal and work-related information
  • analyze the headcount, FTEs and hours paid
  • track employees’ working time
  • monitor task performance

Solution

In the course of the project, ScienceSoft’s BI consulting team delivered the following:

  • Data warehouse on Microsoft SQL Server
  • ETL using Python
  • Data cleaning
  • Analytical cube with 18 dimensions and 26 measures
  • Integrated analytical reports based on Microsoft Power BI

As the BI system was to bring together data from 4 sources, it was tuned to discover any discrepancies (such as typos, mismatches, different abbreviations, etc.) between them.

Since the Customer wanted to improve HR management processes, ScienceSoft developed specific reports with the following information:

  • Employee’s first and last name, as well as ID
  • Contact information (such as address, phone number, etc.)
  • Job position
  • Contract type
  • Hire date
  • Contract termination date
  • Full-time equivalents
  • Hourly rate per job position
  • Paid hours
  • Not paid hours

The reports allowed the Customer to watch over contract termination dates and organize relevant HR procedures on time. Additionally, the Customer got an overview of personnel costs and was able to use this information for budgeting.

To allow the Customer to control the headcount, ScienceSoft implemented the dashboards that show:

  • The number of employees per job position
  • Actual headcount

Two modules were designed for monitoring employees’ performance: the one was to track employees’ working time and the other their task fulfillment.

Results

The delivered BI solution allowed the Customer to make HR and performance management more controllable thanks to monitoring task fulfillment, tracking employees’ working time and watching over important milestones (for example, a contract termination date). Besides, the system ensured data integrity by spotting discrepancies in it.

Technologies and Tools

Microsoft SQL Server (DWH), Python (ETL), Microsoft Power BI (Reporting).