Decision Support at Stanford: Partnering with Users Copyright CAUSE 1994. This paper was presented at the 1994 CAUSE Annual Conference held in Orlando, FL, November 29- December 2, and is part of the conference proceedings published by CAUSE. Permission to copy or disseminate all or part of this material is granted provided that the copies are not made or distributed for commercial advantage, that the CAUSE copyright notice and the title and authors of the publication and its date appear, and that notice is given that copying is by permission of CAUSE, the association for managing and using information resources in higher education. To copy or disseminate otherwise, or to republish in any form, requires written permission from CAUSE. For further information: CAUSE, 4840 Pearl East Circle, Suite 302E, Boulder, CO 80301; 303-449-4430; e-mail info@cause.colorado.edu DECISION SUPPORT AT STANFORD: PARTNERING WITH USERS Joy Mundy Senior Business Analyst Information Distribution Services Stanford University Stanford, California Joy.Mundy@forsythe.stanford.edu Warren Thornthwaite Program Director, University Data Warehouse Information Distribution Services Stanford University Stanford, California Warren.T@forsythe.stanford.edu ABSTRACT Stanford University has been developing its Decision Support System (DSS) and associated Data Warehouse since the summer of 1992. The primary mission of the team is to significantly improve the University's data-based decision-making capability. We've taken an opportunistic approach to developing a decision support system by focusing on high- value, high-profile problem areas. After a brief context and history of the University Data Warehouse, this paper describes a major partnering project between the University's Budget Office and the DSS team, and ends with a discussion of the lessons we are learning from the project. PART I. CONTEXT AND HISTORY The Changing Nature of Managing University Business Schools are businesses. They have revenues and expenses, assets and liabilities, products and consumers. For example, the "market" for research, one of Stanford's major product lines, has been declining in recent years. As a result of this and other factors (such as the Loma Prieta earthquake), Stanford is in the middle of the same kinds of cost cutting efforts that many major enterprises are facing. As resources become scarce, decisions on how to allocate them become more difficult to make. Management at Stanford is inspired to take a new approach and require quantitative as well as qualitative decision making. The following excerpt from a 1993 Board of Trustees presentation sums up the urgency of improving decision making capability: "In today's unforgiving economic and regulatory environment, Stanford cannot afford to base decisions on guesswork or partial information. We have immediate needs for reliable, integrated management information to support critical decisions being made right now. At the same time, Stanford must not become so preoccupied with today's pressing demands that we neglect to plan for the future; solutions for today's information needs must be flexible enough to meet tomorrow's needs as well." Stanford is a major enterprise by all measures (except profit, of course). Our total expenditures across all business units is closing in on $1.5 billion annually. The University has close to 14,000 students and 1,380 full time faculty in 7 schools including a major teaching hospital. We also manage 8,180 acres of campus, retail and office park real estate and a $2.5 billion endowment. As one would expect, Stanford generates a lot of operational data and is typical in its systems evolution. Many of our legacy systems are "stovepipe" in nature. Although they do a fine job collecting the data needed for their function, maintaining that data and reporting on it, it is very difficult for a business user to combine data from two or more different systems. Stanford's legacy systems use at least five different identifiers for people and three different identifiers for organizations. A report on faculty activity should draw data from the faculty, student, finance and sponsored research systems, but these systems do not communicate with each other. As a result, many major decisions have been made without the help of complete information. The DSS Project Two years ago, the Director of the Data Center proposed to the Provost and the Board of Trustees that the University create a Decision Support Systems (DSS) team. Motivated by a history of success with DSS in the Medical School and an understanding of the need for analysis, the Provost's Task Force for Information Systems funded a pilot project to demonstrate the feasibility and value of a University DSS. This successful pilot project resulted in a charter DSS group charged with the following mission: Significantly improve the University's data-based decision making capability by * providing tools for end users and IS to support ad hoc data access and DSS applications development, * providing a single, integrated authoritative source for university decision-making data, and * providing examples, expertise and training to help build the University's analytical capability. The Warehouse Foundation The Initial Base Given the emphasis on value, the DSS project has taken an opportunistic (almost mercenary) approach to implementing the Warehouse. Our focus has been on high value problems. We designed an initial framework for the warehouse, then prioritized its development based on the requirements and interest level of several key clients, mostly in the Provost's office. The initial development of the warehouse's foundation was driven by client requirements, so each new application meant a large behind the scenes effort to create the data structures needed to solve the problem. We relied on business analytical expertise in our group to work with the client in defining the problem. This person would then work with the data team to ensure the available data met the need, or to specify any additional data required. As the base grows larger, the incremental effort for each new use has decreased significantly - in some cases from weeks to minutes. The Platform Part of the original charter of the DSS team was to help pioneer the University's use of open, client/server based technology. The Warehouse is Sybase based, running under UNIX on a SPARC 20 (see figure 1--Not Available in ASCII Text Version). This open client/server systems approach offers the advantages of: * mixing and matching off-the-shelf, industry standard, commercial products-allowing the University to respond more quickly and economically to changes in information technology and allowing users to have more choices in selecting the software tools they prefer. * separating data capture needs from data access needs- systems can be optimized for transaction processing at the data capture end and for decision support and analysis at the data access end, without either end needing to compromise. The desktop platform mix in our user base is about 60% Macintosh and 40% PC compatibles. The Data Before we could answer any significant questions, we had to build a database. Drawing from the Medical School's experience, we extracted a core set of data from the University operational systems. A significant amount of data work took place during the extract process: person identifiers were mapped to a unified set, data was aligned by time and organization, history was kept, and certain calculations, aggregations and filters were applied to give the data a business orientation. (Figure 2 Not Available in ASCII Text Version) The DSS team built and populated databases containing information about student status and activities, faculty status and activities, sponsored projects, courses and instructors, fund balances, budgets and expenses. In addition, and more importantly the team developed and verified the logical links between these disparate clusters of data. The University Data Warehouse is now the only place at Stanford where there is a generalized way to link information about the same person in different systems. (Figure 3 Not Available in ASCII Text Version) Data Access Once the University Data Warehouse was populated with tables and data, we tested data access tools for users. We are currently supporting three data access tools: BusinessObjects, DataPrism, and Pablo. The first two tools are supported for both the Macintosh and Windows platforms. User Community We are in the midst of launching the Warehouse to an initial user community, targeting users in Central Administration, the Controller's Office, the Budget Office, and School and departmental administration. We hope to convince users that new tools will: * help them to explore the data, * remove some data "drudgery" from their current reporting requirements, * encourage creative thinking, and * be easier to use than legacy systems. To succeed we must develop extensive training and documentation, simplify the database, educate users about the data, and make sure the users and database have adequate computing power. The database simplification and data education pieces are much more difficult than one might expect. Simplifying the database reduces the range of questions it can answer, and the search for the 20% of complexity that answers 80% of the general questions is a difficult one. The University is a complex place, and the data it generates matches that complexity. Stanford's academic environment seems to be more prone to complexity than a for-profit institution, and the distributed business environment has led to systems that accommodate many different modes of management. We are still trying to determine if our DSS can be a true end-user environment, or if the complexities of our business, the state of our tools, and the multiple roles that our users play will require us to rely on technically skilled users who act as intermediaries. The University has invested significantly in the systems that create the source data for the DSS. This source data is essentially a fixed asset and the marginal cost of using that data to enhance the decision making process is relatively small. As we cautiously enter the roll-out phase of the Data Warehouse, we are working with small groups of users who share an interest in a specific subject area, such as sponsored research or graduate student data. Wherever possible, we will capitalize on this interest to motivate users to climb the learning curve associated with querying a complex database. In many ways, the Consolidated Budget project described in the next section is just this kind of starting point. It is a critical project, so people are motivated to learn; it is similar to existing activities, so the logical leap is not too great; and it is small enough to manage. PART II. THE PARTNERING PROJECT: THE CONSOLIDATED BUDGET The projects that the DSS team has undertaken in the past two years span a wide range of analytical complexity and breadth of access. The projects most visible to senior management have required complex transformations of University-wide data, yet the deliverable may be simply a report. We have found that projects at the other end of the spectrum, which deliver flexible access to base data for a broad set of users, are a greater challenge. The project that we will discuss in detail here-the Consolidated Budget project- requires that we work closely with business analysts to provide flexible, accessible data and analysis tools. Statement of Business Problem Stanford has centrally budgeted and controlled only a subset of its activities. The "Operating Budget" of about $450 million covers only 40% of the University's expenses, and encompasses what is generally thought of as the general instruction and operation of the University. Senior management has set out the charge that the University develop a process to forecast, plan, and budget the entire activities of the enterprise, which includes $500 million in sponsored research, and $150 million of expenditures from gifts and endowment that are managed locally. The analytical problem is that both sponsored research and management of restricted funds are extremely decentralized at the University, as individual faculty members conduct research and control many gift funds. Forecasts and plans, by contrast, must be developed at the Decanal level, with input from departments who in turn communicate with their faculty. The key ingredients of a good forecast are a clear understanding of past activities, and a reasonable guess about how those activities will change in the future. No application or data can answer the "future directions" question, but helping Deans' and Vice Provosts' administrative staffs better understand their organizations' current and past operations is the DSS team's charter for the Consolidated Budget project. The data problem has been that the source financial systems were built to manage the University's day-to-day operations, and are designed to report on a relatively narrow set of activities at a time. There is no technical reason that the needed reports and analyses could not be generated directly from the source system. Rather, the problems are related to flexibility, ease of use, and scalability, as the Deans' administrative staff explore the financial data, develop ad hoc reports, and run those reports for each department in their School. For example, one of the Consolidated Budget reports would require users to run 6 reports in the existing systems and combine the results. The DSS and Consolidated Budget Support The DSS team is supporting the Consolidated Budget project for 1994-95 by providing School and VP area administrative staff with data, tools, and training. The base data are five years of detailed yearend financials for each area, and are largely unchanged from the systems of record. We have developed data collections especially for the project, that rationalize, transform, and pre-aggregate some slices of the data. We have developed flexible structures to easily aggregate up and drill down through the different dimensions of the data. The Consolidated Budget users access the data through an off- the-shelf query and reporting product. Their view of the DSS database is limited by this product to the data collections that were designed specifically for the project. Our users are finding this view of the data useful for a broader range of questions than just the Consolidated Budget. DSS team members developed a formal training program on the tools, techniques, and data for the users, and we spend a lot of time in ongoing consulting and support. Helping our users understand the tools and data, and to use them effectively, is the team's greatest challenge. PART III. THE PARTNERING PROCESS: WORKING WITH CLIENTS Unlike most operational systems, DSS is a service that users may choose to use or not use. As a result, it is particularly important to work directly with the users in all phases of a DSS project, including the fundamental decisions about what data elements and periodicity of data to warehouse. Although it is necessary to receive input, and get buy-in, from a wide range of users, it has been critical to the success of the Consolidated Budget project to work closely with a high-level project owner: the University Budget Office which staffs the Provost. The DSS team relied heavily on analysts in the Budget Office during the design and development phases of the project, and they are lending their expertise during the delivery phase as well. Application Design: Help Clients Think Outside Current Boundaries One of the first lessons that the DSS team learned is that users' views of what they want are strongly coloured by what they have. A user who is adept at extracting data from the source systems and who is reasonably facile with desktop spreadsheets, will ask that DSS simply provide an easier way to download 10,000 rows into Excel. Others request that we replicate the standard reports, but make them faster and easier to run. The DSS tools and data can easily accomplish both tasks, but we would be squandering resources if DSS were merely a big database. Because users are not familiar with the technology, they must rely on Information Systems (IS) staff to propose specific plans for transforming general requests-"I want to explore my financial data"-into an application. The key implication of users' lack of familiarity with the technology is that the IS staff in DSS must be hybrids; they must have skills in both business analysis and systems analysis. During the Consolidated Budget project, a DSS analyst met weekly with the Budget Office and target users for two years, in order to learn the details of the business problem that DSS was committed to help solve. Another lesson we've learned is that in order to provide analytical flexibility, IS staff must develop a toolkit of standard enhancements to base data: standard approaches to slicing, dicing, rolling up and drilling down through the data. Further, this toolkit must be customizable, if not by the users themselves then quickly and easily on their behalf by IS staff. This point follows directly from the earlier statement that users tend to want different instances of something they have already. As soon as you give them a new way navigate through their data, they'll want a slightly different version of the same thing. The Stanford DSS team developed a generalized approach to building hierarchies atop atomic data as the center post of its data enhancement toolkit. Hierarchies help users to navigate through data, they provide natural paths of aggregation and drill down, and they become subtotal lines in canned reports. The DSS team has developed tools that make it quite easy to develop new hierarchies, and we have even had some success in having users design their own hierarchical structures. The design phase of the DSS Consolidated Budget project consisted largely of determining upon which atomic elements in the financial data to build hierarchies, and choosing the tools with which to deliver the data. Application Development: Prototyping Is the Key Once you've chosen information delivery tools and developed your data enhancement toolkit, prototyping is not just key; it's most of the work. In our experience, DSS tools are well suited to prototyping, and the iterative cycle is to manage. Rapid prototyping and development reaps the investment in infrastructure and data alignment that underlies a production Data Warehouse. The Consolidated Budget project supported the development of many important pieces of the DSS infrastructure, especially the development of the hierarchy tools. Once those pieces were installed, the user access applications were developed over the course of six weeks. We anticipate that future application areas can be developed even more quickly. A DSS application must balance the need to simplify users' views of complex data and minimize the chances and negative outcomes from "bad queries" with the requirement that the system support ad hoc querying. User tools provide some assistance here, although there's always a tradeoff. We've found that users have difficulty with (1) navigating through the tables and columns of the database; (2) joining tables correctly; and (3) remembering to aggregate when requesting a sum or count. Our choice and use of data access and analysis tools has been driven by our commitment to help users avoid these pitfalls. Application delivery: Training, documentation and rollout Training and documentation are absolutely vital, and involve more than just "how to use the system." The training program must educate users about the tools, the data, ad hoc querying and reporting. Users who previously have accessed information only through canned searches and reports may not be familiar with all the dimensions of their data. We have learned that the "value added" information such as our DSS hierarchies were initially confusing to many users. The DSS team has not been impressed with the quality of documentation that arrives with data access products purchased off the shelf. We have found it necessary to expand upon, or even rewrite, that documentation. As wasteful as such an endeavor sounds, it pales in comparison with the waste of resources that would result if users avoid DSS because they can't drive the tools. Rollout is considerably more challenging than in the mainframe environment, as anyone who has dabbled in client/server architecture will attest. As a vanguard client/server application at Stanford, we have tackled more than our share of general connectivity and distribution problems. We have found that choosing an access tool that keep most or all of its knowledge about the application (its metadata) on the database server itself is extraordinarily valuable. PART IV. CRITICAL SUCCESS FACTORS The work of the Stanford DSS team over the past two years has led us suggest the following critical success factors to colleagues who are considering building a Decision Support System of their own. Project value: Developing a Decision Support system is expensive; the projects that provide the initial impetus for the system must provide significant value. Client involvement: * Partnering is mandatory, since DSS is optional. * Identify a business partner to be the project owner. The person must be engaged and excited about the project, and ideally cannot do her or his job without this application. * Clients know what they need and how it should look. * IS staff must work with users to deliver a flexible product in the best way possible. Client analytical ability: The business partner, and other future users of the application or system, must recognize that ad hoc querying to perform "decision support" is not easy, even under the best of circumstances. If the clients do not have the analytical skills and knowledge to use data that are raw enough still to be flexible, then IS will have to deliver a less flexible and more costly product. Trustworthy data: * The data upon which DSS applications are built must be available within a single system, * clean (error-free, with referential integrity), and aligned across the various sectors. Technology: DSS applications should use off-the-shelf products that adhere to industry standards. The technology in the user access area is changing rapidly, and everything you design and build should be modular and reusable. PART V. NEXT STEPS AND CHALLENGES Users Our biggest challenge is user acceptance (another phrase for organizational change). Downsizing over the last few years has hit the administrative community hard. Fewer people are trying to do the same amount of work, and any new tool with a learning curve must have a major obvious payback in terms of time savings in the long run. With DSS, this payback is not always obvious (or it would have been seized by now). The real return comes in making decisions or finding problems that are not even on the table today. Education is a big part of gaining acceptance. Documentation on the data and use of the tools is weak. We are working hard to fill it in, but the task is massive. We also need to investigate alternative delivery vehicles for this information. The World Wide Web has several advantages, like a single set of source documents, and multi-platform readers developed by third parties. It also means converting our documentation into HTML. Data As we expand our user base, we will continue to expand the contents of the Warehouse. We are in the process of reverse engineering a user-driven logical model for the DSS database that we'll use as our guide to filling out the database contents. Infrastructure Early on, we built a metadata driven utility called Table Update and Maintenance System (TUMS) on the UNIX side which drives our current loading process. Over the next 12 months, our challenge is to automate the full data load process, combining the mainframe piece and the UNIX/Sybase piece into a single job stream. We have been disappointed with the immaturity of current job control products that bridge the two environments. In general, we need better tools, both for the data access side, and the data management side. There are literally dozens of data access tools available. Many of them have useful features. Most of them are in the process of growing up from desktop roots-they do not handle many of the enterprise issues well. Capabilities like distribution and management of reports, security, version control and so on are difficult or impossible with most of today's data access tools. Data Administration The need for a Data Administration function became clear when we began to develop a data warehouse. Many of the source systems collect information without proper data edits in place. In many cases, these were actually removed to improve the transaction time because they were not critical to the system's function. Many others are free-form data entry, and need to be manually aligned with other data sources. Stanford has recently adopted a set of Information Systems Principles which call for operational systems to recognize their requirement to meet broader University data needs as well as the specific needs of the business function. Some day, our source systems will generate data with the same codes and references. Meanwhile, we continue to clean up after ourselves. Security The warehouse faces the same general security issues as all client/server applications. The data has to be secure both at the source, and across the network. The warehouse also has the added complexity of protecting confidential data from a broad range of users at all levels in the University. Stanford is very careful about who can see what data. Implementing security in the warehouse has not been easy. At this point, we are confident about our security set up, but its administration will become an increasing burden as more users come on-line. PART VI. CONCLUSIONS In the initial phases of our project, our group provided extensive analytical expertise so that we could understand problems from both a business and technical point of view. We must continue to be responsive to the requirements and capabilities of our user community or we will fail. We know the textbook formulas -that is, we are familiar with the checklists and guides and seminars on how to build a data warehouse. These are helpful, but success will ultimately depend on organizational culture and individual attitudes. In our case, economic realities are forcing a cultural change. If we can help people handle that change better, we will be successful. Partnering is critical to the success of any DSS/data warehouse effort. If users are not brought into the process, they will not use the product and they will not be able to articulate the value of the system. Without an understanding of the value, one of the University's largest assets will "rust" away unused in the back rooms of the computer center's disk farm.