In the late 1990s, the United States Air Force faced a major financial management challenge. Wing-level administrators knew that budgeted money was being spent on various activities and programs, but they could not effectively track the expenditures in a timely manner. It simply took too long to get a clear picture of how and where money was being spent.
Don Henney, the 11th Wing's comptroller and senior manager with 29 years of experience, was all too familiar with these budget and cost analysis difficulties. In 1999, he and other senior leaders were challenged to increase Air Force flying hours by 40 percent, at the same time providing hard, verifiable justification for any funding increase. "We had to squeeze every penny, and found it nearly impossible to get timely data out of our multitude of systems in order to properly track budget against expense," he remembers. "This made it very difficult to accurately track and project our flying hour costs in order to justify increased funding."
Henney recalls that leaders were held accountable for daily activities using a cumbersome system, where reports took days or even weeks to generate inaccurate data. Financial personnel were overburdened trying to validate the cost structure because the real-life expenditures were far exceeding projections.
The answer was the development of the Commanders' Resource Integration System (CRIS), a global data warehouse featuring financial management analysis and executive decision support tools. It is now the Air Force's financial management tool of choice for the operations and maintenance budget, approximately $37 billion annually. How this system handled ever-increasing user and query loads while providing an expanding range of results to help solve budgetary challenges provides insights for business intelligence professionals everywhere.
Like many large enterprises, the Air Force recognized the potential resource savings that could be realized through the use of data warehouse technology. However, all data warehouse systems are not created equal. After considering a number of alternatives, the Secretary of the Air Force's Financial Management Division sponsored development of the CRIS data warehouse. While initial development focused on financial and funding decision support, the success of CRIS has led to much broader applications. The design, engineering and implementation achieved the elusive combination of increasing performance, capabilities and features while handling substantial increases in client usage. This combination of seemingly incompatible but essential system features, the "perfect storm" scenario, is exactly what the Air Force needed.
Air Force financial managers recognized there was a huge amount of data available to support financial analysis needs, but the data was scattered across a number of disparate legacy systems that were largely incompatible and could not offer standardized query results.
"We needed to do a better job of detailing and analyzing flying hour costs," Henney notes. "We had variables such as fuel, supplies, repairables, contracted support and more. Analyzing several hundred thousand line items per unit to find cost drivers was no small feat. Our flying hour costs were huge, and our repairable costs were escalating out of control. If someone had a better way to do business, we were going to jump on it."
These challenges echoed those of the private sector, where IT resources were commonly isolated in incompatible applications, databases or formats. As a result, data was not readily available to assist with broader enterprise information-dependent tasks.
In defining the initial requirements, the Air Force recognized the need to pursue forward-looking technologies that offered multiple uses, with an emphasis on scalability, while remaining cost-effective to meet ever-tightening budgetary constraints.
THE DATA WAREHOUSE SYSTEM
The system engineers selected a "scale-out" architecture (see Figure 1), providing a shared environment but with separate, independent modules. Scalability of the architecture was particularly beneficial: it returned value early in the development process and allowed for the flexibility to add or modify easily. The modular architecture reduced start-up costs by allowing incremental development.
Figure 1: Data Warehouse Flow Diagram
The modular software architecture design of CRIS is meta data driven end to end. The meta data repository contains the physical model of the warehouse, the transformation processes and a logical model of data associations. The meta data model helps keep the cost of modifications and additions low, and maintains consistent data and process quality. The architecture also employs meta data support for multiple representations of data which, coupled with the use of an "intelligent mid-tier" server, allows CRIS to select the best performing schema for a query.
The CRIS ETL process loads multiple databases simultaneously, in parallel, which greatly increases processing efficiency - an absolute necessity in an environment where data sets can be extremely large and must be available globally in near real time. Feeds are retrieved/received using high-speed, secure Web interface file transfer. Translation produces a common format. Loading establishes data tables that are published to schemas on multiple data servers. Data servers feed data marts - which hold the most commonly accessed data fields for easy, fast access and query processing.
The accuracy of the data held in a warehouse is central to the warehouse's fundamental usefulness. CRIS addresses data accuracy in three ways. First, source data is exercised against a set of client-defined rules for verification (i.e., the client's data, thus the client's rules). Second, the source data is considered "system of record," meaning the accuracy is certified by the source and no modification is permitted. Third, robust predefined exception tables continually cleanse data in preparation for loading.
CRIS users provide unique user IDs and passwords, authenticated by system software. This authentication provides access only to the mid-tier server; no user has direct access to the warehouse data. Role files stored in the mid-tier define what data can be viewed or queried. Users can only query information they are authorized to view (restricted on a need-to-know basis). All queries are passed through the mid-tier, and result sets are passed back from the servers or marts, through the mid-tier, to users.
THE ACCESS & ANALYSIS SYSTEM
The access and analysis system is comprised of components that perform functions of meta data application, access security, auditing, performance monitoring, and data query support and analysis. High-performance tools allow users to identify search criteria and report data elements used to interrogate the warehouse. Tools provide essential ad hoc query capability, including online analytical processing (OLAP), graphics and reporting, exporting of results and query scheduling.
The mid-tier serves as a gateway between the analysis system and warehouse data system (i.e., the users and the data). It provides protocol and access authentication, and processes ad hoc queries sent by users of the access and analysis system tools.
The availability of up-to-date data and the prompt access to it is supported by the use of a statistical data collection feature built into the mid-tier. This feature evaluates statistical data to identify the schemas most frequently accessed in the warehouse and then establishes a set of data marts to respond to the most frequently requested data fields. This serves two important purposes: it promotes faster refresh times and it ensures no interruption of user access (because there are multiple, duplicated data marts).
The data marts play a significant role in the performance of the data warehouse. The overall design includes provision for the escalating use being experienced today. Yet even as users and queries grow nearly exponentially, performance has remained superb. This is living proof of the forward-thinking design and engineering that is the foundation for the success of CRIS.
User interfaces to the data warehouse offer essential ad hoc query and analysis features, as well as "flash" (Web) views for executives and managers who need a broader, overall view of financial performance. The query tool offers enormous flexibility to design simple or intricate queries, with the user defining everything from search criteria and reporting columns to when the query is processed and what the output should look like. OLAP technology gives users a powerful analysis tool that can reformat and re-sort data "on the fly," and that can be converted to charts for presentation. The Web views are presented in a readily understood format and also provide drill-down capability. In short, the query tools provide insights and answers.
CRIS was originally implemented on a limited basis, with only a handful of users allowed access to run reports. "The tool was a major breakthrough - it far exceeded our wildest expectations," Henney says. "The idea that you could go through massive amounts of data in near-real time to track and identify cost drivers compared with the weeks-old or months-old data we had been using was great. CRIS was an unqualified success." Henney and other Air Force comptrollers began referring to CRIS as their "secret weapon."
"The senior-level leadership realized that the complexity of our accounts required increased visibility and corporate vetting," he continues. As more people realized what CRIS could do in terms of analysis, they wanted access, and widespread use soon followed.
High-level officials not only wanted answers faster, they wanted to do the analysis themselves and have the tools to drill down into the data to uncover insights on the fly. The Executive Web View (EWV) was developed to provide a browser-based analysis resource for executives and managers. EWV results are drawn directly from the CRIS data warehouse. The user interface is a digital dashboard (a Web page) that allows users to control how the information is displayed. Both graphic and tabular information are displayed, and the user has complete control to select from a variety of perspectives and drill down to accounting line details.
The Air Force's senior leadership quickly learned to take advantage of EWV. Executives and managers could see, at a glance, all the information available needed to make timely and wise decisions. "It was literally saving us millions of dollars annually, making it easy to justify," says Henney.
THE PERFECT STORM
The key to measuring the success of any data warehouse can be judged using five key performance factors:
- Increasing number of users
- Increasing number of queries per user
- Decreasing query response time
- Quantifiable return on investment
- High accuracy and report quality
When all five factors are present, the data warehouse has created "the perfect storm." In the case of CRIS, the "storm" is clearly present, with the warehouse system receiving data from 19 groups, in 250 data feeds, and more than 1.5TB of data. The user and performance figures are noteworthy.
Demands on the system continue to increase, demonstrating not only user acceptance, but also increased reliance on the quality and accuracy of the system's responses. In FY 2003, more than 1.3 million queries were processed by CRIS. However, in a 70-day period between March 21 and May 31, 2004, more than 683,000 queries were processed, suggesting a trend toward more than 3 million queries in FY 2004.
"This data warehouse and its architecture have undergone extended development and extraordinary growth beginning with a best practices proof-of-concept demonstration. That ultimately became the operational foundation for this large, exceptionally successful high-performance system," says Mike Rhodes, senior data warehouse architect with Teksouth Corporation, the system engineering and integration firm for the CRIS project.
Ultimately, CRIS has demonstrated a strong return on investment for the Air Force and taxpayers by reducing demands on manpower and better focusing fiscal assets within various programs.
"We have to validate our outstanding obligations three times a year, and the numbers are in the billions of dollars," Henney relates. "CRIS gives us instant results we can view in a very user-friendly fashion. We now can spot and eliminate problems in advance instead of doing damage control. Without a doubt, CRIS has been a very wise business decision."
CRIS System by Teksouth
CRIS has demonstrated a strong return on investment for the Air Force and tax-payers by reducing demands on manpower and better focusing fiscal assets withing various programs.
Register or login for access to this item and much more
All Information Management content is archived after seven days.
Community members receive:
- All recent and archived articles
- Conference offers and updates
- A full menu of enewsletter options
- Web seminars, white papers, ebooks
Already have an account? Log In
Don't have an account? Register for Free Unlimited Access