Use Business Intelligence to Drive Savings

The South Coast British Columbia Transportation Authority (TransLink) is Metro Vancouver’s regional transportation authority and provides service to 21 municipalities situated on 1,243 miles of British Columbia’s southwestern mainland. The agency is the first North American transportation authority responsible for the planning, financing and managing of all public transit in addition to major regional roads and bridges.

With more than 1,500 buses covering 220 routes, 8,830 bus stops and 528 miles, and extensive rail services, TransLink operates within one of the largest transit service areas in the world.

With such a large transit network, measuring service efficiency is a primary concern. Easy access to delivered service data is necessary for analyzing the quality of operations, reporting to management and building efficient schedules.

For TransLink, the delivered service data was captured with the fleet management system. However, the agency needed a way to access data quickly and easily to compare it with the current schedule data to effectively analyze run times and to make service adjustments.

The agency’s subsidiary, Coast Mountain Bus Co. (CMBC), operates 200 of TransLink’s 220 routes. In 2012, CMBC’s service analysts were evaluating revenue service run times using a process which took up to 12 hours for each route. This consumed staff hours, limiting the opportunity to make service adjustments for all but the top priority routes. Getting this information quicker would enable further streamlining of service and subsequently reduce operating costs. The mission was simple: Create a high-performance data warehouse where the delivered service data could be “married” to the static schedule data for the purpose of analyzing variances and improving run-time efficiency.

In 2006, TransLink contracted Init, Innovations in Transportation, to install an intelligent transportation system on board its bus fleet. The implementation included a fleet management system, on-board computers, next-stop announcements, and statistics and reporting software. Real-time passenger information displays were later added to the project.

TransLink Project Manager Darla Jamieson set the goal of extracting and processing the agency’s critical data in a more proficient manner.

In October 2010, she prepared a business case for a run-time analysis project tying scheduled run-time service data with delivered service data to give useful insight on where to streamline service. Sponsored by Tom Fink, director of transit service design at CMBC, the business case justified the technology solution with Fink’s vision of achieving efficiencies and recognition of the potential of having timely actual run-time data for his staff to analyze.

Fink explained, “We needed to optimize our existing resources — operators, buses — by making the best use of our available data to improve operational efficiency.”

The steering committee unanimously backed the run-time analysis project and approved its start for January 2011. The project would be managed using the business technology services project management methodology.

Run-Time Analysis Plan

The goals of the run-time analysis project covered four main objectives:

  • Access and analyze the delivered service data faster
  • Remove idle times from the schedule
  • Reallocate/adjust service where needed
  • Tell a success story about the importance of the data

The targeted goals in the proposal aimed to increase the analysts’ processing capacity to 200 analyses per quarter by reducing the extraction to less than an hour per route.

The TransLink team began building a data warehouse where the service data — arrival and leave times at all stops, vehicle halts within deadheads — could be integrated with TransLink’s scheduling data, including trips, timing point segments and stops.

The project was conducted as a business intelligence project, not a traditional systems development project. One key difference between these types of projects is the focus of the requirements-gathering is on how the business uses, perceives and questions the data. Clear definitions and consistent terminology are crucial.

All current and future analytical requirements and integration points were studied, however, it was equally important to keep the eye on the prize: the run-time analysis process. This process was the baseline to determine which data carried the most weight, was worth the effort to profile and cleanse, and would therefore justify expensive transformations in the data warehouse.

Jamieson clarified further on the importance of the next step: data profiling. “You need to undertake data profiling on the raw source data to understand exactly how the data is captured and how operating behaviors impact that data. This process took several months and significantly more effort than originally anticipated.”

Hardware played a key role in the final product as the project scope and budget were revised when it was found that existing infrastructure could not deliver the needed performance for the estimated transactional volumes and data retention requirements. Specialized data warehousing appliances were procured, which also improved the productivity of the developers and testers with faster turnaround of code changes.

The introduction of process changes ensured data was being captured correctly on the road. “The way you build your schedules, define your gate and stop coordinates, and the way the operators are trained to use the system will impact the quality of the data that is available for analysis,” Jamieson said.

The ability to get to the idle times was the goal, primarily through the identification of excess run times, which proved to be significant in helping create even better efficiency within the schedules. As a secondary goal, it was suspected more idle time would be found within the garage deadheads but this was not known because the data had never been available for analysis before. Both goals were successfully accomplished, due to the diligence of the TransLink team.

With so much attention being focused on getting to the data, the team realized how much a proactive approach during the original TMAC system implementation could have helped them with their short- and long-term goals. Gerry Akkerman, director of business technology planning, served on the steering committee and found the run-time analysis project innovative and valuable.

Akkerman explained, “Agencies need to think about how they want to use their data from the outset of their ITS implementation. Having clear objectives for how the data will be used is critical to success. We learned very quickly that full access to data sets was absolutely necessary and that trying to clean data up or to understand the data architecture after system implementation was a heavy lift.”

Data Provides Results

The run-time analysis project concluded in 21 months with decisive results. The service data from the data warehouse was more than sufficient to deliver the value the committee needed and achieved full payback in one quarter.

By comparing the operational data with the scheduled data within the newly created data warehouse, analysts were able to extract and compile information at a rate of less than five minutes per route and small routes in only seconds. That was a huge time savings in comparison to the one to 12 hours it had previously taken. In addition, they increased the number of route analyses conducted from 50 per quarter to 1,200. This included all routes in both directions on all service days.

With the new speed of access to run-time data, schedulers were able to remove idle time in the schedule, saving more than 36,000 hours annually. The agency exceeded its target of removing 5,000 hours of idle time by more than 720 percent. By removing idle time from run times and deadheads, and reallocating resources to new or improved service, TransLink is able to realize the benefits in every schedule going forward and has established a solid baseline for monitoring the impacts of traffic and operations.

Fink, a business intelligence advocate, concluded, “The run-time analysis project has not only enabled us to optimize running times, it has also fostered improved union-management relations by allowing us to target problem areas and resolve issues quickly. Ultimately, our efficiency has greatly improved and there are gains yet to come.”

“Beyond the obvious tangible returns, there is intrinsic value in the mined CAD/AVL data — not just for the purpose of streamlining service, but also for use in future BI applications,” Init CEO Roland Staib said.

The lessons obtained through the run-time project have amplified the awareness of the value in business intelligence. It’s a success story about excellent project management and the benefits achieved through analysis of data. In addition, the agency is using charts and other visualizations of the data as an outreach tool to share with operators how run times are analyzed. This facilitates increased communication based on actual information, building trust in the scheduling and daily operations management practices.

During the project, a change request was submitted to acquire specialized data warehousing hardware and to provide additional time to understand the processes affecting data captured on the road. The changes were approved by the steering committee and were managed through business technology services’ project management discipline. More than 28 staff were involved in the run-time analysis project, including IT developers, business technology managers, data analysts, service and scheduling staff, in-house Init system experts and executive leaders.

In the end, the run-time analysis project will deliver an internal rate of return of 178 percent over five years. The project exceeded its four initial goals with great success. The key to the success of the project was the actual data from the fleet management system.

“Data matters,” repeated Akkerman. “We took the data from the Init system and turned it into insight. With modern management systems like this, agencies can gather critical intelligence, support sound business decisions and effectively streamline service.”

Akkerman continues actively communicating with other transit professionals about the value in ITS data, as well as business intelligence and service analysis, citing the run-time analysis project’s success to really drive it all home. MT

Ann Derby is the director, marketing & events with Init, Innovations in Transportation and Darla Jamieson is a project manager at TransLink.