This paper was presented at CUMREC '99, The College and University Information Services Conference. It is the intellectual property of the author(s). Permission to print out copies of this paper is granted provided that the copies are not made or distributed for commercial advantage and that the title and authors of the paper appear on the copies. To copy or disseminate otherwise, or to republish in any form, print or electronic, requires written permission from the authors.

ABSTRACT: UW-Madison successfully migrated more than 20 years of legacy data from a homegrown system to a purchased package, PeopleSoft's Student Administration system. UW used a phased approach to implementation, with the Admissions module going into production first. The data to be converted included legacy admissions data and the Campus Community data needed to support admissions processing. This paper describes the problems facing technical staff, tools and strategies used to solve those problems, and the (happy) result. Also included are reflections on what should have been done differently.

In March 1996 the Division of Academic Services at the University of Wisconsin-Madison decided to become a charter school with the Peoplesoft Corporation and to implement that company’s Student Administrative System. By August 1998 the first two modules, Campus Community and Admissions, were successfully implemented. This paper details the activities of the applications development staff in the months between March 1996 and August 1998 which led to that successful implementation. Our role was on the technical side, assisting the customer offices to implement the new system.

UW-Madison is a landgrant institution founded in 1848, the year that Wisconsin became a state. It is a large research institution with an undergraduate population of 25,000 and a graduate student body of 15,000. There are several professional schools on campus: Law, Medicine, Veterinary Medicine, Pharmacy, Nursing. It has one of the largest populations of international students in the United States.

The UW-Madison has a long and proud history of decentralized leadership which made implementing an integrated system challenging. The legacy system—known as Integrated Student Data System—is actually a collection of several systems which share data.

Some of these are:

• Undergraduate Admissions
• Graduate School
• Registrar
• Financial Aids
• Student Loans
• Housing
• Bursar

These systems were developed independently. Though data is passed from one system to another (when a semester begins, for example, Admissions data is converted to Registrar data), the systems were integrated after the fact rather than having been designed to work together. The legacy systems run on an IBM mainframe using IMS and DB2 and are primarily written in Cobol. The Peoplesoft-based system runs in a Unix and Oracle environment.

On the Madison campus Peoplesoft’s system was christened ISIS (Integrated Student Information System). Initially the project was structured with one module implementation team for each of the six modules in the Peoplesoft project. Those modules are

• Campus Community
• Admissions and Recruiting
• Financial Aid
• Student Records
• Student Financials
• Academic Advising.

Each team was comprised of functional people from the campus’s major administrative offices and technical people from the University’s Division of Information Technology. Each team was led by a functional person.

Over time, the approach of "one team per Peoplesoft module" changed. The contents of the Campus Community module were parsed out to the data custodians of the various data elements (most of these ended up in the Registrar’s Office). Also the number of teams for Admissions proliferated as separate teams were formed for undergraduate admissions, graduate admissions, medicine, and veterinary medicine.

At the UW-Madison professional schools admit students in June for the school year that is more 12 months in the future. The other admitting offices begin admitting in fall for the upcoming fall. The August 1998 implementation of the Admissions module did not involve the professional schools. The three remaining admissions offices--undergraduate, graduate, and continuing studies (or summer school)-- account for roughly 38,000 of our 40,000 students, however. With this "reduced scope" in mind, we began to identify what was needed to support Admissions processing in ISIS in production.

We had a few false starts. At first we planned to load only active students followed by a subsequent load of historical student records. This approach was rejected because it was just as difficult to extract the data for a limited number of students as it was to extract the data for all. The admissions offices needed to know if the University "knew" someone that may have attended previously. Examples are re-entry students or those with bachelor’s degrees now applying to graduate school or to a professional school. In addition, some special students are required to re-apply each term.

Initially, we also thought about identifying only the Campus Community data elements required by Admissions. These turned out to be:

• name
• date of birth
• Social Security number (where available; remember we have a large population of international students who in the legacy system had been assigned a pseudo-SSN)
• gender
• residency (we are a state university)
• address

This approach also was rejected and full Campus Community records were built for each person on the legacy system, including those converted from the Prospective Student system. Updating data fields within a record proved to be much more difficulat than refreshing the entire record. By building entire records the business office people had a chance to review data online with more functionality. Also the list of needed fields kept growing until only a few fields were missing from the list. Fields that were added were

• student career
• PIN
• citizenship
• visa
• residency
• email address

Early we perceived the need to synchronize legacy and ISIS data. Mercifully, the legacy system already had an ID cross reference table that was accessed by all legacy systems. This was expanded to include EMPLID. Every student on the legacy system was assigned an EMPLID and we agreed on a campus business practice. EMPLIDs were assigned only in ISIS for students that were truly new to the University. On initial data entry a student’s Social Security Number and Campus ID for old students could be entered. Once the panel was saved these fields were greyed out and could no longer be updated in ISIS. We assign pseudo-SSN based IDs for all students. A pseudo-ID process on the Unix side automatically assigned campus IDs for new students. An interface was built between the legacy system and ISIS so that all maintenance to Social Security Number and Campus ID was done in the existing 3270 transaction via a gateway between the two platforms. All the usual add capability for the legacy system was disabled. (Disabling 3270 transactions turned out to be an effective way to get people’s attention that ISIS was going live.)

If a record needed to be added to legacy, it was first entered in ISIS and then added to the legacy system using a transaction we developed that pulled basic demographic information back to legacy as well as EMPLID and Campus ID. Then this record could be used in any traditional legacy function that was still enabled. Employing this methodology we were able to keep database keys synchronized across platforms.

We converted admissions three weeks before the beginning of the fall term. By providing the "backload to legacy" functionality we were able to support late applicants, especially late registrants, and we could continue to support peripheral systems such as the NSLDS Clearinghouse.

Data was extracted from legacy files according to the Peoplesoft table it was destined to populate. Cobol was the programming language. We considered various tools and methods, including Convoy. We rejected Convoy, not merely because we would have had to purchase it, but because it produces SQRs that ran very slowly in our environment and we had a lot of records.

Ultimately we used a combination of Import Manager and SQL Loader. Import Manager has the advantage of applying all the Peoplesoft business rules to the data being loaded. The disadvantage is that it is time-consuming. We had 1.6 million Campus Community records of data to load. The conversion team came up with a clever technique. Import Manager was used to load a small data set and to edit small sets of extracted data for corre ctness and completeness. The online panels were reviewed to guarantee that they functioned correctly. When we were satisfied that the functioning was correct, the entire data set was loaded with SQL Loader.

An interesting sidelight is that SQL Loader runs more quickly on the Unix receiving platform than on the mainframe. The reason is that on the mainframe the data was flowing on a unit basis and on the Unix it flowed in blocks. The unit of work is the basis of moving data through the FTP pipeline; on the mainframe it was essentially one record at a time. By FTPing the data to the Unix box before loading the data transferred in blocks with many records per block. It was substantially faster loading because it was no longer competing for FTP resources.

In June of 1998 a dry run of the conversion took place. We did this for several reasons. One was to get a sense of the time requirements of the extract jobs and the load process. The other was to provide the administrative offices a two-month window of opportunity to see what the converted data would actually look like and to define any adjustments needed to the extract programs.

The dry run went flawlessly. The jobstreams ran from 4:00 p.m. Friday until 7:00 p.m. Monday. Only after the real conversion in August did the functional people identify any changes to the conversion programs. But the dry run reassured operations and technical staff that the conversion could be done over one weekend.

Customers were told that the system would be unavailable on the Monday following the conversion weekend. This turned out to be true since the last load was not completed until 6 p.m. that Monday. The delay was not due to problems with new technology and toolsets. Rather an FTP data set was too large to be received by the Unix box; once it was divided into three data sets, things proceeded.

The numbers were impressive. Forty-two foundation tables were loaded before the dry run either manually or by Import Manager or by SQL Loader. A priority sequence for the table loading was identified. Ten staff worked for 4 months to map data, write extract programs, and develop load scripts. These were applications staff. For the dry run and the actual load a DBA was needed, too. Twenty programs extracted data to create load data sets for forty-two ISIS tables. There was an FTP job for each table. The entire process of extracting and loading took sixty hours for 22 million rows of data.

The production conversion in August 1998 was actually less smooth than the June dry run. Some extract jobs abended over the weekend. Based on the timings learned from the June dry run, we told users that the system would be available at 8:00 a.m. Tuesday morning. In actuality, the system was not available until 10:00 a.m., when a functional office completed some security administration. Once that was done, the system was operable and all our work was done.

Great! We were glad to have that job successfully behind us. While the actual data conversion was done but other work awaited us, including Communications, Checklists, test score processing, producing lists and labels. These functions kept us busy for months.

What did we learn? What we would do differently, given another chance? There were technical and non-technical lessons learned. First, the technical lessons:

• It was crucial to have a way to link the old ID to the new EMPLID.

• Validating the data mapping with Import Manager was useful. It identified the wrong assumptions we had made about how the data is used within the Peoplesoft system.

• The dry run was imperative. It satisfied programming staff and Production Services staff that indeed the conversion was possible in a single weekend.

From a technical perspective, we were satisfied with the results. But we’re technologists, not users of the data. We could have improved our communication with the many users of the old system and the new system. Despite what we thought were clear messages about "no more updates in the legacy system", customers called to report that 3270 transactions no longer worked after the middle of August.

We should have sat down with the major users and walked cases through the system between June and August. This would have forced a review of the data at the operational level. Instead some tables had to be corrected after the August load.

Conversion of data from the legacy Prospective Student system was technically successfully but we inadvertently created multiple records for the same entity. A person was on ISIS more than once with different EMPLID keys. Peoplesoft does not provide an easy mechanism to merge data like we had in our legacy system. We developed two utilities, Find Value and Delete Value, to assist in managing the data in instances like this. An audit of the data to be converted would have forestalled production problems in admissions processing.

Despite these second thoughts, we reused the techniques learned in the Admissions project for the Student Records data conversion (scheduled in March 1999). If imitation is the sincerest form of flattery, this reuse is the clearest sign that the techniques were successful.