Use Business Intelligence to Drive Savings

Sept. 13, 2013
Make sound business decisions through the efficient analysis of effective data.

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 to be 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, as well as 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 the delivered service data is necessary for analyzing the quality of operations, reporting to management and, in return, building more efficient schedules.

For TransLink, the delivered service data was captured with the fleet management system. However the agency needed a way to access the data quickly and easily, to compare it with the current schedule data to effectively analyze run times (the time it takes operators to complete bus routes/runs), 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 that took between one to 12 hours for each route. This consumed numerous 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. Later, real-time passenger information displays were added to the project.

TransLink Project Manager Darla Jamieson set the goal of extracting and processing the agency’s critical data more proficiently. In October 2010, she prepared a business case for a run-time analysis project that would tie the scheduled run time service data with the delivered service data and 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 for achieving efficiencies and recognition of the enormous potential of having timely actual run-time data for his staff.

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:

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

The targeted goals in the proposal aimed to increase the analysts’ processing capacity from 50 analyses to 200 analyses per quarter by reducing the extract time from one to 12 hours per route to less than an hour per route.

The TransLink team began building a data warehouse where the service data — arrive 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 that 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, were 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 that the 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,” said Jamieson.

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 that 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 the data 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,” INIT CEO Roland Staib said, “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.”

The lessons obtained through the run-time project have amplified the awareness of the value in business intelligence. It is a success story about excellent project management and the benefits that can be 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 & 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.

Ann Derby is the director, marketing & events with INIT and Darla Jamieson is a project manager at TransLink.