Measure once, use often

"When someone has a new data source, I go through a set of questions: How do you want to measure this? Do you have a way you are already reporting this data?" Chuck Cameron, DAU

Most agencies would like to reuse at least some of the data they collect every day. The trick, however, is to ensure that the context of the data is kept intact, so that people reusing it will know what they are looking at.

The Defense Acquisition University, headquartered in Fort Belvoir, Va., has gotten a handle on this problem through the use of a data warehouse and data integration software.

Overseen by the secretary of Defense, DAU trains the Defense Department's acquisition, technology and logistics personnel. It has six campuses across the country, and graduates 115,000 students a year. DAU has about 40 different sources of student, employee and finance data, which are fed into a data warehouse and used to generate more than 2,400 reports.

For the past two years, the agency has used the Informatica PowerCenter data integration platform from Informatica Corp. of Redwood City, Calif., to build the data warehouse and the Informatica PowerAnalyzer to compile reports.

The agency started the data warehouse to fuse forecast budget data'available only in a custom-built mainframe application for Defense finance accounting'with actual expenditures, which was kept in another DAU system. The agency's IT staff also wanted to fuse data from an electronic time management system with this financial data. Now, DAU has all that data replicated in a single set of reports.

'I can look at the total cost of a specific item from both a nonlabor and a labor standpoint, whereas before I had to go to each different system to track those items,' said Chuck Cameron, the DAU systems engineer who oversaw the implementation. 'I had to pull the data out and use the old spreadsheet method of mapping things to each other.'

Since the system went live, others in the agency have asked for similar summaries of their own material. 'The sources have come fast and furious after we showed the functionality,' Cameron said.

Every faculty member gets a report on the success of their classes, as well as a status on their professional development. Managers get reports on class costs, teacher certifications and employee time accounting, as well as on graduation and attrition rates.

DAU offers regular reports to agencies and large government contractors on how many of their employees have taken classes and how far along they are in various training programs. DAU's IT department gets regular reports on the number of call-center requests per year, as well as how many unread e-mails are at the help desk, divided by day, month and year.

Faster Implementation

Cameron's team also used the analysis tools to expedite the implementation of a human resources system.

The new system required data from a number of external systems. A contractor had struggled for two years to complete the job, but was slowed by the process of mapping the data from the original sources. Because the data warehouse already had the key to the information, the IT team was easily able to link the data sources into the appropriate parts of the HR system. The job was completed within six weeks.

Understanding data outside of its context 'can be the biggest challenge' to reusing it, Cameron noted. When someone has a new data source, Cameron looks for ways to map it into the data warehouse. 'I go through a set of questions: How do you want to measure this? Do you have a way you are already reporting this data? If so, how can I see how your reporting it now?' he said. 'If I see how they are reporting it, it may come to me how I can map that into existing dimensions of my data warehouse,' he said.

Although much of the data warehouse work is done now by the IT staff, Cameron is hoping that the analysis tool is easy enough to use for people who aren't knowledgeable in running business intelligence applications.

'We did a lot of due diligence, knowing that our core audience was not BI savvy. We wanted a tool that they could feel comfortable with,' he said. Reports that need to be generated on a regular basis can be scheduled to be completed automatically by the software.

The DAU is one of about 100 federal customers for Informatica, said Jim Pruden, director of federal sales for the company. In addition to the Defense Department, the company also does a brisk business with the Homeland Security and Justice departments, where the software is used to share data among the 22 different agencies that went into making that agency.

'DHS has incredible data integration challenge,' Pruden said, adding that the department has 'every kind of data source, new and old.' In some cases, information just doesn't get used. In other cases, administrators write out scripts to fuse the data, though that approach tends not to scale well.

The company is one of a number of different vendors that offer Extracting, Transforming and Loading software. ETL software integrates data by automatically pulling information from databases and other sources and formatting it into a common form. Informatica's PowerCenter line of software also includes a bevy of analysis and report formatting tools.

The power of ETL software is its ability to access data in both structured forms, such as databases, and unstructured forms, such as the flat text files produced by old mainframe computers, or word processing documents. 'We can access them in batch, or as soon as someone accesses a record, we can detect it,' Pruden said.

Although in most cases, the data is aggregated into a data warehouse, Informatica's software can also be used to deliver aggregated, formatted data directly to a portal, or to another application.

'People are looking for the right data at the right time. And we can provide the data on-demand,' Pruden said.

About the Author

Joab Jackson is the senior technology editor for Government Computer News.