Creating Custom PDL Processes Presenters Jamie Choate Software

Creating Custom PDL Processes

Presenters Jamie Choate, Software Systems Specialist III People. Soft Functional Lead for Admissions Interests: improvements using existing functionality and modifications, automating process, reporting and auditing processes Hobbies: Crocheting, playing on the drums! Taylor Rutherford, EM Analyst Interests: Enhancements, improving the user experience Hobbies: Spending time with family, boating

About The University of Texas at Dallas People. Soft since 2009 On People. Tools 8. 53. 10 23, 000 students Currently using the PDL for several data loads and test loads, not currently using it to load applications. Delivered: ACT, GRE, GMAT, IELTS, TOEFL, SAT Custom: TSI, ELS, CLA+, ALEKS, IB, CLEP, THEA is in progress

Contents Brief introduction to what the PDL does and why we should use it Brief introduction to the components of a PDL process Creating a custom PDL

What is the Prospect Data Loader? The prospect Data Loader users a file parser to load data into staging, and then posts it to the person record The Run File Parser step loads data into staging based on mapping the user defines (Similar to the older Load Test Scores process) The Transaction Management Process search, matches, and posts the data to the person record (Similar to the older Search/Match Post Test Scores process)

Why use PDL? Oracle is no longer supporting the older method of loading test scores once the PDL layout is delivered—this is especially important since we’re seeing updates to SAT and ACT very soon There are improved features, such as an integrated search match PDL allows functional users to create, update, and manage test score or data loads

PDL Basics 1. Data Update Rule Entry 2. Transaction Set Up 3. Field Conversion Definition 4. Context Definition 5. File Mapping Definition 6. Functional Security 7. Setting up the Run File Parser Process 8. Setting up the Transaction Management Process

Setting up the PDL 1. 2. 3. 4. 5. 6. 7. 8. Data Update Rule Entry (Setting how your individuals that are updates will have their person record updated) Transaction Set Up (Defining how your data load will use the data update rule entry, and defining your search match rules) Field Conversion Definition (Defining which fields, if any, need to be translated from an external value to match an existing internal value) Context Definition (Defining which tables and fields you would like to be able to map to) File Mapping Definition (Where all previous steps come together to map incoming data into People. Soft) Functional Security Setting up the Run File Parser Process (Loading data into staging) Setting up the Transaction Management Process (Search matching and posting data into the person record)

Data Update Rule Entry The data update rule entry uses user defined rules to update the data for ‘Update IDs’ – newly created IDs will load everything from staging to the person record. Affiliation Overrides can be used to apply a different set of update rules to those IDs with certain affiliation

Setting up the PDL 1. 2. 3. 4. 5. 6. 7. 8. Data Update Rule Entry (Setting how your individuals that are updates will have their person record updated) Transaction Set Up (Defining how your data load will use the data update rule entry, and defining your search match rules) Field Conversion Definition (Defining which fields, if any, need to be translated from an external value to match an existing internal value) Context Definition (Defining which tables and fields you would like to be able to map to) File Mapping Definition (Where all previous steps come together to map incoming data into People. Soft) Functional Security Setting up the Run File Parser Process (Loading data into staging) Setting up the Transaction Management Process (Search matching and posting data into the person record)

Transaction Setup The transaction is where your data update rules, search match rules, and which staging tables you will write to, all come together. For delivered score and data loads, transactions are named beginning with SAD_ At UT Dallas we use the Ignore option under Search/Match Setup, because setting people to suspended under PDL prevents additional automatic search matching on the record, but ignore allows this, working more like suspend under the older score loading method.

Setting up the PDL 1. 2. 3. 4. 5. 6. 7. 8. Data Update Rule Entry (Setting how your individuals that are updates will have their person record updated) Transaction Set Up (Defining how your data load will use the data update rule entry, and defining your search match rules) Field Conversion Definition (Defining which fields, if any, need to be translated from an external value to match an existing internal value) Context Definition (Defining which tables and fields you would like to be able to map to) File Mapping Definition (Where all previous steps come together to map incoming data into People. Soft) Functional Security Setting up the Run File Parser Process (Loading data into staging) Setting up the Transaction Management Process (Search matching and posting data into the person record)

Field Conversion Definition You may not always need a field conversion definition—you will only need one if you need to convert external values to values that you have within People. Soft:

Setting up the PDL 1. 2. 3. 4. 5. 6. 7. 8. Data Update Rule Entry (Setting how your individuals that are updates will have their person record updated) Transaction Set Up (Defining how your data load will use the data update rule entry, and defining your search match rules) Field Conversion Definition (Defining which fields, if any, need to be translated from an external value to match an existing internal value) Context Definition (Defining which tables and fields you would like to be able to map to) File Mapping Definition (Where all previous steps come together to map incoming data into People. Soft) Functional Security Setting up the Run File Parser Process (Loading data into staging) Setting up the Transaction Management Process (Search matching and posting data into the person record)

Context Definition The context definition is where you define which records you want to use, set up the parent record for your records, set up inheritance between records, and set up your unique counter that creates your temporary IDs for your staged records. Be careful not to create orphan data by incorrectly establishing relationships between your tables. If you are using a field conversion definition, you sync it with your context definition, here.

Setting up the PDL 1. 2. 3. 4. 5. 6. 7. 8. Data Update Rule Entry (Setting how your individuals that are updates will have their person record updated) Transaction Set Up (Defining how your data load will use the data update rule entry, and defining your search match rules) Field Conversion Definition (Defining which fields, if any, need to be translated from an external value to match an existing internal value) Context Definition (Defining which tables and fields you would like to be able to map to) File Mapping Definition (Where all previous steps come together to map incoming data into People. Soft) Functional Security Setting up the Run File Parser Process (Loading data into staging) Setting up the Transaction Management Process (Search matching and posting data into the person record)

File mapping Definition The file mapping definition is where all your PDL setup pieces come together, including your transaction, context definition, and field conversion definition (if applicable). You do so across multiple tabs: File definition: used to set up the context definition, file type, delimiter, and conversion definition. File layout: used to define field position and format Mapping: used to map values using the field conversion and the context definition Preview layout: used to view the record tree. We will be skipping it in these slides Preview Data: Here, you can load a sample file and preview what incoming data will look like in staging

File Mapping Definition File Definition Tab Here, you link your context definition, and conversion definition, and set your file type

File Mapping Definition File Layout Tab Here, you define your fields, their location, set the format, and type. Flat file: CSV file

File mapping Definition File Mapping Tab You must map required fields Here is where you tie in your value conversions by going to the value conversion tab for your relevant record and field, and choosing your conversion field

File mapping Definition Preview Data Tab Once your setup is complete, you can attach a file with one row of data, and you can see how the test data will load into staging for each record, based on your mapping setup! It is normal for some fields and records to appear blank here.

Setting up the PDL 1. 2. 3. 4. 5. 6. 7. 8. Data Update Rule Entry (Setting how your individuals that are updates will have their person record updated) Transaction Set Up (Defining how your data load will use the data update rule entry, and defining your search match rules) Field Conversion Definition (Defining which fields, if any, need to be translated from an external value to match an existing internal value) Context Definition (Defining which tables and fields you would like to be able to map to) File Mapping Definition (Where all previous steps come together to map incoming data into People. Soft) Functional Security Setting up the Run File Parser Process (Loading data into staging) Setting up the Transaction Management Process (Search matching and posting data into the person record)

Functional Security Users will need functional security to access the transaction— otherwise, in staging, the transaction will show as plain text, and not a clickable link: User without functional security will see User with functional security will see Called CTM Transaction Security For each user needing the access, grant the access for the transactions. You can also grant ALL access if users will need access to all PDL transactions

Setting up the PDL 1. 2. 3. 4. 5. 6. 7. 8. Data Update Rule Entry (Setting how your individuals that are updates will have their person record updated) Transaction Set Up (Defining how your data load will use the data update rule entry, and defining your search match rules) Field Conversion Definition (Defining which fields, if any, need to be translated from an external value to match an existing internal value) Context Definition (Defining which tables and fields you would like to be able to map to) File Mapping Definition (Where all previous steps come together to map incoming data into People. Soft) Functional Security Setting up the Run File Parser Process (Loading data into staging) Setting up the Transaction Management Process (Search matching and posting data into the person record)

Run File Parser Uses your file mapping to load your external file into staging When you create your run control, you choose your file mapping ID and path, and save it. This is similar to the load test scores functionality in the older method of loading scores

Setting up the PDL 1. 2. 3. 4. 5. 6. 7. 8. Data Update Rule Entry (Setting how your individuals that are updates will have their person record updated) Transaction Set Up (Defining how your data load will use the data update rule entry, and defining your search match rules) Field Conversion Definition (Defining which fields, if any, need to be translated from an external value to match an existing internal value) Context Definition (Defining which tables and fields you would like to be able to map to) File Mapping Definition (Where all previous steps come together to map incoming data into People. Soft) Functional Security Setting up the File Parser (Loading data into staging) Setting up the Transaction Management Process (Search matching and posting data into the person record)

Transaction Management Process The Transaction Management Process is what posts your data from staging to the person record. This is similar to Search/Match/Post Test Scores using the older method of loading test scores. Transaction Management utilizes your transaction setup to determine: How to handle the loaded data Which search match parameters to use What to do if no match is found

Staging tables and PDL For PDL test and data loads, two staging tables are used: Constituent Staging and Prospect/Admissions staging. The tables are linked together, so you do not need to work through both staging tables for each person. You can access the staging tables through the navigation, and they are also linked through the transaction name from constituent staging. Constituent staging contains bio/demo data such as DOB, email, name, phone, ethnicity, citizenship, etc. Prospect/Admissions staging contains prospect data, such as prospect plan, interests, and education data, in addition to test score data and participation.

This presentation will be available online. For more information on PDL, see: People. Book information for PDL: Setting up field conversion definition Setting up context definitions Setting up a file mapping definition Staging tables for PDL Setting up CTM general information Understanding CTM general information Adding new staging tables and entities for PDL (developer reference) Setting up the Entity Registry (developer reference)

Custom PDL Build For our custom PDL, we will be using CLEP as an example. CLEP is a credit-by examination program that is administered by the College Board. We receive the scores electronically as a flat file, but these steps can be modified based on the file type.

Data Update Rule Ø Set Up SACR System Administration Utilities Date Update Rule Entry Ø For CLEP we only used the Data Update Rule Entry tab and not Affiliation Overrides. The Data Update Rules determine how current IDs will be updated. We chose not to update most data with CLEP and only to address if blank, mainly considering that most students whom we receive the scores from already have an ID in our system.

Transaction Setup Ø Set Up SACR System Administration Utilities Transaction Setup Ø The transaction is where staging tables and components are selected and search/match is setup. In a custom load, the transaction setup can be based on delivered loads that are similar.

Ø On the transaction setup tab, you select the tables used in the transaction. These will be the same standard tables that you use in score and data loads.

Ø On the Search/Match Setup tab, you specify your rules and search orders for search/match.

Field Conversion Definition Ø Set Up SACR System Administration Utilities File Parser Field Conversion Definition Ø For CLEP we only needed to convert two fields. We converted the file value for test type and the file value for gender in the CLEP data to our internal People. Soft values.

Ø Test type field value conversion

Ø Gender field value conversion

Context Definition Ø Set Up SACR System Administration Utilities File Parser Context Definition Ø The context definition allows you to create your record tree that will be where you pull from for file mapping. With a custom build, you can copy from a People. Soft delivered PDL or build it from scratch. You will select the same conversion definition that you built previously.

Ø If you do build it from scratch, you will want to be sure to set up your unique counter mapping action in the SCC_TEMP_ID field of the SCC_STG_CONSTIT parent record. Ø Be sure to also update the counter definition

Ø For CLEP we used the records that are essential to any PDL: SCC_STG_CONSTIT (the parent record of all of these required records) SCC_SL_TRNMAP SCC_STG_PERSSA SCC_STG_PDE SCC_STG_NAMES SAD_PDL_HDR_STG Ø We also added the additional records that we use for scores.

File Mapping Definition Ø Set Up SACR System Administration Utilities File Parser File Mapping Definition Ø Here you also have the option to copy a delivered file mapping definition to work from or you can create a brand new one. We tend to create a new one each time because once the context definition is changed, it will blank out the mapping tab. Ø For CLEP, on the File Definition tab, we selected the context definition that we had previously built along with the same conversion definition and “flat” for file type.

Ø The file layout tab allows you to enter the field names and starting positions based on the data layout file that you will receive.

Ø On the mapping tab, you select all the records that you will use and the mapping actions for the fields that you receive, such as birth date.

Ø Be sure to update the value conversions that you previously designated. We created a field value conversion for gender, so we included that in the file mapping. First update these value conversions on the mapping tab, and then the value conversion tab.

Ø It is really helpful to upload a sample file on the Preview Data tab to see if your fields are mapped correctly.

Run Control Setup Ø To begin running the PDL, you will need to create a new run control. Ø Enter the file path to your score file.

Ø Once functional security is granted, your users can run the file parser and begin working the scores loaded in the staging table.

Tips and Tricks Make sure you do not change the format on the name SEARCH fields from uppercase—these are used to search match and if they are not set to uppercase, you won’t be able to search match on name, because the underlying name rules look for the names being searched on to be uppercase. Using PDL, the status of ‘Ignore’ is like the older method of scores; ‘Suspend’ DO NOT default USERID on SCC_STG_CONSTIT, or set it as %Operator. ID– There is a bug where posting a transaction that has a user. ID set will change the ID of the person posting the transaction to the ID on the score/data load/etc. If you are having an issue loading scores and aren’t sure what the problem is, use Preview Data on your suspected problem person—the error message given in File Mapping Definition is much more helpful than the one in the log file.

Tips and Tricks – sub parsing a field If you encounter a situation where you receive multiple fields in one incoming data field, you can use sub parse field to separate that data into multiple fields. A good example of this is for IB scores, because in the IB flat file layout, name is one field, separated by a comma. We need to pull this into two fields, first name, and last name (and in some cases your name search fields) To use Subparse fields, select the subparse fields checkbox for your field: And choose your delimiter. In this example, the name is in the format of last, first so we use a comma as the delimiter. The occurrence box is used to determine location in relation to the delimiter. To determine which location to use as your field, enter the number of where the field name is located. Occurrence‘ 1’ is the field before the delimiter. This will also apply when you have multiple delimiters and multiple sub sections.

Tips and Tricks Calculated mapping actions You may need to create calculated mapping actions for your custom PDLs. At UT Dallas, so far we have not created custom calculated mapping actions— we are re-mapping first name and last name as the name search fields for most of our custom loads, where we already receive names without formatting, or where we directly map to EMPLID and don’t use name for searching—only to create the record in staging. Oracle offers some helpful information about calculated mapping actions: http: //docs. oracle. com/cd/E 56917_01/cs 9 pbr 4/eng/cs/lscc/task_Creating. Calculat ed. Mapping. Application. Class. Objects-b 18000. html#topofpage http: //docs. oracle. com/cd/E 56917_01/cs 9 pbr 4/eng/cs/lscc/task_Commonly. Used. C alculated. Mapping. Application. Class. Objects-b 88000. html#topofpage

Troubleshooting, some common error messages: 1) You may see something like this error message if you are using the delivered search rule PSCS_130 and there is a single quote in the address that falls in the first 6 characters of the address This is because the lone single quote is causing an issue with the search match rules logic, by closing the SQL statement.

Troubleshooting, some common error messages: A fix for this: Don’t use PSCS_130, or edit PSCS_130 to look for more characters, or a full match—there is still the potential for error, but this should help reduce it. We also have a case in with oracle for this issue.

Troubleshooting, some common error messages: 2) You may see this message if your data file has the same component taken on the same date for the same person, or if there is an issue with a component when you run file parser:

Questions? Contact Information: Jamie Choate Email: jfc 083000@utdallas. edu