Feds turn to OLAP as reporting tool

With budget cuts and staff downsizings, the federal government is wrestling with many of the same issues as private industry. Like Fortune 1000 companies, federal agencies are competing with fewer resources and greater degrees of accountability for performance.

In this climate, on-line analytical processing (OLAP) technology moved into the federal market, following its success in the commercial world. With its roots in decision-support systems and executive-information systems, OLAP is a client/server software technology that analyzes data and presents patterns and trends that help senior managers make decisions. OLAP generally is used within networked client/server environments with SQL databases consolidated (although separately accessed) in a data warehouse. The technology provides the ability to compare and analyze data from multiple sources.

Two legislative initiatives in particular have spurred the use of OLAP in the federal market. First, the Chief Financial Officers Act of 1990 required agencies to create integrated financial-management systems. Second, the Government Performance and Results Act of 1993 requires performance-based budgeting. Agencies must comply with some of the provisions of GPRA starting next year. Federal information technology managers within the past year have started using OLAP to meet the requirements of such initiatives.

"Two of the key applications for OLAP are budgeting and financial consolidation," said Michael Joseph, an analyst covering applications and information access at International Data Corp., Framingham, Mass. "Both the federal government and the commercial market use OLAP to determine performance to budget. The goal here is to be able to affect future...budget decisions to determine allocation."

There are various ways to approach the use of OLAP technology. The Transportation Department, for example, has built a data warehouse and repository using Arbor Software Corp.'s Essbase 3.2 multidimensional database analysis server. Essbase is optimized for OLAP and supports a wide variety of de facto standard commercial products and platforms.

"We're using it as a framework for off-the-shelf products," said Richard Gates, a financial information specialist at DOT's Office of the Assistant Secretary for Budget and Programs. "We did not want to get into any custom development whatsoever to which you would be beholden to someone for support."

Gates' office built its Executive Reporting Framework (ERF) system to manage grants and operations data with the Essbase 3.2 Analysis Server. Representing the largest part of the budget, grants data reflects the taxes and fees that DOT distributes back to states for highway and bridge construction, airport development and transit systems. Operations data covers payroll, administrative expenses, travel, training and other day-to-day operations costs.

ERF uses Microsoft Corp.'s Excel and Lotus Development Corp.'s 1-2-3 spreadsheets and Lotus Notes as front-end systems to Essbase, which extracts financial information from DOT's mainframe-based Department Accounting Financial Information System (DAFIS).

ERF already has achieved increased financial accountability, Gates said. "We discovered when we were tracking the budget for salaries and expenses appropriation that there were some areas where we were overplanned. We made adjustments within the quarter so we didn't go over budget," Gates said. He is extending ERF to manage budget projections, development and formulation. "If we get the word from [Office of Management and Budget] that we're going to have to take a budget cut, then we want to project how that's going to affect our different programs," he said. "Rather than reporting on what's happened, we'll use it more dynamically to project ahead."

Gates reports to Louise Frankel Stoll, DOT's chief financial officer and assistant secretary for budget and programs, who is responsible for managing DOT's $40 billion budget. When Stoll was appointed to her position in 1993, there was no financial analysis system to compare the department's budget with congressional appropriations or to help managers track performance against budget.

The Commerce Department also chose to use a commercial product to implement OLAP technology in its Financial Executive Information System (FEIS). The department built the system using Cognos Corp.'s Powerplay multidimensional analysis software and its Impromptu query and reporting tool.

FEIS is essentially a management-support system designed to aid senior-level and support staff in analyzing a variety of conditions across an array of management functions. In FEIS, a Hewlett-Packard Co. HP 9000 extracts data from IBM Corp. mainframes, analyzes that data and reports the results via Banyan Networks Inc. and Transmission Control Protocol/Internet Protocol local-area networks to Microsoft Windows PCs. With Windows 3.1, Windows NT and Windows 95 interfaces, Powerplay also provides direct native access to Arbor's Essbase, Oracle Corp.'s Express, Pilot Software's Lightship and Informix Software Inc.'s MetaCube multidimensional databases. Written 90 percent with Powerplay and 10 percent with Impromptu, FEIS tracks the usage of government bank cards throughout Commerce.

"We have the ability to drill down to every transaction. But trend analysis is what senior-level managers are interested in," said Pat Smith, director of the Office of Computer Services within Commerce's Office of the Secretary. Smith said the department also uses FEIS to monitor its supervisor-to-employee ratio and the number of personnel specialists who work in the department's equivalent of a human resources office. The department also began recently to prototype an extension to FEIS that tracks frequent-flier miles. The seven-user prototype will expand to 15 users in August, Smith said.

The department saved about 85 percent of costs by using the Cognos Powerplay off-the-shelf system instead of building its own. "You can spend millions of dollars creating a new system," Smith said. "That's not what we wanted to do. We wanted a system that could go right into legacy systems." With fewer dollars and less staff, Smith plans to use FEIS to manage resources more effectively. "As we have less support personnel, it'll require more extensive use of these systems to support senior policy officials."

Commerce also is offering FEIS to other agencies and has demonstrated the system to the Treasury and Energy departments and the Office of Personnel Management. The latter is considering the use of FEIS to track retirement claims, Smith said. In addition, the Government Management Reform Act of 1994 created a program called franchising, or cross-servicing, which allows agencies to compete with one another in such fields as data processing to serve other agencies.

The Marine Corps, meanwhile, is beefing up its Total Force Decision Support System with an eye toward providing OLAP capabilities. TFDSS is based on an Oracle data warehouse. The division has created a test bed of "data marts" - subsets of a data warehouse - to see if they would support the use of OLAP for trend analysis and personnel assignments, said Capt. Dan McQuay, a systems analyst with the Marine Corps' Manpower and Reserve Affairs Department.

While the deployment of OLAP is still in the exploratory stage, the Marine Corps' goal is to be able to drill down to find personnel best suited for certain assignments. The OLAP process could filter appropriate vs. inappropriate personnel to make the assignment process easier. It also could forecast best practices based upon the historical effects of policy.

Among the products and technologies under evaluation are middleware and gateway products from vendors such as Platinum Technology Inc., MicroStrategy Inc.'s DSS Agent OLAP product and Software AG's Esperant query and reporting tool. The Marines also are looking at Sybase/PowerSoft's Powerbuilder and Microsoft's Visual Basic as front ends.

"We have a variety of users with different requirements, so we don't think any one tool meets all those needs," McQuay said. "We're trying to build an infrastructure in which the users can chose their own tools." The Oracle-based TFDSS pulls data off a corporate mainframe VSAM flat file database, so the Marine Corps is investigating products that could integrate well into its existing environment.

The Marines are taking OLAP implementation one step at a time. In three months the first end user will be supported, and the service hopes to learn from that user how to support others better. Within the next two years, the Marine Corps hopes to have the data warehouse and OLAP technology set in place. In the meantime, it will work to overcome the primary obstacles to deploying OLAP. Those include cleaning the data (obtaining it, then presenting it in a logical sequence) and integration issues surrounding the database, gateway and middleware tools, McQuay said.

Regardless of the approach, the bottom line is that those who master OLAP could be better positioned to compete for program funds.

"As inquiries by OMB increase, the need for the ad hoc reporting qualities of OLAP will also increase," said Kirk Cruikshank, Arbor's vice president of marketing. "Tracking and analyzing budgets shows you have good control over a program."

Cruikshank said the next vehicle for OLAP likely will be through the World Wide Web. To seize that opportunity, Arbor recently released Essbase Web Gateway, a server application that provides read and write access to Essbase multidimensional databases over the Web. "The government ought to be jumping all over this Web stuff, if for no other reason than for an intranet," Cruikshank said. "To use a standard Web browser for OLAP functionality represents low administrative cost and low cost per desktop."

From what federal users are saying and doing, it appears that the jumping has already begun.

**

Gerber is a free-lance writer based in Kingston, N.Y.