Reporting on Student with Multiple University IDs

Last Update: 4/13/2020

Overview

IA needs to report on students using a single University ID. Examples include determining when the student began their studies at IU (admit term), whether they were retained from one semester to another, and how long it took for
them to graduate. However, some students will have records under two or more University IDs in the Student Information System (SIS) tables and/or the IA snapshot tables.  

For this reason, IA has created a number of tables and views in the IU Data Warehouse (IUIE, DSS_RDS) that display a single, “unified” University ID for each person, along with the original University ID. IA uses these tables when determining a student’s “admit term,” “admit type,” and cohort for retention and graduation.

The following two tables have the students who have had two or more University IDs:

• PSE_PRSN_DUPL_UID_ST

o In this table, PRSN_UNIV_ID represents the “unified” University ID that IA uses for determining “admit term, “cohort,” etc. In contrast, PRSN_ORIG_UNIV_ID represents the University ID that was originally on the student’s record. Here is an example:

• PSE_DUP_ID_GT

o This is a copy of the table above, but with the ID column names “switched” and the current primary student name added in a separate column. In this table, PRSN_USE_THIS_UNIV_ID represents the “unified” University ID used by IA, and PRSN_UNIV_ID represents the University ID that was originally on the student’s record. Here is an example:

The remaining tables and views that show both the “unified” and “original” University IDs for students are copies of existing DSS_RDS tables. In cases where a student has two equivalent rows under two different University IDs, a reporting column PRSN_DUPL_ID_SEL_NBR is available to select the preferred reporting record. (The preferred row will have the number 1 in this column.) Here is an example from the IA admit term/type table

(IR_ADMT_TERM_TYP_PIT_NUID_T):

Tables and Views 

Below is a partial list of tables and views that show both the “unified” and “original” University IDs for students. The tables and views in the first column all have these three columns in addition to their source table columns:

Background

Here is a brief background summary for those who would like to better understand these cases. In general, the SIS “search-match” process for creating a student record prevents duplicate University IDs. As a result, only about 2600 cases exist (as of April 2020) where persons have been assigned two or more IDs in the SIS.  In the SIS tables, staff from University Student Services and Systems (USSS) routinely check for cases where a student may have two University IDs in the SIS. In those cases, a preferred University ID is identified, and the other (nonpreferred) University ID has the associated person name changed to begin with XXXDupUse, followed by the preferred University ID and person name. Here is an example where a student went through this process twice. In this case, the student’s “preferred” record in yellow has the student’s name without the “scrambled” prefix XXXDupUse:

In some cases, USSS staff will invoke a process that “merges” the records in most SIS tables so that they are listed under the student’s “preferred” University ID. However, this “merge” process is not possible in some cases where
Student Financials history exists under multiple University IDs. Also, this “merge” process does not affect IA snapshot tables.

Finally, USSS staff enter a Service Indicator on the student record with the “original” University ID that corresponds with the XXXDupUse rows displayed above. Here are examples: