Agency makes data warehouse click

Rhode Island Public Transit Authority

Faced with limited budgets and few staff resources, the Rhode Island Public Transit Authority has created a new warehouse for legacy data using a relatively simple point-and-click, Web-based approach.

The old system was put together beginning around 1990 and contains vital data such as employee and vehicle performance statistics and fleet maintenance records. But there was no way to aggregate data from separate records and produce trend reports, according to Vincent Reina, RIPTA's director of operations.

Such things as employee sick days were still being tracked using pencil and paper.

Now, using DT/Studio, an extraction, transformation and loading (ETL) tool from Embarcadero Technologies Inc., Reina is able to populate the new database using a graphical interface to define relationships among data.

"We would have liked to have done this in-house, but our [information systems] department only has a small staff and it wasn't possible," he said. "To make matters worse, even the company that provided the original system didn't know how to integrate [the legacy data]."

DT/Studio uses a Java-based ETL engine. In addition to being able to point to existing sources and transfer data to a new system, it can also be used to design target data structures for specific reporting purposes, said Stephen Aikins, Embarcadero's product manager for DT/Studio.

Because the ETL engine is Java-based, the new database design can be tested in the development phase and then moved into production without requiring changes to any of the code.

Reina expects that the tool will be useful in looking at datasets that other transit industry fleets around the country are using, to see what can be applied to RIPTA's situation. It also can be used to incorporate active data produced on individual RIPTA desktops into the new data repository.

Robinson is a freelance journalist based in Portland, Ore. He can be reached at hullite@mindspring.com.