UCB Goes ERP: Berkeley's Infrastructure Includes New Business Rules and Policies

The financial systems at the University of California, Berkeley (UCB) were in need of an overhaul. An updated system was needed that could improve end user access and processing, and minimize the number of interfaces between systems. UCB chose to implement an Enterprise Resource Planning system to meet these needs; follow their path from vendor selection to final results.

The Berkeley campus is the oldest of the nine institutions that make up the University of California system. For the 1999-2000 fiscal year, the budget of the Berkeley campus, including all fund sources, was just over $1 billion. There are about 30,000 undergraduate and graduate students, 10,000 faculty and staff employees and about 13,000 student employees. IT is decentralized across the campuses. Aside from the payroll system, which is developed and maintained by the UC Office of the President (the systemwide office), each campus is responsible for operating its own applications and data centers.

To appreciate the condition of the financial systems at the Berkeley campus, we only had to look at its 30-plus year old general ledger. For years, the record length was restricted to 80 characters, and had never been expanded beyond 85. The year indicator was one-digit, which means that it was Y2K compliant, but we had a "decade problem." The budget system was over 20 years old, and its functions could no longer cope with the demands and dynamics of today’s planning requirements. There was (still is) no human resources system. Compounding these deficiencies, in the late ’80s and early ’90s, the country and the state’s recession resulted in the California Legislature cutting the universitywide budget severely, to a point that staff salaries were rolled back. The IT budget also fell victim to the multiple-year reductions.

Later, as the economy improved, and with the inevitable approach of the year 2000, the Berkeley campus began to look to revamping its entire financial system in order to meet the challenges of the new millennium. In 1995, a task force reviewed the campus’ financial needs. It requested public bids for a financial system that could improve end user access and processing, and minimize the number of interfaces between systems. At the time, client/server architecture and Enterprise Resources Planning (ERP) were the leading technology solution. A number of well-known vendors presented their financial software packages to the task force, including Oracle, SCT, PeopleSoft, and others. Because of PeopleSoft’s unique commitment to the public sector, past experience with student systems, its promise of a Macintosh client support, pricing, and its willingness to partner with the university in development, PeopleSoft was selected. The proximity of its headquarters and development center to the Berkeley campus was also a consideration. The campus decided to deploy account payables, purchasing and general ledger first, followed by human resources. Later, budgeting was added.

What makes an ERP implementation so complex is that the applications generally do not fit the organization’s business and technical frameworks. As a consequence, a new technical infrastructure, new business rules and policies, as well as procedures have to be introduced simultaneously.

Challenges

In late 1995, the campus took delivery of PeopleSoft Financials for Public Sector Release 5.x, and began to experiment with its implementation. Other than a small UNIX/Sybase-based data warehouse, the campus’ administrative applications was overwhelmingly IBM OS/390 based, with many mission-critical applications running under CA-IDMS, while payroll used DB2. The IT staff for administrative computing had little UNIX database experience, nor was the campus UNIX infrastructure robust or adequate enough to support mission-critical applications. The decision to use OS/390/DB2 to support our PeopleSoft implementation came naturally, since the operational knowledge and requisite skills to support a DB2 on the OS/390 were in place, and the underlying technology proven, and solid. In the opinion of IT, these advantages outweighed the relatively weak PeopleSoft support of the OS/390/DB2 platform. To facilitate TCP/IP connectivity between the user workstations and DB2 PeopleSoft recommended Gupta’s (Centura) SQLNetwork.

At the time, the campus has about 20,000 TCP/IP connections to its network, which has an FDDI backbone. Some of the outlying offices from the main campus relied on T1 or ISDN connections. A number of communication protocols were supported, including IP, IPX and Appletalk. Needless to say, the campus also has a relatively large number of Apple Macintosh computers. There are about 4,000 workstations used for administrative purposes, and the breakdown between PC and Macintosh is about 60-40, respectively. Personal administrative UNIX workstations are not supported. Even with that exclusion, supporting two workstation platforms, with so diverse an architecture has proven to be a big challenge. Attempts to standardize the campus to use one platform over another has only given rise to religious wars.

One of the difficulties in supporting the performance of a multi-purpose network that is used for research, instruction and administration is the management of the variety of network traffic that flows through it. For example, a user whose workstation is located on the same subnet as the Search for Extraterrestrial Intelligence Institute (SETI) project will experience a severe degradation in response time often. Economies prohibit the campus from having separate academic and administrative networks.

The lack of an acceptable way to distribute and synchronize software on client workstations in a campus ERP implementation also presents a major challenge to IT. Where in many private organizations, workstation infrastructure generally is guided by central IT management policies, the responsibility of a workstation setup and support at a university often resides with the staff in the department.

This means a staff member may maintain his own workstation, except in departments or offices that can afford their own workstation support staff. The campus IT organization recommends hardware and software configurations, but they are not always followed. In short, the manner in which administrative workstations are maintained mirrors the broader concepts of academic freedom.

The number of administrative computer users on campus is about 2,500, based on the number of active user IDs. Prior to the introduction of PeopleSoft, users accessed their OS/390-based applications using TN3270 clients over the campus TCP/IP network from their PC or Macintosh workstations. The campus staff, in general, were not adequately prepared to work in a Windows or a Mac OS. Entrance to applications were often scripted for the users, so they had little or no knowledge of how an application was initiated. This inadequacy was another issue addressed during our ERP implementation.

Another system-related deficiency the campus thought it would solve with PeopleSoft’s architecture was sign-on security. At the time, PeopleSoft sign-on communication was not encrypted. So, the user ID and password were transmitted over the network in the clear. Given the security vulnerabilities that are well known in educational settings, this deficiency was clearly unacceptable.

Solution

After the first PeopleSoft R 5.x 2-tier test workstations were installed, we discovered that indeed some large queries do spawn a large amount of network traffic that could impact the subnet performance as a whole, if they were executed in large volume. Even if we had 20 percent of the 2,500 users on the application concurrently, we felt we would not be able to sustain the network traffic, and produce an acceptable response time.

We began to seek an architecture that could resolve most, if not all of these challenges. We knew about Citrix and its capability, but we were initially hesitant to adopt this relatively new technology. Things changed quickly as we watched PeopleSoft’s promise of a Macintosh client fade, while the time to implement PS applications approached. It turned out that Citrix could address many of our technical issues and implementation concerns.

The connectivity middleware, SQLNetwork (later, with IBM’s help, we substituted IBM’s DB2 Connect) runs under Citrix with few problems. Citrix’s Macintosh client allows our Macintosh users to run PeopleSoft applications on the Citrix servers. The proprietory ICA protocol uses compression that partially addresses our "password in the clear" transmission. Although Citrix has an encryption feature, we chose not to deploy it for fear of adversely affecting performance. With Citrix, the PeopleSoft clients are installed on the servers only, so it saves us the human effort maintenance time involved in synchronizing client deployment. In addition, the shadowing feature in Citrix allows us to debug user problems conveniently.

To address the potential voluminous network traffic generated by some PeopleSoft SQLs between the client and the database server, we placed the Citrix servers close to the database server on its own subnet. Since Citrix only sends screen images to the client’s workstation, much of the network traffic is eliminated on the rest of the subnets on the campus. Testing showed that PeopleSoft panels displayed via Citrix and under a two-tier configuration are consistent. We only had to determine the number of Citrix servers we needed and report printing.

Comments from our consultants and vendors suggested that we could support at least 50 users per four CPU Pentium-based NT server with a G of memory. However, in practice, we can only support up to 30 users per server, and deliver a reasonable response time. The response time issue depends on the application and the functions invoked.

By far, the biggest problem we have encountered is using Citrix to deploy PeopleSoft report printing. While we do not allow large volume report production in the online environment, some report printing is necessary for operation during the day. Because there is no standard printer models and brands on our campus, we have to support most, if not all of them, either locally connected or network connected. The range of variation for printing includes Macintosh or PC, Novell, NT, or AppleTalk, remote dialup connection or TCP/IP-based, HP, Lexmark, Apple, etc. With its auto-mapping feature, and its large cache of printer-drivers, Citrix does its best to accommodate them all. The lack of a workstation installation standard and change control in the departments also contributed to many of the printing problems.

PeopleSoft’s use of cache file information presented yet another challenge. Under a two-tier configuration, PeopleSoft loads and saves its cache file to a workstation’s hard disk, initially, and only reloads the changes as necessary. Therefore, we have to simulate this requirement on Citrix. We make up a "universal cache file" for our applications that contains most of the commonly used objects and load it into a user’s "cache file area" on the Citrix server at sign-on time, thereby eliminating massive loading for everyone. This method is not perfect, but it works for about 90 percent of our users.

The use of Citrix has allowed us to avoid a large workstation support staff increase. If we estimate that each staff person can support 150 workstations, then we would need at least 17 to support our 2,500 users. As it stands now, we have been able to make use of our existing workstation support infrastructure, and hire three NT administrators to support our 30 development, training, test and production Citrix servers.

Having used Citrix to reduce the roundtrip transaction time for a PeopleSoft panel, we turned our attention to application tuning. As with all the other ERP software providers, PeopleSoft’s application code is written in such a way that it can be executed under a generic DBMS. In other words, it is not optimized. This was especially problematic for us, because, at the time, IBM was in the midst of staffing up its PeopleSoft support, so we had to spend a good deal of time tuning the applications ourselves.

As an example, we found that when multiple users run the PO Edit function concurrently, some of them experience deadlocks, despite the fact that the tables being used were already placed at a row-level locking. The issue turned out to involve how PeopleSoft uses its temporary tables. PeopleSoft first inserts rows into the temporary tables, and then deletes them at the end of its use. As a result, when RUNSTATS is run, the table is empty so DB2 would choose to scan the table, instead of using an index. When multiple users invoke this function, there would be multiple table scans for update. To solve this problem, we had to disable the DELETE statement in the executable, collect those rows for the purpose of RUNSTATS, and then, enable the DELETE statement again for operation.

In other instances, the tuning can be relatively simple. A vendor panel pulldown that took 20 seconds to 30 seconds was tuned to one to two seconds simply by introducing an index.

One curious problem we encountered was related to printing a "screen dump." Many users want to print a screen image to preserve a record of what they’ve done. PeopleSoft does not provide a screen print function. Although a screen image can be captured under a normal Windows Edit copy function and printed under a product, such as WORD, some users find this method too cumbersome. We had to install a screen print software on Citrix to permit screen print. To our horror, the screen print software transmits the image in a one-plus Mb bitmap file. With so many users printing their screens, and with so many printer driver variations, some users complained that sometimes it took as long as three or four minutes before they could retrieve their printout. We have since persuaded the users to install the screen print software on their local workstation.

These are just a few of the challenges we encountered along with our solutions. To enumerate the myriad of problems is beyond the scope of an article. We have issues relating to workstation configurations, server upgrades, software licensing, version control, PeopleSoft upgrades, batch processing, backup and recovery, database structure design, reporting, operations, deployment, training, staffing, recruiting, staff retention, and other implementation logistics, all of which required the mobilization of almost the entire campus administrative staff and substantial IT resources.

Future

As of this writing, we are in the midst of deploying our general ledger module, with a new chart of accounts for the campus. We have been using the accounts payables and purchasing modules for over a year, and we have now upgraded our application to release 7.02. To the experienced administrators, the introduction of a new chart of accounts probably represents more of a challenge than a PeopleSoft implementation. We are also beginning to plan our PeopleSoft HRM system implementation.

Technically, IBM’s PeopleSoft commitment and support, along with their DB2 enhancements, have substantially improved. Our Citrix infrastructure and OS/390/DB2 are robust. PeopleSoft’s newest release shows much promise.

Logistically, the campus has decided to split our PeopleSoft technology, albeit temporarily, between using Release 8.x, an Internet-based product, for HRMS while the rest of the PeopleSoft applications will remain C/S-based, and then catch up later. We are now faced with establishing a new infrastructure, while continuing to support the C/S one. However, the transition seems inevitable and necessary.

Kenneth Yip is the Manager of IT Infrastructure & Enterprise Architect for Administrative Systems at the University of California at Berkeley. He can be reached via e-mail at kydas@uclink.berkeley.edu.