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.