pain free duplicate record merging l.
Skip this Video
Loading SlideShow in 5 Seconds..
Pain Free Duplicate Record Merging PowerPoint Presentation
Download Presentation
Pain Free Duplicate Record Merging

Loading in 2 Seconds...

play fullscreen
1 / 34

Pain Free Duplicate Record Merging - PowerPoint PPT Presentation

  • Uploaded on

Pain Free Duplicate Record Merging. Denise Cordrey Emily Carr University of Art + Design Kelly Nyman HEITBC/OA Solutions Who We Are?. Consortium of eight schools in British Columbia Collaborate on all aspects of Colleague

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'Pain Free Duplicate Record Merging' - lorene

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
pain free duplicate record merging

Pain Free Duplicate Record Merging

Denise Cordrey

Emily Carr University of Art + Design

Kelly Nyman

HEITBC/OA Solutions

who we are
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

where we are
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

developing a duplicate merge utility
Developing a Duplicate Merge Utility


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

items to consider
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

general overview
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
identifying duplicate records
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

where id s are used
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
determining which id to keep
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
id used cf st hr
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”

id used cf and st
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

id used cf or st only
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

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

temporary naming of an id
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”

pre merge preparation st
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
pre merge preparation st18
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.

pre merge xmgr
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

post merge xmgr
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
manual post merge processes
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)

duplicate record delete
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.

  • 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

pre process
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

the main process
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
post process
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.

unmerge process
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)
tips and best practices
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