Small bureau is big on data
Connect with state & local government leaders
Featured eBooks
Connecting state and local government leaders
Jeff Butler likes to compare the 10 months the Bureau of Transportation Statistics spent building the TranStats database portal to NASA's work on the Hubble space telescope. It was equally complex, he says, but unlike NASA, BTS suffered no last-minute hitches.
BTS' Jeff Butler stands at the I-395 overpass near Transportation headquarters in Washington, where an intermodal database of databases is gathering statistics about U.S. transportation.
Jeff Butler likes to compare the 10 months the Bureau of Transportation Statistics spent building the TranStats database portal to NASA's work on the Hubble space telescope. It was equally complex, he says, but unlike NASA, BTS suffered no last-minute hitches.
The Transportation Department agency funded all of TranStats' $2 million cost for systems and labor. Several of the two dozen portal builders volunteered, and others were volunteered from various BTS offices.
'They put in significant overtime,' said Butler, assistant director of the Office of Statistical Computing. 'We designed everything in-house.'
The TranStats intermodal database of 100 databases, launched in September at transtats.bts.gov, grew out of the 1998 Transportation Equity Act, which seeks to balance federal spending on surface transportation with safety, the environment and jobs.
The BTS application developers wrote about 1 million lines of Structured Query Language, Extensible Markup Language, JavaScript and Microsoft Active Server Pages code to pull together the raw source data and serve it up on the Web.
They built TranStats as a big container for feeder organizations'other Transportation agencies, the Bureau of Labor Statistics, the Census Bureau, the Energy Information Administration and outside providers.
The feeder agencies keep their statistics in various formats, not all databases. Stats arrive by CD-ROM or File Transfer Protocol in everything from Borland dBase and comma-delimited ASCII to Oracle, Microsoft Access and Excel, and other formats.
So the TranStats developers first had to decide how to reformat the raw source data 'in a way conducive for analysis,' Butler said.
They also had to provide search capability through Transportation's DOTbot spidering engine. About 35 percent of the 15,000 daily hits on TranStats come from DOTbot queries.
'Our secret weapon' was Sybase Adaptive Server IQ Multiplex from Sybase Inc. of Dublin, Calif., Butler said. The business-intelligence database system runs on a cluster of Sun Microsystems Sun Fire V880 servers: one for Web service, another inside the agency's firewall and a separate failover server.
The portal users include federal and state transportation agencies, metropolitan planning groups, trade associations, and other analysts and researchers.
Butler said, 'They kept asking us, 'Why do we have to go all over the place?' ' So BTS tried to simplify access to transportation statistics for the widest possible range of users. The users' common response, he said, has been: 'It's about time. When can we expect other data?'
Early resistance
While the developers were at work on the container code, BTS officials contacted the feeder agencies and other providers. 'We encountered some early resistance,' Butler said. 'We emphasized the benefits' of a ready-made container and a single point of access to transportation statistics. Some of the agencies update their information on a monthly cycle, some quarterly and some annually. BTS had to set up a delivery process and schedule for each one, then adhere to it.
'We started with the biggest, most-used databases,' Butler said. Each has several tables, plus metadata and a lookup table. The databases report on everything from the percentage of flights delayed at airports and pedestrian traffic across U.S. borders to crude petroleum prices and ships in ports.
'When BTS showed me TranStats, I was very impressed,' said Kim Taylor, Transportation's acting CIO. 'This site is an excellent example of agencies working together on an e-government initiative that provides citizens with tools to get answers to their questions, when and how they want them.'
Taylor said he is continuing to try to persuade the Office of Management and Budget to add TranStats to its roster of 25 Quicksilver e-government initiatives.
Whether or not that happens, BTS is committed to improving the portal. 'After the first year, you have to automate more processes,' Butler said.