Using PDL for Admissions Data Loads Southwest HEUG

Using PDL for Admissions Data Loads Southwest HEUG Conference Session 3005 Tuesday, July 17, 2018 11: 15 am – 12: 05 pm Great Room 3 University of Nevada, Las Vegas

PRESENTER Sky Hieda Business Systems Analyst / Functional Architect Modules: Admission & Campus Community Enterprise Application Services – Enrollment & Student Services sky. hieda@unlv. edu University of Nevada, Las Vegas

About UNLV Location: Las Vegas, NV Founded: 1957 Identity: Public, Co-ed Enrollment: 28, 000 Mascot: Hey Reb School Colors: Scarlett & Gray University of Nevada, Las Vegas

UNLV Campus Solutions People. Tools 8. 54. 30 CS 9. 0 Bundle 49 Migrating to People. Tools 8. 55 and CS 9. 2 by December 1, 2018 University of Nevada, Las Vegas

Agenda/Contents • Brief Introduction to PDL • Components of PDL for delivered test score loads • Using PDL for custom test score/data loads • Using File Parser to load admissions data • Search Match Post Setup and Configuration • Tips and Tricks • Questions University of Nevada, Las Vegas

Introduction to PDL Prospect Data Load – ties in existing functionality and allows to map almost any external data file into staging and production tables using search/match processing Three staging areas - Application staging; Prospect/Admissions staging; and Constituent staging PDL replaced the old External Test Score load process University of Nevada, Las Vegas

Components of PDL • Data Update Rule Entry - Set how the person record is updated • Transaction Setup - Define the data update rules and search match rules • Field Conversion Definition - Define any fields that need to be converted to a value in the system (not required) • Context Definition - Define your parent/child relationships • File Mapping Definition - Map fields from the external file to their respective staging tables • File Parser Setup - Loading data into staging tables • Transaction Management Process - Applies search match rules and posting data to production University of Nevada, Las Vegas

Components of PDL • Data Update Rule Entry - Set how the person record is updated • Transaction Setup - Define the data update rules and search match rules • Field Conversion Definition - Define any fields that need to be converted to a value in the system (not required) • Context Definition - Define your parent/child relationships • File Mapping Definition - Map fields from the external file to their respective staging tables • File Parser Setup - Loading data into staging tables • Transaction Management Process - Applies search match rules and posting data to production University of Nevada, Las Vegas

Data Update Rule Entry Main Menu > Set Up SACR > System Administration > Utilities > Constituent Transaction Mgmt > Data Update Rule Entry University of Nevada, Las Vegas

Data Update Rule Entry Update Action Types Do Not Update - prevents the constituent's entity data from being updated Add if Blank - adds the entity data only if the constituent does not have the entity data Update/Insert - update or insert the constituent's entity data Safe Update (For Non-Typed Entities Only) - update or insert the constituent's entity data only if the incoming data is populated – will not overwrite a value in the database with a blank from the file. University of Nevada, Las Vegas

Data Update Rule Entry Affiliation Overrides Use affiliation overrides for applying different update rules to ID’s with different affiliations. University of Nevada, Las Vegas

Components of PDL • Data Update Rule Entry - Set how the person record is updated • Transaction Setup - Define the data update rules and search match rules • Field Conversion Definition - Define any fields that need to be converted to a value in the system (not required) • Context Definition - Define your parent/child relationships • File Mapping Definition - Map fields from the external file to their respective staging tables • File Parser Setup - Loading data into staging tables • Transaction Management Process - Applies search match rules and posting data to production University of Nevada, Las Vegas

Transaction Setup Allows you to assign parameters for how you want your transaction to run Define the data update rule entry Transaction Options ( Use Batch for FP) Partition Data (We use Transaction) Main Menu > Set Up SACR > System Administration > Utilities > Constituent Transaction Mgmt > Transaction Setup University of Nevada, Las Vegas

Transaction Setup Allows you to assign the Search/Match Parameter and rules to be used to load the transaction University of Nevada, Las Vegas

Components of PDL • Data Update Rule Entry - Set how the person record is updated • Transaction Setup - Define the data update rules and search match rules • Field Conversion Definition - Define any fields that need to be converted to a value in the system (not required) • Context Definition - Define your parent/child relationships • File Mapping Definition - Map fields from the external file to their respective staging tables • File Parser Setup - Loading data into staging tables • Transaction Management Process - Applies search match rules and posting data to production University of Nevada, Las Vegas

Field Conversion Definition Main Menu > Set Up SACR > System Administration > Utilities > File Parser > Field Conversion Definition The Conversion Definition allows you to identify one to one conversions, thus translating the value of a data point on the incoming file directly to another value to be used internally. Example using ethnicity code: University of Nevada, Las Vegas

Components of PDL • Data Update Rule Entry - Set how the person record is updated • Transaction Setup - Define the data update rules and search match rules • Field Conversion Definition - Define any fields that need to be converted to a value in the system (not required) • Context Definition - Define your parent/child relationships • File Mapping Definition - Map fields from the external file to their respective staging tables • File Parser Setup - Loading data into staging tables • Transaction Management Process - Applies search match rules and posting data to production University of Nevada, Las Vegas

Context Definition Main Menu > Set Up SACR > System Administration > Utilities > File Parser > Context Definition Identify your parent child relationships to use in the mapping definition. Set default values for any values you want automatically defaulted in the mapping definition Mark a value visible or not visible on the mapping definition. University of Nevada, Las Vegas

Components of PDL • Data Update Rule Entry - Set how the person record is updated • Transaction Setup - Define the data update rules and search match rules • Field Conversion Definition - Define any fields that need to be converted to a value in the system (not required) • Context Definition - Define your parent/child relationships • File Mapping Definition - Map fields from the external file to their respective staging tables • File Parser Setup - Loading data into staging tables • Transaction Management Process - Applies search match rules and posting data to production University of Nevada, Las Vegas

File Mapping Definition Main Menu > Set Up SACR > System Administration > Utilities > File Parser > File Mapping Definition 1. Define the file and specify the file type - create a custom name for your mapping, assign the context definition, conversion definition, file type and delimiter 2. Assign a field name and format each incoming field in the file 3. Map the records in the context definition and set desired mapping actions 4. Preview layout allows you to preview the file mapping layout in a tree format 5. Preview data allows you to test a file and what the inserted values will look like without actually posting to the staging tables University of Nevada, Las Vegas

Components of PDL • Data Update Rule Entry - Set how the person record is updated • Transaction Setup - Define the data update rules and search match rules • Field Conversion Definition - Define any fields that need to be converted to a value in the system (not required) • Context Definition - Define your parent/child relationships • File Mapping Definition - Map fields from the external file to their respective staging tables • File Parser Setup - Loading data into staging tables • Transaction Management Process - Applies search match rules and posting data to production University of Nevada, Las Vegas

Run File Parser Setup Main Menu > Set Up SACR > Syst Admin > Utilities > File Parser > Run File Parser University of Nevada, Las Vegas

Components of PDL • Data Update Rule Entry - Set how the person record is updated • Transaction Setup - Define the data update rules and search match rules • Field Conversion Definition - Define any fields that need to be converted to a value in the system (not required) • Context Definition - Define your parent/child relationships • File Mapping Definition - Map fields from the external file to their respective staging tables • File Parser Setup - Loading data into staging tables • Transaction Management Process - Applies search match rules and posting data to production University of Nevada, Las Vegas

Transaction Management Process Main Menu > Stud Adm > Application Transaction Mgmt > Transaction Management Process Main Menu > Stud Recruit > Prospect/Admissions Data Mgmt > Transaction Management Process University of Nevada, Las Vegas

Custom Data Loads Setting up PDL for custom data loads uses the same steps as a delivered test score load. University of Nevada, Las Vegas

Using PDL for Custom Test Score/ Data Loads Scenario The Chemistry Department administers an electronic chemistry placement exam to students in their department. The placement exam is used as pre-requisites into various courses and in some cases, a factor in determining admission into the Chemistry major. The department needed a way to batch load these scores into the database. University of Nevada, Las Vegas

Using PDL for Custom Test Score/ Data Loads 1. Data Update Rule Entry 2. Transaction Setup 3. Field Conversion 4. Context Definition 5. File Mapping University of Nevada, Las Vegas

1. Data Update Rule Entry Create a new data update rule for the custom data load and set your updates actions of how you would like your entities to be updated. Set your affiliation overrides University of Nevada, Las Vegas

2. Transaction Setup Create a New transaction for the custom data load. Add the Data Update Rule. If this is a score load use the same setup as one of the delivered loads. Set your Search/Match in the Search/Match Setup tab University of Nevada, Las Vegas

3. Field Conversion Definition This step is only required if you need to translate values. Use ‘Copy Field Value Conversion’ page to copy and edit an existing conversion definition. University of Nevada, Las Vegas

4. Context Definition Create a new context definition or copy and edit and existing one. Tie in your conversion definition. Minimum records to create a new context definition for a custom test score load: ü SCC_STG_CONSTIT – no parent record needed. This is the only record for SCC_STG_CONSTIT that uses counter definition for SCC_TEMP_ID: ü SCC_SL_TRNMAP – Set parent record as SCC_STG_CONSTIT where SCC_TEMP_ID is inherited from parent ü SCC_STG_PERSSA ü SCC_STG_PDE ü SAD_PDL_HDR_STG ü SCC_STG_NAME University of Nevada, Las Vegas

4. Context Definition Additional records for custom test score loads: ü SCC_STG_TEST – parent SCC_STG_CONSTIT ü SCC_STG_TESTCMP – parent SCC_STG_TEST University of Nevada, Las Vegas

5. File Mapping Definition Create a new file mapping definition and tie in your context definition and conversion definition. Setup the file type and delimiters. University of Nevada, Las Vegas

5. File Mapping Definition Setup the names of the fields in your external file by indicating the field number/position (flat or delimited), field type (character, date, number) and field format the field (upper, lower, etc). University of Nevada, Las Vegas

5. File Mapping Definition Add the records mapped in the context definition and set how you want your custom data to be loaded through the mapping actions. University of Nevada, Las Vegas

File Mapping Definition Skip if Blank bypasses writing to the staging record if the incoming corresponding field is blank in the data file. Force Insert writes to your staging records when you are not using the direct from file mapping action for a particular record. University of Nevada, Las Vegas

File Mapping Definition Calculated Mapping Action Allows you to assign an Application Class that does some kind of logic and returns a value for a calculated field University of Nevada, Las Vegas

5. File Mapping Definition Calculated mapping actions for a particular field reference the associated sort order number. You will need to make sure any changes to the sort order is reflected in the calculated field setup. University of Nevada, Las Vegas

File Mapping Definition Field Conversion The Value Conversion tab is where you tie in the conversion for any data points. University of Nevada, Las Vegas

File Mapping Definition You map to a record multiple times if you are needing to bring in different data points for the same field. ACT’s file layout was updated when they delivered new test components. To load all 15 test scores components (pre and post 2015), you map to record SCC_STG_TESTCMP for as many test components you are needing to post. University of Nevada, Las Vegas

File Mapping Definition University of Nevada, Las Vegas

Loading Admissions Application Data At UNLV, we do not use AAWS to load admission applications, however, we use File Parser and some of the delivered components used in PDL to load data into the delivered AAWS staging tables. University of Nevada, Las Vegas

Using File Parser to Load Applications Utilizes similar functionality to PDL but uses the application staging records. 1. Data Update Rule Entry 2. Transaction Setup 3. Field Conversion Definition 4. Context Definition 5. File Mapping Definition University of Nevada, Las Vegas

1. Data Update Rule Entry UNLV syncs biodemo data with other instances within our Nevada System of Higher Education (NSHE) institutions. Entity’s update actions are set to meet the business needs of NSHE and the admissions/registrar office. UNLV does not have affiliations setup to not overwrite data for certain populations. University of Nevada, Las Vegas

2. Transaction Setup The transaction setup is different from test scores because you are using application staging. At UNLV, we have different data update rules and search/match rules for application and test scores load. University of Nevada, Las Vegas

3. Field Conversion Definition Setting up field conversions for applications is similar to test score loads. Using a record (table) name validates the internal values against that record. The default value will be used if any of the external values in the file are blank. University of Nevada, Las Vegas

3. Field Conversion Definition You can set to halt processing and produce and error message. The error message uses the message catalog to display error message. University of Nevada, Las Vegas

4. Context Definition You will need the same context records as the test score loads - SCC_STG_CONSTIT, SCC_SL_TRNMAP, SCC_STG_PERSSA, SCC_STG_PDE, SCC_STG_NAMES Additional context definition records need for application data loads: SAD_APL_DAT_STG – parent record is SCC_STG_CONSTIT. Set ADM_APPL_NBR to Unique Counter for the mapping action with a counter definition: SAD_APP_CSQ_STG – Uses parent record SAD_APL_DAT_STG SAD_APL_PRG_STG – Uses parent record SAD_APP_CSQ_STG SAD_APL_PLN_STG – Uses parent record SAD_APL_PRG_STG University of Nevada, Las Vegas

5. File Mapping Definition Our file mapping is Setup to process multiple row types because our external file contains multiple rows. The section ID value is listed in the electronic file and separates the row from the previous. The field number corresponds to the starting column of the particular section ID value row. University of Nevada, Las Vegas

5. File Mapping Definition University of Nevada, Las Vegas

5. File Mapping Definition Force insert is not an option when using multiple row types. In order to force insert a record you need to have at least one field mapped directly from the file. To ensure that you are not force inserting blank data, your data points that are mapped directly from the file need to be populated from the incoming file. At UNLV, to ensure that we force insert record SCC_STG_PERSSA, we have a field on the incoming file that is ALWAYS populated with a ‘Y’ or ‘N’ value. We map those data points directly from the file to the required FERPA field. University of Nevada, Las Vegas

5. File Mapping Definition Field SCC_TRANS_STS and SCC_TRANS_STS_DT are not required fields on record SAD_APL_DAT_STG, however, if these data values are not set correctly, your load will not work correctly. Set SCC_TRANS_STS to ‘SB’ and the SCC_TRANS_STS_DT to ‘%Date’. University of Nevada, Las Vegas

Search Match Setup We use different search match rules and parameters for applications and test score data loads. University of Nevada, Las Vegas

Setting Up Search Match Rules and Parameters 1. Understand your data 2. Define the fields to use in your search match rules 3. Create a search parameter based on the rules you Setup 4. Add new fields (if any) to display as a field for search/match results 5. Create search results code and Setup fields to display for search/match results 6. Define how you want to handle matches 7. TEST and review search/match periodically University of Nevada, Las Vegas

1. Understand Your Data In order for your system to efficiently search for matches through Search/Match you need to understand where your data is coming from and how it is affecting your database. Search/Match could reduce the amount of manual intervention needed so staff going through staging spend the time on records that require the intervention. University of Nevada, Las Vegas

1. Understanding UNLV’s Data Search match parameters for applications use 13 rule codes that use a combination of fields: Search match parameters for test scores use 14 rule codes that use a combination of fields: National ID Last Name Search First Name Search Middle Name Date of Birth Gender Address 1 City State Postal Phone Email University of Nevada, Las Vegas

1. Understand Your Data Application Search/Match Rules vs. Test Score Search/Match Rules University of Nevada, Las Vegas

2. Define the fields to use in SM rules Setup search match rule codes to use in search match Ad Hoc Search – bypasses the predefined search standards custom search without predefined operands and without limiting the character to evaluate. Required – requires a value in the search field for the search rule. University of Nevada, Las Vegas

3. Create the SM Parameter Create a new search match parameter code and set all of your search match rules in sequence order. The rule codes are in sort order sequence from lowest to highest with the lowest number being the highest confidence of a match. University of Nevada, Las Vegas

4. Search Match Result Fields Many search/match results fields are delivered and predefined. You can also create a new search/match result field and choose the record name and field that will be available within the search results. UNLV created two new search result fields for search/matching on test score loads; Address 1 and Telephone. University of Nevada, Las Vegas

5. Search Match Results Create a new search match result code and setup the fields you want to display in the search results. University of Nevada, Las Vegas

5. Search Match Results You can set different display options depending on how you want your results to be displayed. University of Nevada, Las Vegas

6. Define Matches Define how you want the system to handle matches based on finding one match or multiple matches. Set how you want to handle when the system finds no match. Setup the matches on both the Transaction Setup page and the Transaction Management Page. University of Nevada, Las Vegas

6. Test Well and Review Often After reviewing suspend test score records in staging, admissions requested to have level 110 Setup to update on one match instead of suspend. Previous search match 110 - all PDL fields w/out Name - (DOB, gender, Address 1 (first 15 characters), State, Postal (begins w/ first 5), phone, and email. Updated search match Level 110 was moved up to 90 and will update on one match. University of Nevada, Las Vegas

Tips & Tricks University of Nevada, Las Vegas

Example 1: Using Force Insert University of Nevada, Las Vegas

Using Force Insert Problem: With force insert selected on record SCC_STG_TEST to map SAT I and SAT II, it created blank SAT II test components if the test taker did not take the SAT II test. Solution: Added two new conversion fields and used fields SAT Grade Level 1 and SAT Subject Test ID 1 -1 directly from the file. SAT Grade Level 1 was used because if the student took the SAT test this field will always be populated. If the student took SAT II, field SAT Subject Test ID 1 -1 will always be populated. If this fields are not populated on the incoming file, either the student didn’t take it or it is an error. University of Nevada, Las Vegas

Using Force Insert Added two new field conversions named SAT Subject Test Taken and SAT Taken. University of Nevada, Las Vegas

Using Force Insert Unchecked force insert and used field SAT Grade Level 1 (SAT I) and SAT Subject Test ID 1 -1(SAT II) directly from the file in the TEST_ID staging field. University of Nevada, Las Vegas

Using Force Insert Added the conversion field University of Nevada, Las Vegas

Example 2: Using Force Insert for Multiple Row Types University of Nevada, Las Vegas

Using Force Insert on Multiple Row Types Problem: The file mapping used to load undergraduate admissions application is using multiple row types. Force insert is not an option when your file contains headers. We needed a way to make sure we are able to insert required record SCC_STG_PERSSA record. Solution: We are using a VA Benefit field directly from the file that is always populated as ‘N’ in the file. University of Nevada, Las Vegas

Using Force Insert on Multiple Row Types On the file layout University of Nevada, Las Vegas

Using Force Insert on Multiple Row Types On the file mapping definition University of Nevada, Las Vegas

Example 3: Duplicate Scores University of Nevada, Las Vegas

Duplicate Test Scores Scenario: Some of the files we receive contain duplicate AP scores - scores we received a month ago are being sent again OR a student completed a new test and we are receiving the old scores in the files along with the new scores. Per the vendor, they will send the scores again because the student requested they be sent again. When posting from the PDL staging tables, the scores that already exist on the Test Results page are being updated with a new ‘date loaded' which then makes it look like they are new scores that we haven't received before. Issue with this process: Because we make admission decisions and articulation rules off of test scores, we have a need to keep the historical record of the date loaded based on the decision that was made at that time the data exists in the system. University of Nevada, Las Vegas

Duplicate Test Scores Business Need Request: We don't want the system to update the date loaded field on the test results page for an existing test score record if the data elements on test component, test score, and test date match. Solution: (Required a technical fix from our Software Developers) An update to the code in App Package SCC_SL_TRANSACTION was made to handle duplicate test score not overriding the date loaded date on the Test Results page. The code checks for an existing record. If the process finds a match it does not update the DATE_LOADED field on record STDNT_TEST table. University of Nevada, Las Vegas

Example 4: Updating the Search/Match/Post Process University of Nevada, Las Vegas

Search Match Post Process Problem: When UNLV first went live with CS in 2009, we didn’t have a way to search for matches in the system if someone had a previous name. For example, if someone got married and changed their last name and is now applying with a new last name and their existing records contains their maiden name. Solution: We created a new NAME_TYPE labeled ‘APP’. We updated all of the third party applications to collect a previous or former name and map that to the APP NAME_TYPE field. Our Software Developers created a technical fix to update the Search/Match/Post process to also search for a former name using ‘APP’ as the NAME_TYPE. University of Nevada, Las Vegas

Example 5: DOB for AP Test Scores University of Nevada, Las Vegas

DOB for AP test scores Problem: AP updated their file layout and is sending the birthday in the file as mm/dd/yy. There is an issue with the birthday not loading (loading blank) in staging into the Birthdate field on record SCC_STG_CONSTIT if the DOB in the file is in the year 2000 (i. e. DOB listed in file as 01/01/00). Solution: Our Software Developers created a technical fix by creating a calculated field to use as a mapping action on the BIRTHDAY field that loads the DOB correctly if it has a birth year of 2000. University of Nevada, Las Vegas

DOB for AP test scores University of Nevada, Las Vegas

Example 6: Test Score Validation Error (Bug 21104352) University of Nevada, Las Vegas

Test Score Validation Problem: Validation now occurs on the Additional Test Values (CAF) in the Test Result page. Earlier to the bug fix, the Test Results page could have been saved with any random Additional Test Field values. Now after the bug fix, there is a validation that occurs on the Additional Test Field. The Additional Test Field value should be selected from the Field Prompt in Test Results page. After the bug fix, when trying to update the Test Results page when a test ID exists with additional Test Data, the user would get an ‘Invalid Value’ error message. University of Nevada, Las Vegas

Test Score Validation University of Nevada, Las Vegas

Test Score Validation The manual workaround is to delete all of the Additional Test Data for each Test ID. Solution: (Posted by Ronit Weiss on HEUG https: //www. heug. org/p/fo/st/topic=5&post=368112#p 36 8112) Ran this SQL to determine which CAF attributes need to be added to record SAD_TESTDT: -- Make sure all CAF attributes are also Setup for the CAF record SELECT DISTINCT A 1. SCC_CAF_ATTRIB_NM FROM PS_SAD_TESTDT_CAF A 1, PS_SCC_CAF_ATTRIBS D WHERE D. SCC_CAF_ATTRIB_NM = A 1. SCC_CAF_ATTRIB_NM AND NOT EXISTS (SELECT 'X' FROM PS_SCC_CAF_RECATTR B WHERE B. RECNAME = 'SAD_TESTDT' AND B. SCC_CAF_ATTRIB_NM = A 1. SCC_CAF_ATTRIB_NM); University of Nevada, Las Vegas

Navigation: Main Menu > Set up SACR > Common Definitions > Common Attributes Setup > Record Context Test Score Validation Results: Setup the 17 attributes returned University of Nevada, Las Vegas

Questions? ? University of Nevada, Las Vegas