coppin state college a model for data clean-up, translation and conversion coppin state college:...
TRANSCRIPT
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
Data Clean Up:
A Current Example
CoppinState
College
Admit View: Data Verification
CoppinState
College
Note: Field left blank until student admitted and residency verified.
Double Click #1 for underlying applicants
CoppinState
College
Residency Code Verification
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
1
2 3 4
5
6 7
8 – Next Slide
CoppinState
College
8
CoppinState
College
Access #1: Drop Down Box
Access #2: Navigate Sequentially (19,622 Tables/Views/State Records/Other)
CoppinState
College
Access #3: Click Minimum Tables and Double Click Table
CoppinState
College
Double Click to View STDNT_CAREER Table
CoppinState
College
Click to Return to STDNT_ENR Table
CoppinState
College
Click
CoppinState
College
Printout Expands to Show All Information
CoppinState
College
Viewing PeopleSoft Sample Data
Click
CoppinState
College
View of Sample PS Data (16,308 records)
CoppinState
College
Double Click
Un-Check
Viewing Coppin Real Data
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
Design of Coppin’s Cloned Table
CoppinState
College
“Begin” Timeline
• Interactive Design/Prototype Begins January 6, 2003
• Initial load of student information complete by January 6, 2003
CoppinState
College
REGEIS Translation And Data Verification
(Starting Points)
CoppinState
College
Term Translation Table Derived programmatically (starting point)
Valid from Spring 1900 to Fall 2899
CoppinState
College
CourseID Translation Table (First Records)
CoppinState
College
CourseID Translation Table (Last Records)
CoppinState
College
CoppinState
College
CoppinState
College
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
CoppinState
College
Address Translation
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
Data Translation (Grueling Lessons Learned)
CoppinState
College
Microsoft Access: Sympathetic
• May be good
• May be not good
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]
CoppinState
College
Appendix: Data Translation
An Example Using SAFILE
CoppinState
College
Direct Import Into focDownloadFaAudt
CoppinState
College
A View to Translate Data to SQL Database (top half)
CoppinState
College
A View to Translate Data to SQL Database (bottom half)
Ensure valid record
CoppinState
College
“Transformed” Records: CSC-REGIS sets no data to Null; CSC-PeopleSoft will set no data to blanks?