Easy data mart
Connecting state and local government leaders
Without any procurement effort, a pair of computer-savvy General Accounting Office officials have put together a data mart to track the office's studies of homeland security projects.
Without any procurement effort, a pair of computer-savvy General Accounting Office officials have put together a data mart to track the office's studies of homeland security projects.Alice G. Feldesman, assistant director of GAO's Office of Applied Research and Methods, and colleague Stephen Palincsar of GAO's Web services group used applications from SAS Institute Inc. of Cary, N.C., to automatically coordinate project data that had previously been hand-collected.Shortly after Sept. 11, 2001, GAO formed a national preparedness team to coordinate its research into homeland security issues. Since then, the office has produced more than 110 reports and testimonies on security and terrorism, according to .GAO conducts some studies, such as a June 2003 report on transportation security, at the request of members of Congress. Such reports might fill 50 to 150 pages including agency responses and multiple appendices.Other GAO documents are prepared statements for delivery before congressional committees or follow-up correspondence related to hearings. But preparation of even comparatively short documents requires a lot of background work.At first, the GAO researchers who were working on homeland security used a Microsoft Word table to keep track of what was going on within GAO, Feldesman said.Information flowed in from several job-tracking services in use at GAO, but manual entry of the information into the Word table fell short with an ever-growing number of efforts in progress.'After a period of time, they realized that the Word table, while extremely useful in terms of coordination, was becoming a monster to feed,' Palincsar said.The team knew that much of the information they needed already existed in electronic format, but it was scattered across three agency systems that didn't talk to one another.'We looked at a mini data mart'a small-scale exercise in data warehousing, which involves taking data out of transactional systems and merging it for analysis,' Palincsar said.For the front end of the mini data mart, Palincsar downloaded and set up the open-source PHP dataMiner database administration tool.At the back end, Palincsar and Feldesman had to pull together fields from a Microsoft Access database, an Oracle Corp. database and a mainframe system, which were all operating as disconnected chunks.'You wouldn't normally think of using SAS as a jackknife for carving up data sets,' he said, but that's what he and Feldesman did to merge the data and deliver it to GAO's intranet in tabular form.'I was able to harvest code from other projects,' Feldesman said, and the Word table was finally banished from the GAO workflow.Some of the data sets change throughout the day or at least get updated every morning, Feldesman said. So the tool runs every day to refresh the picture of GAO's ongoing preparedness research at any particular time.The tool, accessible through the agency's national preparedness intranet portal, selects certain fields from the different data sets to give a meaningful picture of ongoing research.GAO workers can identify the projects by stage of development, project leader or methodologies used.'Those pieces of data became information,' Feldesman said. 'Before, it was data sitting out there in different places.'Feldesman said she built the mini data mart at times between projects, so no contractor was needed. She first got a copy of the Access database in January 2002 and had the data mart up and running by that spring.Although other tools besides SAS exist for building small-scale data warehouses, GAO had already made the investment in the statistical platform, and Palincsar and Feldesman already had the training to develop SAS applications.'The bottom line is that I wanted to learn one system and learn it well,' Feldesman said.
GAO builds its own work-tracking app
www.gao.gov/homelandsecurity.html
Scattered data
'After a period of time, they realized that the Word table, while extremely useful in terms of coordination, was becoming a monster to feed.'
'GAO's Stephen Palincsar
J. Adam Fenster
GAO's Alice Feldesman used her SAS experience to merge data from several systems.
J. Adam Fenster
www.gao.gov/homelandsecurity.html
Scattered data
NEXT STORY: Editorial Cartoon