Analytical Processing As Executive Decision Support At The University Of Missouri System |-------------------------------------| | Paper presented at CAUSE92 | | December 1-4, 1992, Dallas, Texas | |-------------------------------------| ANALYTICAL PROCESSING AS EXECUTIVE DECISION SUPPORT AT THE UNIVERSITY OF MISSOURI SYSTEM Dr. Steve Chatman & Robert Mullen University of Missouri System Columbia, Missouri ABSTRACT The 1990s will see the rise of analytical processing based on select data elements warehoused as archival, integrated, and subject oriented relational records. LAN-based software will support managers and analysts in quickly creating "programs" of linked analytical and reporting tools dynamically exchanging data. Such a system is nearing the end of a two-year pilot project at the four-campus University of Missouri System. The project's goal is to produce a comprehensive warehouse of student, personnel, financial, and course data accessed by Metaphor's DIS software to support reporting and executive decision making. The project represents a fundamental change in the relationship among University System computing, System administrative offices, and data custodians for the independent campus-based transaction systems. The paper will place analytical processing in the context of executive decision support, report on the pilot of the U/IDS project, and will discuss DIS reporting and ad hoc capabilities. As the University of Missouri approaches the end of its two-year pilot project to produce an executive decision support system for ad hoc queries, decision support, and reporting supported by an analytical processing environment, there is much that other institutions can learn from the University's experience. Among the lessons to be learned are those owing to differences between collegiate and corporate environments and problems associated with trying to move too far too fast. But more importantly, higher education faces many challenges that should force it to reconsider prior notions of executive information and decision support. At the national level, higher education faces poor public perception, severe budget deficits, ebbing demand, and growing gloom in the industry.[1] Locally, the demands can be far more challenging. In Missouri, the Coordinating Board for Higher Education has adopted a series of goal statements, developed by a task force composed principally of governing board chairs, that among other standards: establish institutional categories based on admission criteria, set the number of hours faculty will teach, control administrative growth, require annual reporting of outcomes assessment results, establish targeted graduation rates, and define a minimum number of degrees expected by every program with those programs not meeting the standard to be dropped. When faced with these challenges, luxuries that executives cannot afford include slow response and inflexible regular reports. Instead, higher education executives need a system designed to respond to specific concerns and policy issues in the short time frame in which they must make decisions. Management information systems are now reaching an evolutionary point where decision support professionals can contribute to the decision making process in a manner and within a time frame that should improve the quality of decisions made.[2] In general, this type of information handling has been called analytical processing. This paper will discuss analytical processing as a logical next evolutionary step in decision support that was anticipated twenty years ago by leading institutional researchers. Second, the University Integrated Data System (U/IDS) project representing an agreement between the University of Missouri and IBM will be described. Of special emphasis will be discussion of the two major obstacles to the development of the analytical processing systems, the political environment and the problems inherent in integrating the on-line transaction systems of a university. Next, the paper will contrast the steps required in ad hoc reporting within current systems and within analytical processing while emphasizing gains in efficiency, quality and clarity in communication. The presentation will end with a brief slide show demonstrating the capabilities of the analytical processing system using DIS software and a discussion of the status of the project as we near the end of the two-year pilot. The discussion will emphasize lessons learned. Analytical Processing Analytical processing describes an environment where warehoused data is addressed through software that integrates retrieval, analysis and reporting functions.[3] For analytical processing to occur, the warehoused data and integrated software are required. Warehoused data is time variant data, integrated through unifying organizational structures. But as importantly, it is uniquely designed to support standard reporting as well as ad hoc requirements. Warehoused data in an analytical processing environment can be identified by three features. First, a warehouse of data is a collection of: data extracts, pre- processed conditional values, and pre-computed aggregates; much like frozen files. Its nature is one of values frozen in time and as such it is time variant instead of current. Second, the time variant information is integrated across transaction systems to support linking of data from each system: financial, personnel, and student information; through use of a common organizational structure. This is the first feature that distinguishes the data warehouse from a collection of frozen records. Third, the data resides in a relational environment that is designed to support reporting and ad hoc requirements as opposed to simply tracking transactions. The features of integrated data and a custom relational design to support reporting and analysis distinguish data warehouses from collections of frozen records, whether stored in relational files or as flat files. To help clarify the distinctions, imagine that you want to build a warehouse of payroll and personnel information and student information. The first challenge is to identify the links that exist between the systems and to clearly establish them through and integrated structure. Three of the links that naturally exist are: (1) that many students are also employees; (2) that faculty teaching courses are also in the personnel records; and (3) that there are organization structures responsible for students, instructional activities, and employees. In a warehoused system, these features are linked through establishing a common organizational unit, like department, within which accumulated values can be reasonably linked. The second challenge is to build a relational system that uses the common structure to guide design. The data warehouse will not be used to enroll students or to pay faculty. Because it need not support on-line transactions, warehouse design can be built to support reporting and analysis. Once the warehouse is built, data are loaded at points in time consistent with official census reporting. Two other distinctions between a collection of frozen files and a data warehouse are the inclusion of elements to specifically support reporting and analysis and elements that are aggregate values. Elements that would be regularly used in reporting, but that are not individual transaction elements, and frequently used aggregates are computed and entered into the warehouse as it is created. For example, much enrollment reporting requires the determination of whether a student is a first-time freshmen according to Federal reporting definitions. Determining whether a student is a first-time freshmen requires examination of the relationship between several individual elements in multiple time periods. In building a data warehouse, the necessary processing is done once and a new element, "first-time freshmen," is established. Additionally, the number of first-time freshmen in a college or at a campus is an often reported value and is therefore compiled and stored as an aggregate. This preprocessing supports reporting and analysis applications. In addition to requiring a data warehouse, integrated software for retrieval, analysis and reporting is also required. If the system is to be flexible enough to support the ad hoc and decision support needs of institutional executives, then the steps between retrieval and reporting must be accomplished as smoothly and as flexibly as possible.[4] Follow- up queries often ask for the same analysis to be repeated at different levels of a variable or with different restrictions. Integrated software allows the changes to be made, to the retrieval component in this case, and for the report document to be reproduced with the alterations in a nearly transparent and automatic manner. For example, a recent case involved standards for admission. The report produced to support decision making was a spreadsheet combining a variety of analyses for two different cohort groups. As the executives asked for the same information using different admission standards, the conditional statements were altered and the report was reproduced with very minor manual changes. The necessity of warehoused data and integrated software is made clearer when analytical processing is viewed within historic context as an evolutionary advancement. As the batch processing systems of the 1960s gave way to the transaction systems of the 1970s one trend remained true throughout: reporting and ad hoc analysis were always afterthoughts. It was recognized that transactional systems could support enumerative reporting and various reports were developed as management information systems or in response to government mandated requirements. Following the PC/4GL processing revolution of the 1980s, it is now possible to view decision support activities in a new light. Partly because reporting and ad hoc analysis were viewed as afterthoughts and partly due to the limitations of software and hardware in the past, there have been several information processing trends and developments on campuses that present challenges to building an analytical processing environment. First, transaction systems have developed as essentially "private databases," where access is controlled by custodians who often behave like greedy owners but whose motivations are pure: there are issues of data integrity and privacy that must be protected, data elements and their interrelationships may be difficult to understand, and direct access is potentially dangerous and disruptive. In contrast, data warehouses are typically read-only collections of agreed upon and understood elements with English-like values. Second, transaction systems have often been developed independently, following the de facto organizational structures necessary to support the transactions. For example, student information systems often follow academic structures that are different from the administrative structures existing in financial and personnel systems. These organizational differences hinder integrating information for cost studies and other purposes. In addition, the independent transactions systems are often supported by different data base software and accessed through different programming languages rather than being integrated and accessed through a common language. Third, the necessity to produce consistent reports that support historical analysis requires frozen files that are fragile. Occasionally, entire years are lost. The read-only access to warehoused data and the routine maintenance of centrally supported systems help to prevent similar problems in analytical processing. In addition, ad hoc projects often demand that the databases be reestablished from frozen files before processing can occur. This rebuilding of the databases is expensive and the space requirements often restrict normal and ongoing operations. Fourth, there has been a proliferation of databases. Many offices and individuals now maintain databases that bear only an indirect relationship to university records. They do this because the software they want to use requires it, it is a problem to quickly get the information from computing services, they wish to merge data from different transaction systems, or they want to add data that is not in the university's records. If the data warehouse has been well constructed, then user data needs have been anticipated. Also, if the software used gives users the control and flexibility they have experienced when working with microcomputer software, or if access to their preferred software is transparent, then the problem is controlled. Fifth, the institutional research function has become fragmented. While not necessarily a problem or even a concern, inconsistency of reporting produces conflict that is made worse by competition among units for scarce resources. In an analytical processing environment, analysis relies on a shared data source thereby limiting arguments over who has the right numbers. Differences of judgement based on the data continue but the discussion can focus on the issues, not on data. In all these cases, analytical processing helps to alleviate information handling problems. There is, however, another problem that exists in higher education decision support that the ad hoc facilities of an analytical processing system can help to alleviate. That problem results from the myth of executive information systems as decision support tools. It has often been suggested that the information needs of university executives can be anticipated, and if anticipated, can be programmed and produced on a regular basis. But, beyond the most rudimentary aggregates, packaged executive information systems are of little use in higher education for three reasons. The first is that university cycles are typically no shorter than semesters and an electronic system to convey new information rapidly is superfluous. Second, the information needs that can be confidently anticipated are relatively few in number and require no new reporting capabilities. Third, systems of packaged aggregates are unlikely to help the executive faced with issues like: poor public perception; severe budget deficits; and involved and active governing board members, legislators, and coordinating boards. The reasons that executive information systems, and management information systems in general, are of limited value in higher education are that the information contained in them very seldom directly and completely answer any specific need for information as policy is being considered. Also, the systems are not conducive to follow-up questions. It is the position of this paper that it is time to rethink the management information system and decision support system paradigm. This position was actually anticipated by institutional researchers over twenty years ago. The concept of an analytical information system is appealing. A system in which accurate, consistent, and complete sets of basic data are produced automatically, stored conveniently, interrelated readily, and available as needed is certainly to be sought by any college or university.[5] The reason that an analytical processing system supporting ad hoc activities is a better solution is that there is no value inherent in data. As stated by Sandin, From the point of view of educational administration, the general requirement of an information system is that it should achieve a flow of information in the forms and at the times needed for responsible decision-making. Decision making is an event that occurs at a determinate point in time. It is in the moment of decision that information is relevant. If the time schedule for the production of information is not in phase with the time schedule for decision making, and information system of even the finest design will have no influence on planning.[6] Or more succinctly stated by McCorkle, "Data become informative when we have specific policy questions that need illumination and resolution."[7] It is the act of decision making that gives information value and that value is directly related to the precision with which it addresses the informational needs of the executive. In an analytical processing environment, decision support is now at a position where it can address questions related to issues with true precision and can readily support follow-up questions based on the prior response. Through this focused, iterative interaction, the value of information is greatly increased. It was to this end that the University of Missouri joined with IBM in a joint agreement to explore the possibility of applying Metaphor's Data Interpretive System (DIS) to executive decision support. Metaphor is a wholly-owned subsidiary of IBM but will soon have independent operational responsibility and will market DIS under the Metaphor brand name. University/Integrated Data System (U/IDS) The principal objectives of the joint agreement were to explore the application of DIS to higher education administration information needs and to examine the possibility of linking DIS and IBM's Executive Decisions software. Toward this end, IBM contributed software and hardware valued at over $250,000 and the University of Missouri contributed two full-time computer analysts, computing charges, and microcomputer equipment and upgrades. The two-year pilot is tentatively scheduled to end in May of 1993. The project was to build an analytical processing system around a warehouse of data identified by the institutional research staffs of the University of Missouri campuses, system office personnel, and the custodians of each system. The review of these elements began in December of 1989. Almost immediately there was resistance to the project. The standard question was "What are you going to do with the information?" What was seldom understood was that if those involved could anticipate all the specific applications then the data warehouse would not be needed. The process of gaining support for the creation of a data warehouse was one of gaining acceptance and executive sponsorship and working within the university environment to address the concerns of interested parties. Gaining executive support required an understanding of the management philosophy of the organization and styles of senior administrators. In particular, it was stressed that information was an asset to decision making and planning and was therefore an institutional resource like the faculty or laboratories. The difference in speed and quality of response to their information needs when extracts existed versus when the steps required use of the current procedure to create an extract, showed senior administrators the potential value of a data warehouse. Response took days when the extract existed and weeks when it did not. Threats to the survival of the project were faced from the outset, first from campus institutional researchers then from campus custodians and faculty. To respond to access issues and to insure that data were comparable, accurate and complete, a series of data applications groups were formed. These groups were composed of the data custodians who interacted with the project team to review data element selection for likely value and to identify data inconsistencies. Even though the University centrally maintained corporate systems, each has been modified over time by campus persons and different treatment was often required to produce comparable information. Campuses also differed in the completeness of data do to local practices. The data applications groups worked with the project team of system institutional researchers, campus institutional research representatives, and central computing support personnel. The project team was the key working group and reported to a management group of vice presidents, respective associate and assistant vice presidents, and chancellors. The management group was responsible for review and evaluation functions. In addition to these administrative groups, an intercampus faculty advisory committee was formed to ensure that data were "properly used" by the administration and to protect the interests of faculty. Faculty interest was sparked by knowledge that information linking payroll records and course instructional assignments would be part of the system. The warehouse was to be a collection of student information and course data, student financial aid data, payroll and personnel data, and financial accounting data. Student and course data were to be captured at the semester census point. The student data would be supplemented by degree completion and off-schedule activities on an annual basis. Student financial aid data were to be transferred annually in October. Course section instructor records were to be loaded four weeks after the semester enrollment census dates. Payroll and personnel data were to be transferred at the personnel census, October 31. Lastly, financial and accounting records were to be captured in October for the prior fiscal year. These steps were required to form the data warehouse and were necessary but independent of the development of an analytical processing environment. Through discussion with local IBM personnel, the Assistant Vice President for Information Technology began to explore the possibility of using an integrated software product, Metaphor's DIS, a product with a long and successful history in the consumer package goods industry. The joint agreement between IBM and the University of Missouri was subsequently formed and the two-year pilot project officially began in May of 1991. DIS uses a local area network (LAN) comprised of IBM compatible PS/2 computers. The DIS LAN consists of a primary file server, a database gateway server, a communications server, and at least one workstation. The DIS database can reside on the LAN's file server, or it may be located on a mainframe. If the database is on the mainframe, the communications server needs to be linked to the mainframe. At the University of Missouri, the data is located on both the LAN file server as well as on the mainframe. When a workstation requests data, the database gateway server will send requests to either the mainframe or the LAN file server. The retrieved data will then be sent to the requesting workstation. It was decided to build the financial reporting component first then move on to the student information system, payroll and personnel system, and the financial aid. After two years, most of the extract programs have been written and DIS prototype structures have been created for the financial, student and course, and the payroll and personnel data. The prototype for financial aid is also being completed. Issues still unresolved include the prohibitive time required for some operations to run and whether alternative front end processors and other integrated systems might better fit this higher education environment. One issue that has been resolved is that an analytical processing environment is a good solution for executive decision support through ad hoc analysis and reporting. Advantages of Analytical Processing Analytical processing offers a much more efficient way of responding to ad hoc queries in an executive decision support environment. Fewer steps are required, fewer people are needed, the skills of the people participating in the response are less computer specific, and computing costs are greatly reduced. Responding to typical queries about students using previous processes at the University of Missouri required at least eight steps. First, analysts would determine the data needed to respond to the question and any likely follow-up questions. Second, a request would be made to the central computing staff for the data needed. Third, the central computing staff and institutional research analysts discussed data needed with the custodians to determine its validity and availability. Fourth, central computing staff would write or modify programs to recover data from the transaction systems. Fifth, registrars and registrar staff members would run the programs and create flat files. Sixth, the data would be made available to the institutional research staff who would process the data using high level statistical and reporting languages, correcting differences in data element treatments by campus. Seventh, the results would be transferred to independent spreadsheets, graphs, and word processor files. And eighth, a document would be prepared in response to the initial query and returned to the requester. Follow-up questions would require steps six through eight to be repeated. In the analytical processing environment only two steps are required. First, analysts determine the data needed for the question and likely follow-up questions, directly analyze the data using integrated spreadsheets, graphs, and word processor files. Second, a report document is prepared in response to the initial query and the report is sent to the requester. Any follow-ups questions require that these two steps be repeated although most of the first step will be the same the second time. This fundamental change in procedure reduces response time from a minimum of one or two weeks to one or two days. The first gain is time. The second is in better use of the skills of the people participating in the response and a reduction in the number of people required to form a response. Ad hoc response under the existing system requires personnel with three different areas of expertise. First, the institutional research analysts need to be versed in higher-level languages and microcomputer applications, and their formal training is in research design and statistics. Second, the central computing staff are expert in database processes and use lower-level languages. Third, the expertise of institutional personnel in the area or areas related to the inquiry, like the registrar and the registrar's staff, are required. Because the response is made within a university system environment of four campuses, a student information system-based query will usually involve a minimum of 10 to 12 people and require 5 to 10 person-days. In an analytical processing environment, only one or two people need be involved in the formation of any one response. A third gain is measured by lower computing costs. Day-to-day computing costs are also greatly reduced for the campuses in an analytical processing environment. For example, student information system-based queries frequently require a re-creation of prior databases for short periods. The total computing costs for the university system can easily reach $1,000 just to re-create the database and extract selected data. In the analytical processing system, distributed computing greatly reduces billable costs. A more complicated query requiring information from more that one operating system quickly expands the number of personnel involved and greatly inflates costs. Efficiency gains in an analytical processing environment are related most strongly to the existence of the data warehouse. In the creation of the data warehouse, discussion of unique element characteristics by campus and has already occurred. Those differences have been resolved, often through the creation of elements that exist only in the warehouse. In addition, the data have already been delivered and are ready to support analysis. While the data warehouse is of fundamental importance, integrated, object-oriented software like DIS greatly facilitates analysis and releases the potential of the data to support decision making. Because DIS software applications are straight-forward, object- oriented application tools, minimal special training is required and no training in computer programming is needed. This ease of use means that statisticians, researchers, accounting analysts, and others can directly apply their expertise in response to the question without the need for translation. In addition, the computing resource requirements are distributed, reducing the burden and cost of using central computers and relying instead on microcomputers for much of the intensive processing. It should be noted, that although DIS is easy to use, it is probably unrealistic to expect most current institutional executives to form their own queries. The use of an analytical processing system in a four campus university system produces many gains in the quality of analysis and communication with campuses. First, the data are shared among all participants. There is only one set of data to use, so arguments over whose numbers are best are limited to questions of data treatment and interpretation. Second, there are no black boxes. Data retrieval and analysis is an open process. Users do not need to be programmers to know exactly how data were treated in the analysis. For example, there are occasionally differences between program specifications and the actual program actions that can not be deduced by reading specifications. If you do not see the code then you do not know exactly what is occurring. You only know what is supposed to occur. Concerned others can also follow the treatment of data precisely. For example, campus institutional researchers can see exactly how the central offices manipulated their data. Third, data from various transaction systems have been integrated and the integration supports more complex, useful, and valid analyses. Fourth, costs are supported by central administration instead of by the campuses. While not a direct gain in quality, reduced costs encourage campus participation and interaction with the system, thereby indirectly improving the quality and completeness of the data and of the work done. Fifth, the data can be made available to a wider group of professionals with diverse talents. And sixth, the shared warehouse prevents the need for a proliferation of extract files and small databases. Conclusion and Discussion The challenges facing institutional executives require more timely information, treated in a more sophisticated manner, and focused on the specific issues at hand. Executives and external constituencies have a better understanding of the capabilities of computing and are not inclined to wait patiently, and postpone action, while a response is formed over two or three months. They expect the response in a timely fashion. If the response can be made within the decision-making time frame, then the data becomes information and has value. In fact, the executive may now see reason to make a second request to explore new possibilities. Through this quick interactive exchange, decision making is improved. Analytical processing is only new in the sense that old ideas about analytical systems can now be supported due to hardware and software developments. An analytical processing model of executive decision support was anticipated by Sandin writing in 1977. But after many disappointing attempts, I myself have come to the conclusion that, at least for the short run, we are not well served by the conventional wisdom about total information system implementation in higher education. I believe that development of rational decision making in colleges and universities might be better served by a reduction of our aspirations for implementation of an information system and by concentration of our efforts on a limited series of steps which fall short of comprehensive design ... What can be achieved in the short run are the following tasks: (1) identification of the data elements that are the necessary basis for a comprehensive information system if such a system could be established; and (2) installation of procedures for collecting, maintaining, storing, and retrieving these raw and unanalyzed data. These are the first steps that must be taken in information system development. Programs for processing basic data and transforming then into usable information should then be created on an ad hoc basis in response to specific information requests from decision makers. ... The information system that will result from this strategy will not be a system, in the strict sense, at all. It will be a patchwork of reports derived from a unified data bank which can be readily and accurately accessed. But the information that flows from such a processing system, though a compromise of information system theory, will be of direct benefit to the quality of planning and management in higher education, for it will be generated in direct response to management demands.[8] Sandin was exactly right. Executive decision support, specifically, and management information systems, generally, are able to correctly anticipate only the crudest of measures. They can also function well within closed, uniform processes like budget development. In contrast, the process of decision making requires more focused, tailored analysis. Canned programs can no more anticipate the precise analysis to support future decision making than decision makers can precisely anticipate the need for future support. It is executive decision making that must become better understood because it is the decision making process that allows data to become informative. When the data directly applies to the situational demands of the executive and is conveyed within the time frame required of the decision then data and data handlers become informative and valuable. NOTES [1]Shafer, B.S. and Coate, L.E., "Benchmarking in Higher Education," Business Officer, November 1992, p. 28. [2]Weissman, R.F.E., "Toward 2000: Institutional Research and the Next Generation of Campus Computing," in Building Bridges for the Twenty-First Century: General Session Presentations of the 31st Annual Forum, San Francisco, May 26-29, 1991, by the Association for Institutional Research, p. 13. [3]Inmon, W.H., "Building the Perfect Beast." Information Executive, Spring 1991, p. 33. Also Miselis, K.L., "Organizing for Information Resource Management." Organizing Effective Institutional Research Offices, New Directions for Institutional Research, 66 1990, p. 60. [4]Glover, R.H., "Decision Support/Executive Systems at the University of Hartford." Cause/Effect, 12 1989, p. 16. [5]Saupe, J.L., "Collecting and Utilizing Basic Data." In P.L. Dressel (Ed.) Institutional Research in the University: A Handbook (San Francisco, Jossey-Bass, 1971) p. 98. [6]Sandin, R.T., "Information Systems and Educational Judgment." Appraising Information Needs of Decision Makers, New Directions for Institutional Research, 15 1977, p. 20. [7]McCorkle, C.O., "Information for Institutional Decision Making." Appraising Information Needs of Decision Makers, New Directions for Institutional Research, 15 1977, p. 3. [8]Sandin, R. T. pp. 25-26. REFERENCES Glover, R.H., "Decision Support/Executive Systems at the University of Hartford." Cause/Effect, 12 1989, p. 16. Inmon, W.H., "Building the Perfect Beast," Information Executive, Spring 1991, pp. 33-35. McCorkle, C.O., "Information for Institutional Decision Making." Appraising Information Needs of Decision Makers, New Directions for Institutional Research, 15 1977, pp. 1-9. Miselis, K.L., "Organizing for Information Resource Management." Organizing Effective Institutional Research Offices, New Directions for Institutional Research, 66 1990, pp. 59-70. Sandin, R.T., "Information Systems and Educational Judgment." Appraising Information Needs of Decision Makers, New Directions for Institutional Research, 15 1977, pp. 19-28. Saupe, J.L., "Collecting and Utilizing Basic Data." In P.L. Dressel (Ed.) Institutional Research in the University: A Handbook. San Francisco, Jossey-Bass, 1971. pp. 53-99. Shafer, B.S. and Coate, L.E., "Benchmarking in Higher Education," Business Officer, November 1992, p. 28-35. Weissman, R.F.E., "Toward 2000: Institutional Research and the Next Generation of Campus Computing," in Building Bridges for the Twenty-First Century: General Session Presentations of the 31st Annual Forum, San Francisco, May 26- 29, 1991, by the Association for Institutional Research, 7-14.