We discuss our process to obtain acceptance from campus leadership, data owners, and administrative and academic users and use of the web to communicate our progress on this project. Finally, we describe how we plan to formalize this data administration and production effort. We hope our experiences provide a positive example of a broad collaborative effort to develop a useful management system.

Introduction and Background

Berkeley, the first of now ten campuses of the University of California, is one of the world�s leading research institutions and intellectual centers. It offers more than 300 degree programs from the bachelor�s to the doctoral level. Its faculty include 8 Nobel Laureates, 198 members of the Academic Academy of Arts and Sciences, 8 members of the Howard Hughes Medical Institute, 121 members of the National Academy of Sciences, a Poet Laureate emeritus of the United States, and 140 Guggenheim Fellows. Its more than 30,000 students are diverse and highly competitive. Its mission includes public service in addition to instruction and research, and the campus accordingly devotes considerable effort to outreach activities.

The arrival of a new Chancellor, Robert M. Berdahl, in July 1997 motivated the development of a comprehensive management information system which could capture trend data from a variety of streams -- some already accessible centrally, primarily through legacy systems, others held departmentally, often in idiosyncratic formats -- and, using state of the art technology, to combine them in meaningful ways to portray as much as possible of the very broad spectrum of campus activities. The intent was as much to allow departments to present themselves to the remainder of the campus as it was to help inform decision making. The Office of Planning and Analysis was selected to lead this intensive project requiring the collaboration of dozens of campus units under a very stringent deadline.

Cal Profiles were published on November 13, 1998, a month ahead of schedule. They are novel for the campus in several respects: (a) they are comprehensive, and therefore include all administrative as well as academic units, (b) they contain numerous metrics, e.g. the number of prescriptions filled by the health services center, supplied by departments and not available through other central sources, (c) they include executive summaries, narratives similarly supplied by departments as a source of qualitative information, (d) they are available to the entire campus community via the Web, together with all necessary supporting documentation (e.g. methodology, glossary, etc.).

Changing Technology for Cal Profiles Production

The concept behind Cal Profiles was certainly not novel to Berkeley. A predecessor � the Academic Unit Profiles -- contained a small subset of the same measures, but only for academic departments. It had been produced using mainframe technology and was published initially only in hard copy. Later versions were also made available in FilemakerPro and ultimately in FoxBase for campus users with sufficient facility with those technologies. The laboriousness of the effort combined with budget cuts, reorganizations, and staff turnover, resulted in the decision not to update the document for several years, with the consequence that on the new Chancellor�s arrival these data were already somewhat less than current.

Quite clearly, the older technology was not really suitable for the development of the new product, particularly when ease of access was a primary goal that dictated the Web as the logical vehicle for delivery. The intent, moreover, was to use tools intelligible to analysts in order to reduce the need for technical support. The choice to use Microsoft Access was an easy one, and it was in this desktop environment that the entire data assembly was done, although the data eventually were migrated to Microsoft SQL Server in the interests of performance. Technical staff provided the Web front-end using Cold Fusion and Web Trends.

The following table lists the sources and formats of the data available from central systems, broadly defined.

Each type of data has its own, often unique, structure. Financial data, for example, are arrayed by account and fund; data on enrollment are arrayed by student major; data on space are organized by building and room. The intelligent merger of different data types requires the construction of a single structure that can encapsulate all the others concisely. In practice, this means that there will always be rough edges, that the fit can never be precise. In the present instance, the logical choice was to structure all the data according to the Organizational Unit ID developed previously by the Budget Office for the classification of financial information within the Berkeley Information System (BIS) Data Warehouse. This had two advantages. The first, and obvious, one, is the savings of effort. The second, subtler, one is that embedded in the four-character Org Unit ID is sufficient information to permit the easy creation of subtotals and totals which parallel the campus� organizational structure.

The inevitable changes in that structure as departments are created, disbanded, or relocated over time, raise the issue how best to report data for multiple years (in the present instance, five). There are at least two choices. To present history as it occurred � this might be characterized as the purists� approach. Or to rewrite history to conform to the most current organizational structure � the revisionists� method. For two reasons, revisionism was the choice of the day: technical ease of implementation (by obviating the need to incorporate a set of data dictionaries for each year) and better trend information (by avoiding organizational discontinuities). (A disadvantage, of course, is that the data presented for a given unit for a fixed year may change in a subsequent annual update of the document.) To ensure against loss of information, great care was taken to map all defunct units to current Org Unit IDs.

The BIS Data Warehouse was leveraged at every opportunity. Although it now contains primarily financial data, there are pilot projects to expand its ambit to other data, including enrollment, grants and contracts, and space, and its utility will increase accordingly as these are brought to fruition. Moreover, by design, the data published in the Warehouse are at the finest level of granularity, whereas Cal Profiles present information already aggregated to the level of Org Unit ID. To the extent that it contains the relevant data, the Warehouse provides the audit trail for the Profiles. With knowledge of the methodology, widely published via the Web, skeptics can verify for themselves the accuracy of Cal Profiles by obtaining their own data through the Warehouse. Finally, all records in the BIS data warehouse are assigned an Org Unit ID, and are therefore easily mapped into the Profiles.

The Warehouse was the source of all General Ledger, Research Expenditure, and some Adjusted Budget data. For other kinds of data, we worked with the owners to develop an appropriate mapping into the Org Unit ID. Some data exist only at a division level and therefore can�t be presented at the department level. Other data might not map directly to a budgeted area � these are shown either at a lower, sub-department, level without accompanying financial information, or they are incorporated into the catch-all rubric "other". All the relevant mappings � i.e. dictionaries � are published as part of the documentation.

Data from other central sources often required considerable massaging (reformatting and "normalization") to convert them into tables within a relational database environment.

Departments were invited to provide such data as they thought germane that are not available centrally, as well as narratives, according to guidelines that specified format and suggested content. Of the nearly 300 distinct academic and administrative units which comprise the profiles, nearly half provided narratives and nearly one-third provided supplemental data. The narratives often contain live links to units’ own Web home pages or other sites they deem relevant. Supplemental data include, for example, the number of applications for employment received by Human Resources, the number of schools visited by Relations with Schools, and the number of arrests made by the Police Department and provide considerable perspective on the data compiled centrally (e.g. on finances, enrollment, and the like). The ease of access to non-traditional information, both qualitative and quantitative, within a context and a unified setting is, we believe, one of the most valuable features of the Profiles.

Work on Cal Profiles began formally on July 7, 1998 with the Vice Chancellor – Resource Planning and Budget’s successful presentation of the concept to the Chancellor’s Cabinet. The enunciated goals were to produce a unified source of management information that could be accessed by and would benefit the entire campus, particularly, but far from exclusively, with respect to the annual preparation and review of budgets. The target publication dates were given as December for all academic units, and the following January for the remainder of the campus. Each Vice Chancellor was asked to identify one or more representatives to collaborate with us over the intervening months.

The number of representatives grew quickly from the handful originally assigned by their Vice Chancellors, to more than forty, as vague interest turned into great excitement with each successive iteration of the data. They became our sounding board to the campus community. We met with them collectively four times, and countless times in individual sessions.

The first meeting was in late July. After presenting the background and a production plan and schedule, we handed out to each representative such relevant materials for his or her units as we had been able to assemble by that point -- the Org Unit ID structure and some financial data. Our intent was for the representatives to vet our work -- both the structure and the data -- as fast as we could generate it. With their involvement during the process rather than after the fact, we knew we could guarantee the quality of the product, and we would minimize the volume and magnitude of any criticisms post publication. The representatives were continually enthused by the process, if not always by the data.

We held two more substantive group sessions at roughly one month intervals. By the end of September, we were able to present to great acclaim a prototype, but very attractive, Web front-end that the technical staff had constructed. The last session was a celebration on the publication day, complete with a cake decorated with the Cal Profiles logo.

The representatives contributed immeasurably to the success of the project. Their comments led to substantive reformulation of the way in which certain kinds of data hitherto had been arrayed and interpreted, and to improvements in the underlying processes and systems, quite independently of the efforts on the Profiles. In some cases, departments remarked that for the first time in their experience, central data had been defined and arrayed in a meaningful way.

Numerous central offices, including the Budget Office, Space Management and Capital Programs, Financial and Management Analysis, Sponsored Projects and the Graduate Division were key consultants on data within their ambits of expertise.

The BIS group provided invaluable information on their data, their dictionaries, and their access methods. Although they remained largely behind the scenes, they were essential to the proper assembly of the financial data, and they are obviously equal partners in the other pilot warehousing projections referred to elsewhere in this paper.

The Chancellor’s Office-Information Systems (CO-IS) and the Administrative Systems Department (ASD) provided critical support on the Web-front end. Although the medium in this instance is not the message, it does directly determine the utility of the product. Without the Web, the data and narratives would have fallen flat. Ease of access is of cardinal importance – no one today would willingly thumb through a one-thousand page hard copy document. The Web, moreover, enabled us during development to disseminate guidelines and progress reports, and to solicit comments, with a minimum of effort.

The Cal Profiles Web includes links to:

The organizational chart is a graphic that illustrates how data for individual departments add up to the campus total. A Java Applet is used to present the chart in the form of folders which can be opened with a click of the mouse to reveal their nested contents, beginning at the highest (Chancellor and Vice Chancellorial) level of the campus hierarchy. Users whose Web browsers don’t support Java are taken directly to a parallel, text-only, version.

To access the actual Profiles users may:

All three options for obtaining Profile data lead eventually to the same screen, from which users may select the information they desire and view it or download it at will to Microsoft Excel or Word.

All lines in the report are numbered. Clicking on the line number takes the user to a glossary that provides a definition of the content of the line. Clicking on the department name takes the user to the narrative, if the department provided one.

Cal Profiles was a Chancellorial initiative and therefore a major one for the campus and for our office. Below we discuss some of the ingredients that we believe were necessary and have contributed to its success.

In late 1997, at the Vice Chancellor’s request, our office undertook an analysis of the computing requirements of his units, involving both a survey of peer institutions and an internal assessment. We greatly appreciate the responsiveness of the numerous participants who contributed to our effort and who gave us the benefit of their invaluable insights, foremost among which was to obtain the support of campus leadership to receive the necessary resources and attention. Although the timeframe was very short, some institutions recommended getting something out there sooner rather than later, in order to shift the focus from what is capable technologically to what is useful for the campus community. Numerous institutions emphasized training for campus users to ensure they know how to access and to interpret the data they seek. We took that to heart when developing the methodology and glossary sections. They also recommended having a broad range of staff support to cover the necessary technical expertise and project management skills.

In addition to reviewing comparable projects at other universities, prior to the presentation of concept to the Cabinet we also studied the feasibility of producing the Profiles using Microsoft Access. Beginning with a minimalist sketch restricted to a few kinds of financial data, we gradually expanded the ambit of the prototype until it comprised a fairly exhaustive set of data and we attached to it a two year schedule for production. This enabled us to present a conservative plan for the first year while buying time and whetting the campus’ appetite for a more ambitious product the second year.

Assurance of the quality of the data was a key issue. Such central data as had hitherto been available through legacy systems were often in forms difficult for most departments to use, and therefore received relatively little if any scrutiny. A guiding principle of the development of the Profiles was that departments were made responsible for reviewing, well prior to publication, the accuracy of the content and presentation of all central data pertaining to them. This principle, and the review process it engendered, permitted the identification and early correction of numerous errors, both in the base data (including data previously archived in the warehouse) and in the dictionaries used to map them to the uniform framework of the Org ID Codes, with the result that upon initial publication Cal Profiles were virtually error free and users understood and accepted the data and the methodologies used to assemble them.

A second principle, to work wherever possible directly with source data at the finest level of granularity, was no less critical to the success of the Profiles because it permitted rapid the reorganization of the data on departmental demand, without dependence on or recourse to the pre-established and often inadequate structures of various legacy reports.

The openness of the data, structure, and methodology ensured that the additional workload imposed on the departments through their participation in the process would be kept to a minimum, and it greatly facilitated their reconciliation of the central data with their local records. The quid pro quo is that representatives were asked to reciprocate by providing glossaries, a description of methodology, and an accompanying executive summary for any data they chose to provide.

We devoted a great deal of attention to the functionality and the aesthetics of the Web front end and other features of the site, and to a series of policy and technical issues related to access (e.g. not all authorized users are strictly within the campus’ internet domain or have reasonably current hardware and software). The ability to download information directly to spreadsheet or text (if necessary by providing backwards compatibility) and to print directly from the Web is essential.

This project could not have been completed in the allotted timeframe without immediate access to a staff with the necessary technical expertise and project management skills. Technical expertise included understanding data sources, Microsoft Access (import, tables, mapping, report development), Cold Fusion, SQL Server, and understanding web site development. Project management skills included presenting the project in multiple settings, collaborating with "data owners," working with unit representatives, and developing descriptive information on the project, site, and methodology.

Challenging Aspects and Potential Pitfalls

The single most challenging aspect of the project was the very tight timeframe. Prior to the formal announcement of the project in July 1998, we had gotten several months’ head start by beginning to create relational tables from certain legacy files and had gained valuable experience in the process, but we were nonetheless facing a significant volume of work and an equally significant dearth of time. Planning and Analysis had no choice but to assign each task to the single individual best suited for it, and to make the project as a whole its very top priority: one analyst was responsible for the entire assembly of the data; another was responsible for managing the process and its numerous participants, including the departmental representatives; the interim director provided overall direction and guidance. The contributions of other central offices, as noted above, was essential, as was the Web support provided by two technical staff in CO-IS and in ASD.

Although the Cal Profiles is a complex product, its functionality and rapidity with which it was developed made it look easy, perhaps too easy, not only to senior management but often also, surprisingly, to departmental representatives who sometimes came to expect extraordinarily and unwarrantedly fast turnaround on their requests to reconfigure certain aspects of their data.

Owners of the different central data streams reacted differently when we approached them with our need to incorporate their data into the Profiles. Some welcomed it as an opportunity to shortcut their own distribution mechanisms. Others were concerned about how their data would be used and understood by the campus. Yet others felt that their distribution mechanisms were adequate and should suffice. We took pains to explain that although we were responsible for assembling the data, we had to rely on its owners to specify the appropriate structure, and that we claimed no expertise over content. With respect to the potential for misinterpretation of the content, we went to similarly great lengths to provide thorough, easily accessible, documentation of the methodology and data sources, and we invited departmental representatives to include in their narratives whatever explanatory material they deemed necessary.

Future Directions

Following the release of the Profiles we have solicited comments and suggestions for improvements from the campus community systematically (in addition to the informal means for comment available to users via the Web site). We will use the information so gathered to plan how best to update the document in the future, to incorporate any missing central data, and to facilitate report production and Web maintenance. We intend, moreover, to continue the development of the Profiles in close collaboration with the departments and the user community. Our goal, however, is eventually to spend less time updating the Profiles and more time using them for planning and analysis.

We will continue our collaboration with BIS and we will urge the addition of new kinds of data into the Warehouse. The incorporation of the files used to produce student major, course enrollment, and student credit hour data is a work in progress which relies on Decision Suite’s OLAP (On-Line Analytical Project) product that enables users to drill down directly from a summary report to the underlying data. Depending on the success of this effort, we may look to this technology as a vehicle for the publication of Cal Profiles data.

We seek also to illustrate the utility of Cal Profiles as a unified source of information, for example in the incipient campus effort to develop quality centers, and in WASC and other accreditation activities. Finally, we hope to expand the Profiles to facilitate our comparisons with peer institutions and our responses to their requests for our data. An initial endeavor towards this end is mapping the CIP Codes to our Org ID Codes – a good fit will simplify our use of data from national sources, e.g. the National Center for Education Statistics (NCES), the Integrated Postsecondary Education Data System (IPEDS), and the Association of American Universities Data Exchange (AAUDE).