1. Pain Free Duplicate Record Merging Denise Cordrey Emily Carr University of Art + Design dcordrey@ecuad.ca Kelly Nyman HEITBC/OA Solutions Kelly.Nyman@oasolutions.ca

2. Who We Are? Consortium of eight schools in British Columbia Collaborate on all aspects of Colleague Range in FTE’s from 500 to 7,000 6 Service Bureau schools 2 Stand alone schools OA Solutions is our Service Provider

3. Where We Are? Camosun College College of New Caledonia College of the Rockies Emily Carr University of Art + Design Nicola Valley Institute of Technology North Island College Northern Lights College Northwest Community College

4. Developing a Duplicate Merge Utility Duplicates! Worked with our Service Provider to develop a way to deal with the problem of duplicate ID’s First developed the XMGD Merge Utility in 2003 Further enhancements have been made over the last two years

5. Items To Consider What hasn’t been developed yet: Financial Aid Colleague Advancement SQL server has not been tested What it can’t do: Merge duplicate accounts when the ‘merged from’ ID is used in Payroll There is no restore method after process has been run

6. General Overview • Identify Duplicate Records • Determine where the ID’s are used • Determine which ID to keep • Pre-steps and Report to run • Running the Merge utility • Post steps and Clean up

7. Identifying Duplicate Records Reports XIBD (bday) and XIDL (name) One person coordinates all student duplicates Confirms if it is a true duplicate Identifies the source – Credit/Continuing Studies and forwards them to be merged Organizations and Vendors are handled by our Finance folks

8. Where ID’s Are Used • Determine where the ID’s are used • Use the Lookup resolution screens to determine the “Where Used” information • NAE • ORGP • VEND • This also helps to determine which records to keep and which to merge

9. NAE Screen

10. Determining Which ID To Keep Duplicate ID’s can group themselves across a number of different applications: • CF, ST and HR • CF and ST • CF only • ST only

11. ID Used: CF, ST, HR • Keep the ID that has any Employment history (EMP) or HR history (HRP) • No EMP of HRP activity? Then keep the one with the most “where used” codes • If both ID’s are equal in “where used” then keep the ID with the most Transactions Note: If a ID has Employment history or has HR history you can not use this ID to “merge from”

12. ID Used: CF and ST If both ID’s are equal in “where used” codes then keep the ID with the most Transactions. For the number of CF transactions look on VENI For the number of ST transactions look on STAC & ARAI

13. ID Used: CF or ST only When an ID has been used in CF only You have 2 options: Review the VENI form and keep the ID that has the most transactions Keep the account with the latest CORE information When an ID has been used in ST only Keep the ID that has the most transactions Review the STAC & ARAI forms for the number of ST transactions

14. Retaining/Discarding The ID that is to kept be will be referred as “Merge To” ID The ID to be removed is referred as “Merge From” ID

15. Temporary Naming Of An ID When the merge process cannot be dealt with in a timely manner To prevent other users from adding more transactions to the “Merge From” ID. We need to rename the “Merge From” ID to reference the ‘merge to ID’ In this example “Duplicate See 0002570”

16. Duplicate Look Up

17. Pre-Merge Preparation - ST • Fields that this utility does not merge • Capture Core information for both records before merging the ID’s • NAE Name and Address Entry • ORGP Organization Profiles • BIO Biographic information • EMPL Employment information • FINF Foreign Person Information • EMER Emergency Information • DADD Additional Demographics • FNM Formatted Names • ADSU Address Summary

18. Pre-Merge Preparation - ST TRAN -Transcript for both IDs. Use the transcript grouping(s) that capture all credit types ARSP - AR Statement Print report for both IDs. With a start year of 1960 STAL – Student Academic Level. For the “Merge From” ID, SACP Student Academic Program SACP. Resolution form only Check that there is no activity on the ’Merge From’ ID in the Finance Application.

19. Pre-Merge XMGR Run XMGR to provide comparison reports of the modified files This will identify anything that was missed with the “Where used” After the merge is completed you can match XMGR post list to verify that the merge was successful

20. XMGR

21. XMGR Report

22. XMGD

23. XMGD Report

24. Post-Merge XMGR • Run the XMGR report again and keep the report • Compare the Pre and Post XMGR report • Retaining the report for future use will help un-merge the ID’s if needed

25. Post-Merge XMGR

26. Manual Post–Merge Processes NAE – Name and Address Entry ADSU– Address Summary (ensure that all of the address history has copied) EMAIL – XMGD does not merge email information. In the account you are going to keep, ensure that the email types, addresses and preference flag are correct. BIO – Biographic Information FINF – Foreign Person Information (check that statuses have copied correctly) EMER – Emergency Information DADD – Additional Demographics TRAN - Transcript Print for both accounts. Use the transcript Grouping ‘ALL’ so that you can see CS, GS and OFT courses. STAL – Student Academic Level SACP – Student Academic Program VENI –Vendor History (review the vendor forms if applicable) RTSL – Recalculate Term Status Load (recalculate if applicable)

27. Duplicate Record Delete When you are sure that all of the information has copied over correctly, go to the ‘merge from’ ID record and delete it.

28. Merge/Convert ProcessThe Nuts and Bolts

29. XMGR • XMGR role in the Merge/Conversion • Ensures to the user that the files converted properly • Provides debug info to illustrate what files have been converted • Help if needed to unmerge properly Note: XMGR is coded identical to XMGD

30. Pre Process • Load Pre – defined lists in XMGD that exist for files and fields. • One list is where ID is part of the Primary Key for files. • The other list is for the ID that is a field in a file. • Verify if “Merge From” ID used is not one of the following: HR, Financial Aid, Benefactor Note: There are over 270 fields effected by this process

31. The Main Process • Loops through each list creating Select statements to gather records based on the “Merge From” • Changes the record to the “Merge to” ID • Some files will call on a separate GOSUB so that extra processing that can be done • Backup record in Backup file • Naming convention is filename*recordID

32. Post Process Error reports is created when the file could not be changed or ID’s are equal to each other Conversion report is created Delete Web access to WebAdvisor on “Merge From” account NOTE: If an error occurs based on a file could not be read the process will stop immediately. You would have to do research of the cause and manually edit records to ensure conversion/merge can be done properly. This includes un-merging records already merged.

33. Unmerge Process • An IT or database admin will need to analyze each and every record that been effected by the process • Review backup’s and reports • Determine if the file has been touched since the date of the merge • Revert change either in colon prompt or use EDRC form (although EDRC can be tricky and should be avoided)

34. Tips and Best Practices • Try to keep your data clean and teach Users to avoid duplicates • Keep all pre and post screen shots and reports of the merge • Ensure that the ID’s are correct before placing a Yes to proceed