UIRR Reporting on Students with Multiple University IDs

UIRR needs to report on students using a single University ID for various purposes, including determining admit term, retention, and graduation time. However, some students have records under multiple University IDs in the Student Information System (SIS) tables and/or UIRR snapshot tables.

For this reason, UIRR 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. UIRR 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
  • In this table, PRSN_UNIV_ID represents the “unified” University ID that UIRR 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

  PRSN_UNIV_ID	PRSN_ORIG_UNIV_ID
  0000056789	0000034567

• PSE_DUP_ID_GT
  • 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 UIRR, and PRSN_UNIV_ID represents the University ID that was originally on the student’s record.

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 UIRR 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: PRSN_UNIV_ID, PRSN_ORIG_UNIV_ID, and PRSN_DUPL_ID_SEL_NBR.

Table/View	source	Description
IR_COHORT_NEW_UID_V	IR_COHORT_GT	Fall undergraduate cohorts by institution
IR_COHORT_CEN_INST_GT	IR_COHORT_GT and IR_CEN_TRM_SNPSHT_GT	Cohorts for both fall and spring, and for all careers by institution
IR_COHORT_CEN_GT	IR_COHORT_GT and IR_CEN_TRM_SNPSHT_GT	Cohorts for both fall and spring, and for all careers
IR_ADMT_TERM_TYP_PIT_NUID_T	IR_ADMT_TERM_TYP_PIT_T (subset with degree-seekers: IR_DEGR_ADMT_TERM_TYP_GT)	Current IR admit term/admit type values
IR_CEN_TRM_SNPSHT_NEW_UID_V	IR_CEN_TRM_SNPSHT_GT	IR Census-Term Snapshot
IR_STU_DEGR_SNPSHT_NEW_UID_V	IR_STU_DEGR_SNPSHT_GT	IR Degree Snapshot
IR_DEGR_ADMT_TRM_LG_DG_1_V	IR_DEGR_ADMT_TRM_LG_DG_1_T (subset of IR_LGCY_DEGR_INDVL_ST with a university ID)	IR Legacy Degree Snapshot
IR_DEGR_ADMT_TRM_LG_CEN_1_V	IR_DEGR_ADMT_TRM_LG_CEN_1_T (degree-seeking subset of IR_LGCY_CENSUS_ST for students with a university ID)	Degree-seeking IR Legacy CensusTerm (excluding annual ICHE)
IR_NDEG_ADMT_TRM_LG_CEN_1_V	IR_NDEG_ADMT_TRM_LG_CEN_1_T (non-degree subset of IR_LGCY_CENSUS_ST for students with a university ID)	Non-degree IR Legacy CensusTerm (excluding annual ICHE)
SR_STU_TERM_DRVD_COL_NEW_UID_V	SR_STU_TERM_DRVD_COL_MT (view: IR_STU_TERM_DRVD_COL_GT)	Active Student Term rows (same rows as SR_STU_TERM_GT)
SR_INA_STU_TERM_DRV_COL_NUID_V	SR_INA_STU_TERM_DRVD_COL_MT (view: IR_INA_STU_TERM_DRVD_COL_GT)	Inactive Student Term rows
SR_ENRL_DRVD_COL_NEW_UID_V	SR_ENRL_DRVD_COL_MT (selected columns from SR_ENRL_ST)	Active Student Enrollment (by class number, institution, career, and term)
SR_INA_ENRL_DRVD_COL_NEW_UID_V	SR_INA_ENRL_DRVD_COL_MT	Inactive Student Enrollment (by class number, institution, career, and term)
IR_STU_DEGR_PLAN_3_NEW_UID_V	IR_STU_DEGR_PLAN_3_MT (degree portion of SR_STU_DEGR_ST)	Active Student Degrees (awarded/conferred)
IR_INA_STU_DEGR_PLAN_3_NUID_V	IR_INA_STU_DEGR_PLAN_3_MT (degree portion of SR_STU_DEGR_INACTV_ST)	Inactive Student Degrees (awarded/conferred)
IR_PS_ADDRESSES_NEW_UID_V	IR_PS_ADDRESSES_MT (all address rows from ODS_PSE)	All Person Addresses

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 UIRR 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: