ELIMINATE MANUAL SEARCHING AND DUPLICATE CREATION WROBUST SEARCH

ELIMINATE MANUAL SEARCHING AND DUPLICATE CREATION W/ROBUST SEARCH/ MATCH PROCESS FOR FILE PARSER LOADS July 12, 2017

PRESENTER Jane Wilson Director Admissions Operations George Fox University jwilson@georgefox. edu 4 years in Admissions

GEORGE FOX UNIVERSITY • • • 4, 140 enrolled students 2, 406 trad undergrads 14: 1 student-teacher ratio

GEORGE FOX UNIVERSITY & ORACLE • • Went live in 2008 Campus Solutions 9. 0 Bundle 43 Posted almost 37, 000 temp IDs last year

DATA LOAD PROBLEMS Delivered Search/Match/Post Parameters • Wrong updates overwrite good data • Duplicate creation • Must manually Search/Match countless suspended records

DATA LOAD SOLUTION 3 1 DATA FIX QUERY POST CHECK QUERY MATCH CHECK QUERY 2 SEARCH/MATCH 4 POST 5

DATA FIX QUERY Check data before running Search/Match

STEP 1: DATA FIX QUERY

SEARCH/MATCH Run Search/Match run control

SEARCH PARAMETER PROBLEMS Set Up SACR > System Administration > Utilities > Search/Match Parameters

SEARCH PARAMETER SOLUTION

STEP 2: SEARCH/MATCH SETUP Set Up SACR > System Administration > Utilities > Search/Match Rules The key is to test your rules against real data 1. Build rules, including a test rule that just entails First Name & Last Name 2. Build parameter, putting test rule after all other rules 3. Run Search/Match 4. Run query that shows test rule search order number and identify what other rule or adjustment to rules could be made 5. Purge suspense tables and repeat until you feel confident with your rules and parameters to go live

STEP 2: SEARCH/MATCH SETUP Set Up SACR > System Administration > Utilities > Search/Match Rules High Level Rule Example

STEP 2: SEARCH/MATCH SETUP Set Up SACR > System Administration > Utilities > Search/Match Rules Mid Level Rule Example

STEP 2: SEARCH/MATCH SETUP Set Up SACR > System Administration > Utilities > Search/Match Rules Low Level Rule Example

STEP 2: SEARCH/MA TCH

MATCH CHECK QUERY 1. Prevent duplicates 2. Prevent overwritten data

STEP 3: MATCH CHECK Query fields QUERY

STEP 3: MATCH CHECK QUERY USE Duplicate Prevention

STEP 3: MATCH CHECK QUERY USE Overwrite prevention

STEP 3: MATCH CHECK QUERY CRITERIA • A screenshot of alternating staging and production fields is in a previous slide • Include only those Temp IDs that are Update ID, Add New ID, Error, or Suspended status • Add a Drilling URL somewhere so you can easily click into any staging tables that you may need to update

STEP 3: MATCH CHECK QUERY CRITERIA Filter out Search Rule Order Numbers you are certain are exact matches

STEP 3: MATCH CHECK QUERY Macro – how built • Insert columns before data points • Build formula in extra columns • Name example =IF(K 74=L 74, "", "X") • Address example • =IF(LEFT(T 76, 2)<>"PO", IF(LEFT(T 76, 15)=LEFT(V 76, 15), "", IF(LEFT(T 76, 6)= LEFT(V 76, 6), "", "X")), "X")

STEP 3: MATCH CHECK QUERY Google Sheet • R-click column, select conditional formatting, build rule Show duplicate rows: =countif(Q$2: Q$5000, Q$2: Q 5000)>1 Show discrepancies: =SEARCH("X", $H 2)

POST Run Post run control

STEP 4: POST

POST CHECK QUERY Troubleshoot any errors, manually Search/Match if necessary

STEP 5: POST CHECK QUERY Looks for any temp ID that hasn’t posted Loaded Suspended Error It has a drilling URL that links you to Staging Tables in case you need to troubleshoot or manually Search/Match/Post any

SIMPLE POSTING PROCESS DATA FIX QUERY Check data before running Search/Match 1 MATCH CHECK QUERY 1. Prevent duplicates 2. Prevent overwritten data SEARCH/MATCH Run Search/Match run control 2 3 POST Run Post run control POST CHECK QUERY Troubleshot any errors, manually search/match if necessary 4 5

REFERENCE LINKS Search/Match on Steroids – Using Delivered Functionality Specifying Search/Match Parameters for Batch Processing People. Books – Search/Match Setup

CONTACT INFO Jane Wilson Director of Admissions Operations George Fox University jwilson@georgefox. edu THIS PRESENTATION ARE AVAILABLE FOR DOWNLOAD FROM THE CONFERENCE SITE AT HTTPS: //WWW. HEUG. ORG/PAGE/US-ALLIANCECONFERENCE-FILES NOTE: SESSIONS FROM PREVIOUS HEUG CONFERENCES ARE ALSO AVAILABLE.

CONCLUDING THOUGHTS ANY QUESTIONS?