Business intell speeds Recovery Act reporting for DOE
Agency makes the most of data warehouse with business intelligence tools
- By Joab Jackson
- Oct 22, 2009
Here's a new challenge for agencies: How to grapple with suddenly larger budgets while reporting with more detail and timeliness on how that additional money gets spent.
Like many federal agencies, the Energy Department has found itself with additional money to fund its projects, thanks to (ARRA) funding. In fact, being the locus of many of the Obama administration's initiatives with energy conservation, DOE's budget had effectively doubled from $22 billion to almost $44 billion.
But with this greater funding comes greater responsibility to disclose how it is being used. And this transparency – DOE's Office of Corporate Information is finding out – means different things to different stakeholders: The agency's own procurement and budgeting offices, the Office of Management and Budget, state agencies and the public all want to see how that money is being spent.
The surge of funding "brings its own challenges in executing wisely and achieving the goals we're trying to achieve with those dollars," said Lajos Grof-Tisza, a project manager for the agency's internal business software portal. Grof-Tisza spoke at the Oracle Federal Forum, being held this week in Washington.
Business intelligence tools have helped immeasurably in the process of reporting, Grof-Tisza noted. His office used Oracle Business Intelligence Enterprise Edition to automate and ease some of the reporting duties.
Signed into law this past February, ARRA allocated billions of dollars to help stimulate the economy. DOE has a big role in many of the initiatives it funds, such as smart-grid technologies, developing cleaner sources of energy, researching superior battery technologies, and others. Many of these funds will go toward new projects, and use of all the funds will have to be reported to the Office of Management and Budget and to the public, through recovery.gov and the Energy's own recovery funds portal.
And the data needs to be delivered quickly: OMB is requiring near-instant access to financial reports. DOE has long been providing weekly reports, then recently began sending over daily reports, and now is working on ways to let OMB have direct access to DOE data warehouse. DOE also has to give each state a list of all the contracts that the agency awarded within 48 hours of the award.
The good news is that the agency already had been building out a reporting system for managing agencywide systems and data called iManage. The idea behind iManage is to provide a single entry point for users to draw information from the core enterprise systems. For recovery reporting, the idea was to use data from the core systems to provide the basic information, and make minor modifications to address the new reporting requirements, by building new capabilities from business intelligence software.
DOE's architecture of reporting is built on five enterprise core systems: The accounting system is run on the Oracle E-Business Suite. The procurement system is Compusearch Prism. Human resources tasks are handled by PeopleSoft. Performance Management is handled by a Government Line of Business Performance Management Module and project management is done through a variety of systems, including Oracle's own Primavera.
Raw data is rolled up from all these systems into a data warehouse. "This really is a centerpiece for the reports and information that goes out to the portal," as well as to the Recovery.gov and the Energy's own recovery sites, Grof-Tisza said.
The business intelligence software works with the data warehouse information. "There is a lot of capability built into the business intelligence tool that allows us a lot of flexibility," Grof-Tisza said. Notably, software automates and simplifies a lot of routine requests, allowing the iManage team to "manage by exception."
Some new capabilities include the ability for staff to receive daily project and financial reports and even receive alert e-mails when the numbers aren't falling within expected ranges.
The portal also features the ability for users to create their own reports. This feature alone has been a huge help for the agency. By allowing users to access the business intelligence software, "has been really helpful for us to bring down the number of standard reports," the office must maintain.
Previously, the approach that the agency used was to create standard reports. Every time a budget office or OMB needed a new set of numbers, the agency would create a standard report. But the answers provided in one report only lead to more questions — and more requests for additional reports. Over time, they had built up over 80 standard reports.
Such a large array of reports could be a hassle, given that they needed to be maintained and updated when their data sources changed, and employees who required budgetary information had to know what all the existing reports did, before reporting a new one.
With the new approach, a Web page allows users to select a program office and get back numbers and charts that specify spending in a variety of ways, similar to the functions of a spreadsheet pivot table. By allowing users to select the columns themselves, they can "slice and dice" the data.
"There is no right or wrong to some of these reports. It's just different slices and dices of the data," Grof-Tisza said.
Users are presented with high-level departmental summaries, but they can drill down into aspects of individual projects. "We've seen a lot of our senior managers take advantage of that," Grof-Tisza said.
One of the handiest tools has been Oracle Application Express (APEX), a comparatively little-known free Oracle tool that allows users to connect a Web form or spreadsheet with an Oracle database. "We're using this as a rapid form-development tool," Grof-Tisza said.
Often the headquarter's budget or procurement office will request data. Usually this involves working with the program offices under scrutiny. The program offices, however, can be simply overwhelmed with fulfilling such requests. APEX can provide tools that can allow Grof-Tisza's office to work up the requested information within a few hours, by pulling the data directly from the corporate systems.
Geospatial presentations can also help in recovery reporting. Users of the Web site can drill down to find out how much of Energy's recovery spending is affecting a certain location, such as a state or a county. "It lets them know where the recovery act dollars are actually going," Grof-Tisza said. In this task, the agency applies the geographically encoded data to Google Maps.
Another challenge: Reconciling the recovery-spending numbers on FederalReporting.Gov with the information submitted by the various grant recipients and against the numbers in the DOE's own accounting and procurements systems. "There is no way we could audit tens of thousands of recipients, grants and contracts," Grof-Tisza said. Once again, the use of business intelligence tools helps automate the comparisons process.
"Our stuff is automated. We don't have to manually do anything. The business rules are in place to validate that information," he said.
Joab Jackson is the senior technology editor for Government Computer News.