INDUSTRY:

Transportation

SERVICES:

Bus, Light Rail, Shuttle service to the public

COMPANY SIZE:

900+ Employees

OPERATING BUDGET:

$147mm (2015)

HEADQUARTERS:

Sacramento, CA

PROJECT OUTCOME:

• Improved data quality
• Reduced cycle time to generate metrics each month
• Improved analyst efficiency and morale

OUR CLIENT’S CHALLENGE

The Sacramento Regional Transit District (RT) operates 69 bus routes and 43 miles of light rail covering a 418 square-mile service area. Buses and light rail run 365 days a year using 76 light rail vehicles, 205 buses and 23 shuttle vans. Annual ridership has steadily increased on both the bus and light rail systems from 14 million passengers in 1987 to over 26.4 million passengers in FY 2015.RT has complex staffing business rules for route drivers related to seniority, time-off requirements, consecutive workday restrictions, and other constraints. These rules are required by state transit regulations, state employment

RT has complex staffing business rules for route drivers related to seniority, time-off requirements, consecutive workday restrictions, and other constraints. These rules are required by state transit regulations, state employment law and labor contracts which RT operates under.

As RT’s ridership and number of routes increased, RT’s Route Coordinators were finding it impossible to assign drivers in a manner which complied with all of the regulations and labor contracts. Being out of compliance meant bad publicity, impact to labor relations, and potential fines.

TEN2ELEVEN’S APPROACH

Ten2Eleven was asked to evaluate the existing HR and route systems and to deliver an analytics solution to help Route Coordinators assign drivers optimally. Ten2Eleven’s Analysts profiled the existing data and interviewed RT staff in order to fully understand the operational requirements that RT operates under.

Ten2Eleven’s Analysts then created a desktop-based application which would import data from HR and route systems and provide decision support analytics for Route Coordinators to use in assigning drivers. Using rapid development principles, Ten2Eleven solicited feedback from RT staff and refined the application over the course of several incremental releases.

Ten2Eleven provided knowledge transfer and training to RT staff to ensure the solution was maintainable by RT after Ten2Eleven completed the project.

THE SOLUTION

Ten2Eleven’s data experts designed and deployed several complementary process and system changes to support RT:

• A secure web-enabled application based on Microsoft SQL Server Reporting Services (SSRS)
• Extract, Transform and Load (ETL) processes to bring data from RT’s SAP route scheduling system into the SQL Server database
• Procedures written in SQL to identify potential route assignment conflicts and to prioritize available route operators using complex union rules and guidelines
• Access to real-time data to manage absenteeism and reassignment
• Route reports to allow RT dispatchers to quickly view potential route assignments in a visually-oriented format