News Stay informed about the latest enterprise technology news and product updates.

Data warehouse management success with decision support systems

We may have finally reached an era of DSS/BI for the masses. Smart IT managers and data warehouse designers must plan accordingly.

This article originally appeared on the BeyeNETWORK.

Your organization has seen the light!  Your much anticipated data warehouse/decision support system is up and running, providing total information visibility in near real-time – providing your organization real answers, not just data. Your system is integrating millions of rows of data from legacy systems, providing query results in seconds, instead of days. Both users and queries are growing exponentially, and your system is providing the insights your organization needs to make sound, best practice business decisions in a more timely fashion. Congratulations! Your problems have only just begun.

The very success of a data warehouse and decision support system breeds new requirements for its use, and generates substantial challenges that system managers must anticipate in its design and implementation. Ideally the system's architecture and design accommodate the prospect of increasing data, users, queries, and demands well beyond those initially identified. The ideal, however, is rarely accomplished in the real world. This is the story of one system where everything went right, thanks to advanced long-range planning and recognition of the need for consistent, scalable growth.

CRIS and the United States Air Force: The First Generation

  • A number of vitally important elements contribute to the success of a data warehouse/decision support system:
  • A scalable, adaptable, flexible architecture that can respond to new and modified requirements;
  • A simple, attainable implementation plan;A metadata design that accommodates growth;
  • Powerful, accurate, focused analytical tools; Performance that exceeds user expectations;
  • A user-friendly interface with a short learning curve and a simple-to-understand presentation of query and analysis results;
  • A "digital dashboard" offering decision aids for executives and managers; and
  • A quick and solid return on investment.

The U.S. Air Force needed a system to help it track its multi-billion dollar budget and provide tangible results and value to taxpayers and Congress. It implemented the Commanders' Resources Integration System (CRIS) to handle the challenge. CRIS illustrates how the initial success of a system can result in a new generation of significant challenges. Initial CRIS development focused on providing decision support for users analyzing and managing operational costs. USAF budget managers recognized there was a tremendous amount of data available to support their analysis needs, but the information was spread across disparate legacy systems, void of interconnectivity, and ill-equipped to offer standardized query results. While the data variables were understood, gathering them quickly to perform the needed analysis represented a major roadblock in the Air Force's attempts to identify its cost drivers.

U.S. Air Force managers defined CRIS' initial requirements with an eye toward technologies that offered an extended, maintainable lifecycle, while also working within tight budgetary constraints. User needs were clearly laid out: an analytical, decision support tool, along with consistent, rapid query response times, accessed from a stand-alone desktop environment. Teksouth Corporation – the firm performing the design, system engineering, and implementation for CRIS – chose a "scale-out" architecture, providing a shared environment, but with separate, independent modules. The system architect specified scalability in the design for three reasons. "First, it returned value early in the development process. Second, it afforded the flexibility to make important or needed changes, continually and often. Third it allowed the leveraging of Moore’s Law. Knowing that CPU speed is doubling roughly every 18 months, the ability to 'bolt on' additional hardware and take advantage of future CPU speed as use grew was necessary.  The architecture design reduced startup costs by allowing incremental development.

CRIS' design relies on a three-tier system that includes: (1) a back-end data warehouse with multiple data marts, (2) a middle-tier administrative server, and (3) a client access and analysis system that utilizes an on-line analytical processing (OLAP) capability. The system applications perform data acquisition, business rule application, storage and data distribution. The modular design maximizes available options for balancing performance, maintenance costs, contingencies, and capability. This mature architecture is metadata-driven, keeping the cost of modifications low, and ensuring the consistency and quality of data and processes. A key feature in CRIS' design is metadata support for multiple representations of data schemas and an intelligent middle-tier server that selects the most efficient schema for each query.

CRIS processing takes place at a central location, which means extraction, transformation, and loading (ETL) functions are performed one time, in one place, for each business cycle. The processes can load multiple databases simultaneously, in parallel. This greatly enhances efficiency, an absolute necessity for a data warehouse that seeks to make large data sets available around the globe in near real-time. The centralized configuration raises the additional challenge of obtaining huge amounts of data, in short periods of time, from dozens of sites and sources. Data is obtained from these various sites using a high speed, secure data transfer tool, IQ Swift®. It securely transfers data to the system's staging components 20 to 30 times faster than most commonly used file transfer tools.

Success – Then Challenges

CRIS was implemented in 2001 for use by a small group of users within the Air Force, but that changed almost immediately. As one senior USAF representative noted, "CRIS was a major breakthrough – it far exceeded expectations." In fact, CRIS was such a success it soon became known among comptrollers as their "secret weapon." Word began to spread rapidly. Others throughout the organization wanted access to the system, as they became aware of how CRIS could help them do their jobs more effectively. It wasn't long until USAF executives realized the potential. But the very speed that initially attracted attention to CRIS became a potential liability, as its capacity to quickly produce results became important to more people. Executives and managers not only wanted answers fast, they wanted to do some of the analysis themselves. They wanted to have the tools drill down into the data to uncover insights virtually "on the fly." The success of the system brought challenges: a rapidly increasing user community, increasing processing demands, and higher visibility.

            In response, Executive Web Views (EWV) was developed. This CRIS enhancement provides a browser-based review and analysis tool for Air Force executives and managers, enabling them to access high-level financial information related primarily to operations and maintenance expenditures, and provide comparison to budget projections. EWV has subsequently been expanded to support other programs, organizations, and special interest items. EWV pulls its information from the CRIS data warehouse. The interface is essentially a "digital dashboard" that allows users to control how data is displayed. Perspectives (or points of view) display financial information at increasing levels of detail. Drill downs provide "snap shot" viewing of accounting details down to the transaction level. Graphs provide visual representation of data, measured against budget plans and prior year performance.

Measuring Success and Challenges

In three years CRIS has grown from a relatively small system (20 users, 2,000 queries/month) to a robust, terabyte-size data store, incorporating 250 data feeds in 19 groups. Today CRIS is supporting up 11,000 people submitting up to 400,000+ ad hoc queries per month, and the system is expected to double in size by 2007. Yet the true measure of the system's capability is demonstrated by the fact that despite the rapid increase in users and queries, response times have actually fallen.

The average response time is around 10 seconds; almost two-thirds of queries are processed and result sets delivered in less than five seconds. CRIS' design, engineering, and implementation has not only achieved an impressive combination of performance and features, but has kept pace with the challenges of increased requirements and users. The forward-thinking engineering of design represents a major success for this system, one that has enabled modifications and enhancements to be integrated while continuing to meet increasing demand and user expectations.

Dig Deeper on Data warehouse software

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.