The Electronic Factbook: The Foundation For A University Wide Decision Support System |-------------------------------------| | Paper presented at CAUSE92 | | December 1-4, 1992, Dallas, Texas | |-------------------------------------| THE ELECTRONIC FACTBOOK THE FOUNDATION FOR A UNIVERSITY WIDE DECISION SUPPORT SYSTEM Cynthia M. Hadden, Director Barbara F. Evans, Assistant Director Administrative Information Systems Louisiana State University ABSTRACT Reporting general institutional information on students, employees, finances, and facilities is a task that all universities perform. At Louisiana State University an "electronic factbook" is being developed as the first phase of a University-wide decision-support system. This system will attempt to address the routing point-of-time or period-of- time queries for information and will lay the foundation for measuring institutional effectiveness, outcome assessment, and strategic planning. To accomplish this goal, LSU staff will exploit the power and flexibility of DB2 in building a unified information base from a complex environment where operational data are stored in disparate sources, formats, and versions. ------------------------------------------------------------------------ Over the past decade, LSU Administrative Information Systems and administrative departments have worked together to build operational data systems that meet the University's day to day needs. At the present time, we have 45 production systems to manage administrative functions such as payroll, personnel, purchasing, accounting, student records, admissions, and many others. High performance transaction processing and management of large data bases is well documented in theory and practice at the University. Although most of our production systems are heavily integrated, the data is generally associated with the applications that create it initially. Within this local context, the data is well understood and meaningful. Increasingly, however, there is a need to view all this data more comprehensively, to see it as a totality of information, understood and integrated at the enterprise level. The University is faced with competitive pressures, and has an increasing need for better and faster decisions based on accurate, timely, broad-based knowledge. To address the operational needs of the University, we have built a multi-faceted complex environment with many data processing activities, a wide variety of applications, data types, access techniques and storage methods. This operational data may be in standard flat file format on either disk or tape, in key sequenced VSAM files, in IMS hierarchical databases, or in relational DB2 tables. Additionally, we also have an assortment of PC and LAN databases set up in Dbase, Rbase, OS/2 Data Manager and others. This complex environment, generated by growth and the evolution of technology, has resulted in making access to operational data awkward, generally requiring a non-user friendly programming language and requiring the information worker to know details about the data's physical location, its storage and its format in order to get at it. Our operational data often exists in sequential files or hierarchical databases, in a format that cannot easily support quick queries. What would appear to be a simple query can generally involve data processing staff and business analysts to assemble the answers resulting in decreased productivity, higher cost, and lost opportunity. A related problem exists with data being used by high volume real-time production systems. Unplanned or poorly structured querying of the data can degrade performance of the primary transactions. Even when we may know the location of the data, the form it is in, and know how to access it, there can be problems. Historical changes in data values and data that is algorithmically derived makes it sometimes difficult to understand or interpret operational data correctly. An example of this situation is the fact that there used to be four sex codes for students incorporating marital status into the sex code. When the database systems we now operate were developed, this code was split into two separate fields. The LSU Council of Vice-Chancellors and the LSU Office of Budget and Planning have challenged AIS to develop a decision support system which transcends the complexity of our operational environment. Our task is to establish a comprehensive system to provide aggregate and detail information for effective decision making regarding students, personnel, finances and facilities. With a project dubbed the 'Electronic Factbook' we intend to build the foundation of a long range plan for a University wide decision support system. Additionally, we are increasingly requested to provide information in a distributed environment. With the rapid growth of PC's, and local area networks and the increasing sophistication of PC software, we must be able to provide this information to users on a platform where they are most comfortable. Our goal, then, is to integrate and consolidate our operational data from disparate sources, formats, and versions into a unified information base. Once this has been accomplished, the results, with the proper tools, can be distributed to the desired environment for the most effective use in decision making. Given our complex environment, and our ambitious goals, our vision was to develop a system in which we could provide seamless access to information regardless of format or hardware platform in both a traditional mainframe and rapidly growing PC environment. This information would be presented by means of a common user interface with the point and click feel of Windows or Presentation Manager, graphical displays, hotkeys, and drill down menus. This system would be fast, reliable, require little user training and otherwise be transparent to the user. Definitions of decision support systems abound, but formally can be described as interactive computer based system designed to help decision makers use data and models to solve unstructured problems. An executive information system on the other hand, focuses, organizes, aggregates and extracts essential information from extensive data sources delivering that information in formats useful to top administrators. These systems follow a step ladder of complexity, beginning with electronic delivery of reports, to customized reports (new perspective of old data), to variance analysis, to what-if processing and finally to modeling, where not only can the data be changed, but the relationships among the data. In response to our challenge to build a decision support system, we began by employing Entity Relationship Modeling, which is one of our standards of system design, regardless of application. During this phase of application development, the business process is analyzed and the 'what', not the 'how', is defined. The results of these modelling sessions are the entities that will ultimately be the building blocks of the application. We went into the sessions, expecting to model processes of students, finances, personnel, and facilities. We found, however, that this project was different than modelling a business application. It was much more abstract. What we found were that our entities were information sets and source data about these four areas of focus. About the same time as we completed our modeling, IBM announced the Information Warehouse framework. This framework is not a product, but rather an architecture for building the type of system we are discussing. It defines a set of database management systems, interfaces and tools to deliver reliable, timely, accurate information to those who need it. What excited us was that the result of our modeling efforts were in direct accordance with IBM's Information Warehouse concept and enforced our belief that we are on the right track. One of the things the sessions pointed out was there are two parts to our model. On one end is the raw source data generated at the operational level and on the other there are the information sets that are of interest for decision making. So, we began investigating tools and products to help us get from one end to the other. In our research, we found that these products generally fell into three categories: 1) One set of products, generally at the PC level would take our high end information sets and allow us to display them in a 'glitzy' manner employing decision support capabilities. 2) Another set of products would provide for direct access to disparate data sources. 3) 'Frontware' products that did neither, but allowed us to develop front end graphical displays to existing mainframe applications. In the area of decision support software for the PC environment, there are many. Seventeen products were listed in a March issue of PC Week, ranging in price from $100 to $15,000 with about that much diversity of capability and features. We investigated some of these, such as IBM's Personal AS, Pilot's Lightship, and Channel Computing's Forest and Trees. Most of these products claim access to information on a variety of PC data formats and are impressive in their presentation and flexibility. But, they have their drawbacks as well in that some either expect data to be extracted to the PC environment or require certain modes such as DOS/Windows. In some cases, host data can be accessed, but requires the purchase of additional gateway software. Since one of our primary concerns was data access, we investigated products that would allow for the seamless data access we desire. A product from Information Builders, Inc. called Enterprise Data Access (EDA/SQL) claims to provides the ability to find, retrieve, join, and update data on any database, regardless of underlying structure (DB2, IMS, Oracle, Rdb, etc.) located on any hardware platform. This product is sort of a universal query translator/router. It translates in-house front end data access applications into SQL queries and then locates the appropriate database and routes the query. Supposedly, it can be installed on all types of platforms on our network, from the most remote PC to the mainframe, letting us query any database stored in any location. It sounds like just the product we need to solve our needs. But, with a price of approximately $200,000 with a 15% maintenance fee, the product no longer seemed so magical. Additionally, there was no indication of the impact conflicting direct access would have on our production systems, or how it would solve the problem of currency. Other products were investigated to allow us to provide a graphical interfaces to the information we wanted to display. A product from the Easel Corporation would allow us to build intuitive graphical interfaces to our host based applications. One of their products allowed for building templates to access and graphically display information from reports run on the mainframe and from that charts, graphs and variance analysis could be produced. This family of 'frontware' products, which were impressive in presentation, required the purchase of several modules, and introduced yet another programming language and compiled code that would have to be maintained by a member of the data processing staff. As one can see, when we start envisioning a system as ambitious as the one first described, it is possible to access our enterprise data utilizing universal query translators/routers accessing data regardless of platform or format. It is possible to graphically display and multi- media format the results. It is possible to find software that will allow analysis and modelling. Much of what we would like to incorporate/utilize in the decision support system we envisioned is attainable with significant outlays of funds for software in the investment of support technology. But, technological possibilities do not address financial restraints facing our efforts. With this project, we found we must separate what is idealistically desired and what can be practically realized. More importantly, none of the products reviewed and none of the vendors interviewed addressed the fundamental problem of building our information base. We found we had to build the integrated, consolidated information base before we could address the issue of presentation or on-line access. Using the framework that we have with our existing IBM products, and IBM's structure for including new and future products we are beginning to lay the foundation for our information base with the tools we have on hand, DB2, QMF and SAS. Building our information base involves the selective duplication of desired operational data, subsetting it into that which is of value for informational purposes and needed for fast or shared querying. This data may go through transformation from its original format to a single common format and then this disparate, but related data, will be merged into a common information store, during which it may also be enhanced. LSU is proud to announce that we have entered into a jointly defined effort with IBM ACIS to accomplish our goals and develop the Electronic Factbook. Our development efforts intend to demonstrate the feasibility of designing the foundation for a cost-effective, multi- platform decision support application. So what is the electronic fact book and how do we begin? The Electronic Factbook, is a canned set of 100 or more queries, or fact sheets, which will be used by University administrators to support decision making efforts and which will be used by the University community at large to describe the current state of the University. The factbook is the upper section of the decision support pyramid. The project entails identifying and building these 100 or more queries and building the infrastructure to support the queries. It is anticipated that the Factbook queries will satisfy between 60-80% of the requests for information that administration is processing today. The infrastructure will serve as a foundation for a comprehensive, dynamic decision support system. Our development tasks are divided into two distinct tasks or phases: 1.Pulling together data from a variety of sources, transforming the data into information and storing this information in a standardized information base that is accessible by an easy-to-use access language. This is the labor intensive phase of the task and the primary cost is man-hours and commitment. 2.Providing access to the information base on a variety of platforms (both mainframe and PC). This is less labor intensive, and the cost are in hardware and software. It is this phase that will appear glitzy and slick and satisfy a broader spectrum of users. The first phase of the project is absolutely essential to a successful implementation of the fact book and to any future attempts at a decision support system. The first phase must be done before the second if the factbook is successful in the long run. Many vendors would indicate otherwise. Our first step in pulling together data from disparate sources was to establish DB2 as the standard of the unified information base. We established this information base separate and apart from the operational data of the University for several reasons: 1. With DB2, we are able to easily meet our objective to provide a common data access language that can be used in a de-centralized manner - that language being SQL. SQL can be used directly to access data through facilities such as QMF on the mainframe, Query Manager and Forest & Trees on the PC. It can also be embedded into languages such as SAS and COBOL. In the fact book, we envision a standard set of queries which will be written in SQL. 2. Modifications to DB2 tables can be done with less impact on what's already out there. In the past, programs that were used to create the factbook, one year could not be used the next. DB2 provides much more flexibility in this area. 3. A separate DB2 information base will result in minimal impact to the production systems. Direct data access tools described earlier allow the user to access production data from a variety of platforms at cost to the production environment. Additionally, these products are not designed to capture point-in-time data or trend analysis data which is at the very heart of the fact book. 4. DB2 is designed for distributed data access. DB2-OS2 data manager The second step in developing a standardized information base is to build atomic level information sets from targeted production systems. We are at this point in the process now. At LSU, rather than having 20 years of experience in producing university factbook, we have 1 year of experience 20 times. This is because administrators and computer personnel are operating in a changing environment. The structure of the operational data is constantly changing to take advantage of advances in technology, to respond to the business needs of the University and to respond to external regulations. Even if the University would attempt to freeze its operational environment, the federal and state governments would dictate that we make changes in such areas as financial aid and payroll. With the factbook we intend to reverse this process. Instead of collecting data from our production systems for the factbook we plan for production operational systems to deposit information into the electronic fact book. The factbook will dictate the format of this information, not the operational systems. At this level, the data in the factbook will reflect the nature of the database, there will be one record in the factbook for each occurrence of entity in the data base. As such these atomic level information sets will be large. The next step in building the information base is a transition step in which data from the atomic information sets will be aggregated into higher level information sets at the curriculum, department and college levels for example. Once this data has been aggregated the atomic level information set is migrated to tape/cartridge. The aggregated information sets serve as the source for the 100 or so queries which comprise the factbook. Only after we have built this foundation do we intend to provide access to the factbook from a variety of platforms. While the project is still under development, we are convinced our efforts to develop the electronic factbook will be successful. As with our operational data systems, we have started by building a solid foundation. We do not expect our initial efforts to be obvious to the average user near term. However, as indicated by our success with the operational systems, our clients will be much more satisfied with our long range results as the product will be of much higher quality and will come much closer to meeting their requirements. OUTSTANDING PROBLEMS - Multiple Registration Status 434433456, 43785328 (sum ug,fall grad) - Update Log - Look at one SRR read subroutine and one ASN read subroutine - Put scholarship fields in one code table and take code out of FAD8008 - New Residence Codes - PF Keys for screens are not consistent - Look at duplicate screens to see if can eliminate ssn search. 399-66-8348 and 434-29-1987 - Convert on-line programs to use DIR9914 when ADM installed or next conversion - Does RECALC compute pell amount only when given the right calc code - FAD4016 (CSAP Letters) need to verify job on HRM - Move 'DISBX' security from FAD9916 to FAD6034 - Convert FAD-21-01 and FAD-21-06 to use backup file and not FAD.FINSTATE for dupe CFAR check - Create FAD.PRINT_LOG to schedule printing instead of rigging FAD.DOCUMENT - Move Exit Interview Field from FAD.LOAN_TRACKING to FAD.STUDENT - Split FAD6018 into two programs - FAD purge - Edit FIN-YEAR in front end subroutine for valid year - Convert FAD1016 and FAD1021 from COBOL Report Writer to SAS - Move to SAS607