February 16, 2009
Warehouse Technology Means Better Data
By KENNETH HERRSCHER
In many public transit agencies, applications grow to solve management’s need to know how well it is performing in all aspects of operations, including accounting and finance, maintenance and coach operations, scheduling, customer service, marketing, and risk management.
The systems face many issues related to their operations. Is ridership increasing, and will that lead to revenue growth? How well are coach operators performing? Are they safe? Do they comply with performance standards? Are we using them effectively? Are customer complaints increasing or decreasing? What is our accident rate? Are we transporting more riders from every vehicle revenue service mile and hour? Do our vehicles arrive at their stops on time?
The problem is that such questions are often difficult to answer for planning, scheduling, and operations departments. To help them find solutions to these questions, transit agencies may develop in-house applications based on Microsoft Access and Excel.
Recently, one of our transit clients, the Orange County Transportation Authority (OCTA) in Orange, CA, found itself surrounded by numerous information silos dealing with key performance-indicative questions. Almost every department kept its own scores of similar Enterprise Performance Indicators (EPI). However, within months after implementing these information silo applications, OCTA managers found that they had incomplete and duplicated answers that did not agree with other silos’ answers to the same questions.
The solution for OCTA was implementing an industry standard data warehousing model to merge the silos’ source application data into a single version. The Enterprise Data Warehouse, TransitFACTS, includes data marts to facilitate ad hoc queries for transit analysts on such topics as payroll and retirement benefits; coach operator and vehicle scheduling; bidding; attendance; vehicle maintenance; accident and risk management; contract ridership; market development; and university pass and employer pass usage billed to employers who subsidize their employees’ commutes. TransitFACTS also offers an Operational Data Store (ODS) to facilitate monthly operational and financial reporting to OCTA and its board of directors.
Every night, OCTA imports ridership data from the farebox system into the ridership and pass data marts and the library of web-based reports developed for the OCTA Board of Directors’ monthly operational review meetings. A farebox connection to the two-way messaging system on each vehicle enables us to collect the Global Positioning Satellite system latitude and longitudinal coordinates at the time the fare was collected or the pass was used.
Transforming Bad Information into Good
As OCTA’s Geographic Information Systems department reviewed the new GPS-based farebox transactions, the agency occasionally found that all of a route’s transactions were “off-route”: the transactions were made on streets with valid routes on them but, based on the time stamps, the farebox captured the wrong route. Some of these errors occurred from incorrect data entry, as the farebox software does not edit a coach operator’s logon information; others occurred because of equipment malfunction, or because the data was simply not produced by OCTA’s version of farebox software.
As an alternative to spending millions of dollars on a farebox upgrade, OCTA decided to use TransitFACTS to evaluate the erroneous farebox transactions in context with other applications, such as automatic passenger counting, fixed route scheduling, and the coach operator’s scheduling systems.
We designed a sophisticated nightly load process to review the time stamps and GPS coordinates of the raw farebox ridership transactions. It also examined the fixed route and coach operators’ scheduling systems to determine the correct route, trip, pattern, direction, and Stop ID to be assigned. This process preserved the original values of these columns, with the correct data values stored next to the originals they replaced.
By creating a set of performance reports, OCTA was able to correct events that led up to capturing more than $1 million worth of unclassified revenue in a single month. The term “unclassified revenue” refers to revenue not classified by a farebox key or revenue that defaulted to a specific key, as none was pressed to record the fare properly.
An Enterprise Data Warehouse such as TransitFACTS can serve as the catalyst for change a transit agency needs to improve its customer service. By keeping all the data in one repository, the needs of the entire agency are better supported.