Route Optimization, Reporting Automation, and MDM for Transportation Client

Background and Challenge Story

A large transportation client wanted to modernize its processes, supporting systems, and reporting platform and explore how an Enterprise Data platform could improve their information management capabilities.

Their existing mixed technology environment and departmental structure resulted in information and business silos, which made it difficult to cross-link data across departments and extract insights from key data domains.

Zelusit’s Solutions

Zelusit was brought in to develop and implement solutions for operational reporting, route analysis and optimization, and a Master Data Management solution for employee and asset domains to support vehicle maintenance.

• Enterprise Operational Reporting Automation

Prior to the project, on a daily basis, the client would manually generate service levels reports capturing delay and out-of-service incidents against target KPIs, for current stations and routes. A daily customer service report was also published on the organization’s website. The reports had a standard template, and the client wanted to automate the production of these reports.

The client also had an internal work order system to support vehicle maintenance management, which was used to monitor the performance of scheduled maintenance and safety activities (internal and external cleaning, vehicle inspection, safety inspection, etc.) against a set target. Maintenance reports would also go out to the service yards, to help them keep track of the vehicles that needed to be serviced, and maintenance specifics, such as parts replacement for a particular vehicle.

Zelusit conducted a foundational BI pilot for a set number of vehicle routes, which could then be scaled to the enterprise level to provide a holistic picture of the organization’s transportation operation. We started by creating a BI data model for the in-scope data.

Image: Business Intelligence Data Model

The team then created processes to generate 5 different BI reports for service levels on a daily, scheduled basis, to the expected external facing standards. With pre-built calculations, input prompts and drill-down functionality, these reports provided the necessary levels of automation the client was looking for, to support multiple variants.

For vehicle maintenance, Zelusit built 7 distinct BI reports aligned with KPIs for maintenance of different vehicle types. This included scheduled reports with graphical representations of percentage of vehicles that had undergone exterior wash, interior cleaning, standard and safety inspections. Our experts also created two delay and out-of-service incident reports, which would track delays by time (delays of over 3 minutes, 5 minutes, and 20 minutes), allocate reason codes to delay minutes, and report the number of incidents per financial period.

• Route Analysis and Optimization

The client also wanted to analyze the schedule adherence of their vehicles, optimize their routes to reduce delays, minimize headway and bunching, and make their transportation service more reliable. Since the vehicles could not always stick to a prescribed schedule, owing to external factors, like traffic and weather conditions, construction, and other unavoidable delays, actual sensor/GPS data from vehicles was needed to track their location and determine their distance and time of arrival to a posted schedule at the stop. The objective was to identify repeated patterns (due to certain conditions, or at specific times of the day), using historical and external data, to predict delays and notify the right parties to adjust schedules, deploy, or recall buses accordingly, to improve the rider experience.

Zelusit implemented a pilot solution for 2 bus routes over a 6-month timeframe and interlaced weather, traffic, and construction reports with the sensor data for the vehicles. The routes were overlayed on a map of the city which depicted the exact locations of the bus stops, routes, and points of delay.

Our experts tracked three KPIs – average headway at stops (time between two buses), vehicle bunching (multiple buses at the same stop at a time), and schedule adherence (bus arrival within +/- 1, +/- 2, and +/- 5 minutes of prescribed time). Zelusit’s experts built a solution that calculated the distance between GPS data from the buses and the GPS coordinates of the various bus stops to determine proximity to or arrival at the stop. The vehicle locations and headway were displayed on the map. They also compared the actual arrival time to the prescribed schedule for the stop, and then tracked how many buses arrived within 1-5 minutes of that schedule. This provided a view of both schedule adherence and bunching.

This solution not only allowed the client to optimize the schedule based on historical patterns, but also track and optimize routes in real-time, by, for instance, recalling a bus if there were too many on the route, or adding a bus mid-way to the route in case of delays. Since the reports were built against a visual map of the city, the client’s team could view the performance at a particular stop and could drill-down on a specific route to analyze bunching and schedule adherence during certain timeframes and conditions (E.g., during snowfall or peak morning hours).

• Employee Master

Our client wanted to develop and implement a Master Data Management solution to maintain ‘golden records’ of its employees and contractors. Zelusit’s experts first identified the data domain scope, system scope, and functional scope for the MDM solution through workshops with the client’s project team.

The MDM solution housed 4 key entities, including employee records, contact information, organizational units, and driver’s license details.

The Informatica Data Director (IDD) interface was used for the MDM Hub, and a Data Quality analysis was performed to aid in the development of the solution. Data cleansing was used to standardize and cleanse records of extraneous characters, and the record matching process allowed for records from separate sources to be merged into a single, consolidated record. We also established survivorship rules to define what actions should be taken when records contain conflicting information.

The MDM solution kept track of the employees’ (especially drivers and fleet operators) driver licenses and expiry dates and allowed the client to send reminders to the employees to renew their licenses. It helped the client manage their employee database and integrate all employee data into one repository.

Image: Master Data Management Solution

• POC for Asset Master

Zelusit also developed and implemented a small proof-of-concept for an MDM solution for vehicle parts and maintenance. As part of the POC, we created an Informatica MDM Hub with data for vehicle parts corresponding to each vehicle, parts stock in the warehouse, and vehicle maintenance and repair schedules.

This allowed the client to attribute every vehicle to its parts and, based on when a vehicle was due for maintenance, they could check which parts were available in the warehouse and which ones have to be ordered.

The POC could eventually be scaled to include all of the client’s fleets and vehicles to help them effectively manage part inventory and order fulfillment in alignment with maintenance schedules.

Impact

• Enhanced customer service
• Transport schedule optimization
• Manpower cost reduction
• Better fleet maintenance
• Golden employee records
• Increased ridership and profitability

The automation of reporting eliminated manual processes and enabled business users to perform analytics to make informed business decisions. With daily and weekly reports generated at the click of a button, the client was able to free up valuable time and resources, and instead allocate those to more strategic tasks.

The maintenance reports provided the client better visibility into maintenance schedules and performance vis-à-vis targets, which in turn would result in increased vehicle cleanliness and safety for riders. The client could also add other types of reports, as needed, in the future, to understand and track other aspects of maintenance. The Asset Master tied maintenance schedules and spare parts inventory together, ultimately reducing maintenance delays and out-of-service days for vehicles.

The route analytics and optimization pilot was a sound building block for overall optimization of the company’s transportation routes. It helped the client make changes to their schedules to service riders more efficiently, reduce wait times, prevent overcrowding, and ensure that the public transportation service operates as a well-oiled machine. By building trust in the service, the client would also be able to drive ridership and increase profitability.

The MDM hub not only consolidated employee data to create “golden records” of their staff but was also useful in keeping track of driver licenses and expiry dates, so that reminders could be sent out to drivers for license renewal.

Overall, the project was successful in modernizing the client’s systems, and consolidating data from different departments to provide previously undiscoverable insights. These insights helped them optimize resource utilization and offer better customer and employee experiences.

Looking to optimize your operations or reporting solutions? Schedule a discussion with Zelusit’s experts today!