No More Social Security Numbers

1. No More Social Security Numbers Dawn M. Boyer Yvonne M. Riley dmb1@psu.eduymr1@psu.edu Manager, Student Systems Data Warehouse Consultant http://ais.its.psu.edu/ssn/index.html Penn State University Administrative Information Services

2. No More Social Security NumbersMainframe – Student Systems Dawn M. Boyer dmb1@psu.edu Manager, Student Systems Penn State University Administrative Information Services

3. No More Social Security NumbersMainframe – Student Systems • History • Charge from University President • December 4, 2002 • SSN Confidential, Identity Theft • Implement 2004 • Proactive

4. No More Social Security NumbersMainframe – Student Systems • Impact • Over 20 Campuses plus Law and Medical School, World Campus • College/Departments – Local Systems • New ID+ Cards • Central Offices – Student Systems, Data Warehouse

5. No More Social Security NumbersMainframe – Student Systems • University Committees • Steering • Working Group • Sub-committees • Technology • Policy • Conversion Date – December Holiday Break 2004 • ID+

6. No More Social Security NumbersMainframe – Student Systems • Central ID Repository – CIDR file • Only University File with SSN • Restricted Access • Initial Creation – September 2004

7. No More Social Security NumbersMainframe – Student Systems • Student Systems (ISIS) • 44 Data base files – 41 Conversion programs • 2.5 million Person records • 88 million records • 350 million keys • SSN fields - Student, Instructor, Adviser, Parent, Employee

8. No More Social Security NumbersMainframe – Student Systems • What needs to be converted • All electronic files the contain SSN • Transaction logs • Data sets • Presentation (format) 9-NNNN-NNNN • Labels • Laser or pre-printed forms • Letters/emails • Processes that create students

9. No More Social Security NumbersMainframe – Student Systems • Trial Run – December 2003 • Worst file – 15 million records – 7 keys • Worst case – 2.84 hours per million record • 45 hours of processing • Changed Strategy – 12 files • Remove keys – 2 minutes (revert) • Convert File – 3 hours • Re-Add keys – 20 minutes per key – 2.5 hours (invert) • Total – 5.5 hours

10. No More Social Security NumbersMainframe System – Student Systems • Preparation • Step-by-step tasks • Meetings – every 2 weeks – Late Summer 2004 • Developers, DBA, Systems, Operations • Analysis of data storage • Crosswalk File – SSN, PSU ID • System Tuning • Audit logs – turned off • Redundant copy of data – turned off • System Priorities

11. No More Social Security NumbersMainframe – Student Systems • 4 Known Dates • December 19, 2004 – 12:01 AM • Disable Online Systems, Process Grades, Start Conversion • December 24, 2004 – Noon • Stop All Conversion Activities for the Holiday • December 26, 2004 – 8:00 AM • Continue Conversion Activities • January 3, 2005 – 7:00AM • Enable Online Systems

12. No More Social Security NumbersMainframe – Student Systems • Conversion • Official CIDR File – December 19 • File Back-ups – December 19 • Convert online files – December 19 & 20 • Technical and User testing of online systems – December 20 • Convert non-online files – December 21 & 22 • Batch testing – December 21 & 22 • Enable online Systems - December 22 – Noon

13. No More Social Security NumbersMainframe – Student Systems • Problems – • Data Integrity • SSN Changes • Last Minute Decisions • Communication filtering down

14. No More Social Security NumbersMainframe – Student Systems • What Worked – • Starting early • Keep it simple • Planning • Communication • Teamwork

15. No More Social Security Numbers Removing all SSN’s from Penn State’s Data Warehouse Yvonne M. Riley ymr1@psu.edu Data Warehouse Consultant Penn State University Administrative Information Services

16. No More Social Security NumbersPenn State’s Data Warehouse • My task • Convert all social security numbers to PSU ID’s. • Leaving no social security numbers on any table when tables were made available to general users. • 18 databases to convert • 120 tables • 150 million records • Analysis started 1 year prior to the conversion.

17. No More Social Security NumbersPenn State’s Data Warehouse • Where did the analysis start: • Met with our DBA personnel to discuss options get suggestions. • Identify key people, who would do what. • Identify problem areas. • How would we approach the coding. • Set up a timeline for myself.

18. No More Social Security NumbersPenn State’s Data Warehouse • Immediate Concerns – • What to do with records that don’t convert. • Decision was made to copy then to another database for cleanup later and then copied back into the original table. • What to do with old historical records that have incorrect social security numbers. • Steward offices provided us with the corrected SSN prior to the conversion, and we corrected the records.

19. No More Social Security NumbersPenn State’s Data Warehouse • Immediate Concerns – • Where would the CIDR file (holds the SSN and new PSU ID) reside? • Decision was made to put the CIDR file on the Warehouse. • We would use it to convert the Warehouse. • After the conversion was over, users could use this table to convert local systems.

20. No More Social Security NumbersPenn State’s Data Warehouse • What was the best way to convert the Warehouse? • Preserved our original tables. • Update programs would be completely step restartable. • Cloned the structure of each table to be converted adding an extra field at the end to hold the original SSN. • Created a view for each table that pulled the PSU ID for the SSN and moved the SSN to a new ‘holding’ field. • Kept our naming conventions for all views, cloned tables, scripts unique so they could easily be identified. • Example View Name: vssn_tbl_converted • Example Cloned Table Name: ssn_tbl_converted

21. No More Social Security NumbersPenn State’s Data Warehouse • Sample View Code CREATE VIEW dbo.vssn_tableone_scores AS Select psu_id as ‘SSN', Field1 as ‘Field1', Field2 as ‘Field2', Field3 as ‘Field3', SSN as 'ssn_hold' from tableone_scores left join ssnconversion..conversiontable on tableone_scores.ssn = ssnconversion..conversiontable.ssn

22. No More Social Security NumbersPenn State’s Data Warehouse • Update Programs were developed in 4 Phases: • Phase 1 -- Convert the Warehouse into cloned tables (leaving existing tables untouched). After phase 1. I verified. • Phase 2 -- Renamed tables, had steward office verify. • Original tables got renamed. • Cloned tables got renamed to original table name. • Phase 3 -- Dropped the extra ssn_hold field from all tables. • Phase 4 -- Dropped unconverted original tables.

23. No More Social Security NumbersPenn State’s Data Warehouse • Phase 1 Update code: • Count of records to be updated. • Truncate cloned table. • Using the newly created views, inserted records into new cloned table. • Count of records not updated. • Truncate on table where unconverted records were to be moved. • Copy unconverted records to new database. • Delete unconverted records. • Count of records in converted table.

24. No More Social Security NumbersPenn State’s Data Warehouse • The conversion weekend is here – • Midnight On Saturday Data Warehouse was made unavailable to user community. • Complete Backup of all databases was done. • Cloned tables were created . • Arrival of the official CIDR data file on Sunday. • Conversion of the Warehouse begins.

25. No More Social Security NumbersPenn State’s Data Warehouse • As the Warehouse is converting – • DBA monitored the SQL server. • I reviewed all logs for any errors. • We communicated by phone and email.

26. No More Social Security NumbersPenn State’s Data Warehouse • Problems – • Clean up of Historical Data. • Deadlines were given to all steward offices for use to have all cleanup information. • Steward offices were overwhelmed and could not meet the deadlines.

27. No More Social Security NumbersPenn State’s Data Warehouse • What Worked – • Sticking to my timeline – • Starting early and completing the majority of programming by September. • Keeping update programs as simple as possible and step restartable. • Verifying, verifying and re verifying everything.

28. Questions???