CoppinState
College
A Model For Data Clean-Up, Translation and Conversion
Coppin State College: Vell Lyles, Harold Watkins,
Anthony Watkins
USM PeopleSoft In House Conference November 22, 2002
CoppinState
College
Data Conversion
“Conversion of data from an existing system is one of the most challenging tasks you will accomplish in installing your PeopleSoft Student Administration and PeopleSoft Contributor Relations system.”
From People Books: Preparing for Conversion and Project Planning
CoppinState
College
Data Clean Up
“One of the largest time-consuming tasks of the conversion project can be data clean up.”
From People Books: Preparing for Conversion and Project Planning
CoppinState
College
The Foundation: Data Access
• Data Access is at users’ fingertips
• Query modules are on desktops of enrollment management, faculty and staff
• Modules include admissions, financial aid, academic advisement, retention
CoppinState
College
Note: Field left blank until student admitted and residency verified.
Double Click #1 for underlying applicants
CoppinState
College
OIT-User-Regeis Partnership
• System saves money
• Saves time
• Enforces standards
• Functional team used for verification not initiation
• Target bad data (Look up tables)
CoppinState
College
Topics Covered
• Managing the Complexities of PeopleSoft
• Data Translation Tables And Data Verification
• Address Translation
• Data Translation: Grueling Lessons
CoppinState
College
Managing the Complexities of PeopleSoft
The Regeis Referential Index for PeopleSoft (RRIFP)
CoppinState
College
Access #1: Drop Down Box
Access #2: Navigate Sequentially (19,622 Tables/Views/State Records/Other)
CoppinState
College
View of Coppin Real Data (459,435 records)
Totals Controls:1. SIS/FOCUS = 459,533
2. focDownloadTranscript = 459,533
3. PS_STDNT_ENRL = 459,435
CoppinState
College
“Begin” Timeline
• Interactive Design/Prototype Begins January 6, 2003
• Initial load of student information complete by January 6, 2003
CoppinState
College
Term Translation Table Derived programmatically (starting point)
Valid from Spring 1900 to Fall 2899
CoppinState
College
Lookup Tables
Admit File – 13 Degrees Awarded – 2 Term Enrollment – 14 Schedule File – 2 Student Information – 5 Test Scores – 1 Transcript - 3
Double Click to Identify Students
CoppinState
College
Mapping 5 Digit Zip to Zip+4
• Purchased Official Post Office database (40,339,493 records)
• Follow Postal Addressing Standards as stated in Publication 28 of the United States Postal Service
• Not Trivial
CoppinState
College
Coppin Zip4 Statistics
• 74,005 – Total Records in SIS since 1988• 67,073 ( > 90%) – Successfully Parsed • 53,749 ( > 80%) – of Parsed Addresses
Mapped to Zip+4 Extension• 13,324 ( < 20%) – of Parsed Addresses Not
Mapped to Zip+4 Extension• 10,549 (79%) – of Addresses Not Mapped
Have Suggestions
CoppinState
College
Microsoft Access: If Set Warnings Is False
• Primary Key SSN and Address Type: Accept first permanent address, silently ignores all others
• Age Range = 0 to 255: Silently sets student born on 11/15/2025 to null
• Grade of A<space><space> set to A (auto right trims)
CoppinState
College
Microsoft SQL Server: Obstinate
• Like Jogging– Hate While Doing It– Great When It’s Over
CoppinState
College
Microsoft SQL Server
• Violation of primary key = ABORT• Age of student born 11/15/2025 = ABORT• Invalid Record = ABORT• Invalid Data Type = ABORT• Field Too Small = ABORT• A<space><space> = A<space><space>
(no auto right trim)
CoppinState
College
Questions?
• Contact Ms. Vell Lyles at [email protected]
• Mr. Harold Watkins at [email protected]