Transportation aggregates hazmat reporting

Approximately 300,000 companies in the United States ship hazardous materials (hazmat) on a regular basis. Collectively, they dispatch about a million shipments en route across the country every day. Unfortunately, there are only about 800 inspectors among all the federal agencies that ensure that such shipments adhere to safety specifications.

Obviously, any system that can help inspectors choose which companies to inspect would be a great help. And to this end, last November, the Transportation Department unveiled a Web portal for pinpointing the locations of hazardous materials around the country. The portal, collaboration between Transportation and other agencies that oversee hazardous materials, draws data from 26 different sources.

The idea behind the portal was to provide federal agencies with a central hub where hazmat information from all the agencies could be viewed in a single place. Portions of this portal will be open to the public by 2010, though now it is only accessible, via a password-protected Web interface, to registered government employees.

"The purpose is to bring all the different all the different data sources and role them into one central data warehouse, which will allow us to put together reports that we were never able to put together before," said Adrian Carter, the Transportation program manager for the portal.

The Hazardous Intelligence Portal pulls together information from multiple agencies about these locations of these facilities and the materials they ship. Each agency has a representative who gets input into how the portal should operate.

Agencies such the Pipeline and Hazardous Materials Safety Administration, the Federal Railroad Administration, Transportation's own Federal Motor Carrier Safety Administration and the Coast Guard all enter information into, and draw information from, this system. The program is negotiating additional agreements that would let the Centers for Disease Control and Prevention and the Transportation Security Agency participate as well.

Inspectors and investigators from these agencies use the site to help them make decisions on how to most efficiently plan their workloads. It also gives insight into a matter that is currently under inspection. For instance, if the Federal Aviation Administration is fining a company, it can look to see if another agency, such as the Coast Guard, has fined it for a similar violation.

"It stops duplicative effort," Carter said. "So if one agency is doing an inspection, another agency doesn't have to come behind and do another inspection."

Each participating agency has a coordinator who supplies the program with a list of users and what level of access they should have for the system. The system itself has different access levels for different roles — inspector, investigator, manager and so on.

"This project required a lot of cross-agency cooperation," said Peter Doolan, who is a vice president of sales consulting for Oracle Public Sector. "Just getting stakeholders to rally around the project was a great achievement. And once they had access to the data, the guidance team did a great job in quickly getting information on the portal for stakeholders to see."

Technical underpinnings

The system collects data from the external sources each night. The source formats range from relational databases to flat files. An Extract, Transform and Upload (ETL) tool from Informatica converts the data from its source format. The data is transported over private networks or via the Internet. Right now, the data store is about 500 gigabytes of information, but it is growing rapidly.

The presentation layer, as well as the analytic capabilities is supplied by the Oracle Business Intelligence Suite Enterprise Edition. Guident Technologies helped with the implementation. This package included the database, along a pre-created data model that tightly integrated with a set of analytic application. Oracle has a number of different data models for various tasks. In this case, Oracle's Service Analytics Data model was the closet to what the project was trying to do.

"We've already created the schemas for the database so it can be populated with the source information," Doolan said. The business intelligence components, such as the graphs, pivot tables, drill-downs and other functions are already wired into the appropriate fields.

Although customer service may not seem to be a close fit to hazmat incident tracking, it had many of the same characteristics.

"If you think about it, what you are looking at is a data model whose master data element is the tracking of an event," Doolan said. "That's exactly the same as a service request." Or, at least the two are close enough that using this data model would save time over building one from scratch.

At the beginning of the project, the program employees did an exhaustive survey all the fields of data from all the data sources, and mapped each of the data elements to the predefined data model.

"Data is broken down by various subject areas," Carter said. For instance, the site can offer a list of manufacturers that have recently been inspected. So if an inspector is visiting a certain region, that person can avoid the locations that recently passed inspection.

It can offer information about what other possible sites they could visit and even prioritize the different sites. The dashboard uses 38 different issues to help judge the priority of a site needing to be inspected, such as date of last inspection, last types of incidents and the type of material and products being shipped.

The business intelligence component allows the users to look at trending issues. The portal offers cross-agency views into this data by subject areas, such as incidents and dates of last inspection. It also offers different analysis tools to shape the data.

"This gives the government a bird's eye view of what is going on," Doolan said. For instance, an inspector can see if there are specific companies that are having more issues than others. The software can also aggregate data from across different agencies into specific categories. How many incidents happened while the material was in flight? How many incidents happen in a particular region? How many incidents deal with mislabeling?

Carter said that the next phase for the portal is to actually show where hazardous materials are as they are en route. "We want to map the flow of that commodity, throughout states," Carter said. "If someone is shipping chlorine, we want to see how that chlorine throughout the local railways and highways. So if an incident happens, we can get that data out to first responders." The project team has gathered the requirements for this feature and plans to offer the capability by 2010.