Since spring 2010, IU has followed guidelines from the Federal Integrated Postsecondary Education Data System (IPEDS) for collecting race and ethnicity self-reported data from students, faculty, and staff, and for reporting their race, ethnicity, and international status.
Reporting Ethnicity and International
Technical Guide Last Update: 11/8/2022
Overview
Since spring 2010, IU has followed guidelines from the Federal Integrated Postsecondary Education Data System (IPEDS) for collecting race and ethnicity self-reported data from students, faculty, and staff, and for reporting their race, ethnicity, and international status. Based on those guidelines, IU has created these derived fields in the Data Warehouse for reporting race, ethnicity, and international status:
| GT_COL_TECH_NM | GT_COL_LBL_NM |
| PRSN_DRVD_IPEDS_ETHNIC_CD | Derived IPEDS Ethnicity Code |
| PRSN_DRVD_IPEDS_ETHNIC_DESC | Derived IPEDS Ethnicity Description |
| PRSN_IPEDS_ETHN_DESC | Derived IPEDS Ethnicity Description |
Whenever possible, IU staff are encouraged to use these derived IPEDS ethnicity values which are available in the IUIE and Data Warehouse. Below are the valid values along with some sample Data Warehouse reports with these fields. Note that IU reports students, faculty, and staff with an IPEDS Ethnicity value of “US Nonresident” as International.
| PRSN_DRVD_IPEDS_ETHNIC_CD | PRSN_DRVD_IPEDS_ETHNIC_DESC |
| 1 | White |
| 2 | Black/African American |
| 3 | Hispanic/Latino |
| 4 | Asian |
| 5 | American Indian/Alaska Native |
| 6 | US Nonresident |
| 7 | Native Hawaiian/Pacific Islander |
| 8 | Two or More Races |
Sample Data Warehouse Views:
| GT_SCHEMA_NM | RO_TECH_NM | RO_LBL_NM |
| DSS | HRS_CENSUS_EMP_IU_GT | Census Employee (IU) |
| DSS_HR | HRS_CENSUS_EMP_FTE_GT | HRS Census - Employee FTE Fact Table View |
| DSS | ADM_CUR_APPL_GT | Student Admission Applications - Current rows |
| DSS_RDS | FA_STU_TERM_GT | Financial Aid Term |
| DSS_RDS | SR_STU_TERM_GT | Student Term |
| DSS_RDS | IR_CEN_TRM_SNPSHT_GT | Student Census-Term Snapshot |
| DSS_RDS | IR_FA_ENRL_DEGR_AWD_SUM_GT | IR Annual Enrolled-Degree Summary with Finaid |
This technical guide is a tool to help offices that are unable at times to use the IUIE or Data Warehouse. If those users need to rely on SIS or HRMS transactional data to replicate derived values in the Data Warehouse, they are encouraged to carefully study the derived logic in Oracle views and Informatica data transformations. In the sections below, the derivation of IPEDS Ethnicity from SIS and HRMS transactional tables is outlined to correspond to Oracle views and Informatica transformations.
Rationale for Derived Fields in the Data Warehouse
The Student Information System (SIS) and Human Resources Management System (HRMS) store a variety of transactional data that are not designed for reporting. Here are some reasons why derived fields like IPEDS Ethnicity have been created in the Data Warehouse:
Viewing Data Transformation Logic
Data Warehouse transformations are stored in a series of data objects and tools. Here are some of the objects and tools that populate the Data Warehouse from SIS, HRMS, and similar transactional systems:
Overview of where the logic for IPEDS Ethnicity is stored
Checking Your Understanding of the Logic
The IPEDS Ethnicity logic in the Oracle views and transformations above can be converted into a series of three sequential SQL statements, as shown below. Then, the results of your SQL can be compared to the Data Warehouse results to check your logic (see the last SQL statement below).
Note that the SQL below uses student SIS table views in the ODS_PSE schema. Equivalent results for employees can be found by using the same ODS_PSE table names below and simply changing the schema from ODS_PSE to
the ODS_HR (the HRMS schema). In the last SQL below, the SIS-based table DSS_RDS.PSE_HRS_PRSN_MT can be replaced with the HRMS-based table DSS_HR.HRS_PERSON_MT for equivalent employee results.
References
-- Step 1 - Get the current visa permit type for each person and categorize the visa permit types for IPEDS reporting.
CREATE TABLE QPS_VISA_PMT_DATA AS
SELECT vpmt.EMPLID, vpmt.COUNTRY, vpmt.VISA_PERMIT_TYPE, vpmt.EFFDT,
vpmt.PRSN_VPRMT_CTGRY_DESC,
TO_CHAR(vpmt.EFFDT,'YYYYMMDD') ||
DECODE(vpmt.VISA_PERMIT_TYPE,'N','1','9') ||
DECODE(vpmt.PRSN_VPRMT_CTGRY_DESC,'Naturalized US Citizen','9'
,'US Nonresident','7'
,'US Resident','5','1') ||
vpmt.VISA_PERMIT_TYPE AS PRSN_VPRMT_CTGRY_SORT
FROM (
SELECT A.EMPLID, A.COUNTRY, A.VISA_PERMIT_TYPE, A.EFFDT,
CASE WHEN a.COUNTRY = 'USA'
AND a.VISA_PERMIT_TYPE = 'NLR' --Now US Citizen-No Visa Reqd
THEN 'Naturalized US Citizen'
WHEN a.COUNTRY = 'USA'
AND a.VISA_PERMIT_TYPE IN
('ASY', --Asylee (Political asylum)
'LPR', --Permanent Resident
'PIP', --Public Interest Parolee
'REF', --Refugee
'HMP', --Humanitarian Parole
'DA') -- Def Action-Childhood Arrivals
THEN 'US Resident'
WHEN a.COUNTRY = 'USA'
AND a.VISA_PERMIT_TYPE IN
('EVY', --e-Verify
'I-9') --I-9 (Employment Eligibility)
THEN 'Employment Elig'
WHEN a.COUNTRY = 'USA'
AND a.VISA_PERMIT_TYPE IN ('ZZZ')
THEN 'Not Applicable'
ELSE 'US Nonresident'
END AS PRSN_VPRMT_CTGRY_DESC
FROM ODS_PSE.PS_VISA_PMT_DATA A
WHERE EFFDT = (SELECT MAX(EFFDT)
FROM ODS_PSE.PS_VISA_PMT_DATA A1
WHERE A.EMPLID = A1.EMPLID
AND A.DEPENDENT_ID = A1.DEPENDENT_ID
AND A.COUNTRY = A1.COUNTRY
AND A.VISA_PERMIT_TYPE = A1.VISA_PERMIT_TYPE
AND TRUNC(a1.EFFDT) <= TRUNC(SYSDATE))
) vpmt
/
CREATE UNIQUE INDEX QPS_VISA_PMT_DATAI ON QPS_VISA_PMT_DATA
(EMPLID, PRSN_VPRMT_CTGRY_SORT)
/
-- Step 2 - Get the subset of persons with a visa permit type that are reported as
International (US Nonresidents) for IPEDS Ethnicity.
CREATE TABLE QIPEDS_ETHNICITY_INTERNATIONAL AS
SELECT a.EMPLID
, '6' AS PRSN_DRVD_IPEDS_ETHNIC_CD
, 'US Nonresident' AS PRSN_DRVD_IPEDS_ETHNIC_DESC
, a.COUNTRY
, a.VISA_PERMIT_TYPE
, a.PRSN_VPRMT_CTGRY_DESC
FROM QPS_VISA_PMT_DATA a
WHERE a.COUNTRY= 'USA'
AND a.PRSN_VPRMT_CTGRY_DESC = 'US Nonresident'
AND a.PRSN_VPRMT_CTGRY_SORT =
(SELECT MAX(x.PRSN_VPRMT_CTGRY_SORT)
FROM QPS_VISA_PMT_DATA x
WHERE x.COUNTRY= 'USA'
AND x.PRSN_VPRMT_CTGRY_DESC IN ('US Nonresident','US Resident','Naturalized US
Citizen')
AND TRUNC(x.EFFDT) <= TRUNC(SYSDATE)
AND a.EMPLID = x.EMPLID
AND a.COUNTRY = x.COUNTRY
)
/
CREATE UNIQUE INDEX QIPEDS_ETHNICITY_INTERNATIONAI ON QIPEDS_ETHNICITY_INTERNATIONAL
(EMPLID)
/
-- Step 3 - Derive IPEDS Ethnicity from these sources:
-- a) Steps above that determined International persons (US Nonresidents) from
ODS_PSE.PS_VISA_PMT_DATA --> QIPEDS_ETHNICITY_INTERNATIONAL
-- b) The self-reported Hispanic/Latino flag for each student stored in
ODS_PSE.PS_ETHNICITY_DTL
-- c) The self-reported ethnic groups for each student stored in
ODS_PSE.PS_DIVERS_ETHNIC
CREATE TABLE QIPEDS_ETHNICITY AS
SELECT a.EMPLID,
CAST(CASE WHEN intl.EMPLID IS NOT NULL THEN intl.PRSN_DRVD_IPEDS_ETHNIC_CD
WHEN hispdtl.HISP_LATINO = 'Y' OR hispgrp.ETHNIC_GRP_CD = 'HISPANIC' THEN '3'
WHEN NVL(whitgrp.WHITE_NBR,0) + NVL(blckgrp.BLACK_AFR_AMER_NBR,0) +
NVL(asiagrp.ASIAN_NBR,0) +
NVL(amindgrp.AMER_INDIAN_ALASKA_NBR,0) + NVL(pacifgrp.PCFC_ISLDR_NBR,0) > 1
THEN '8'
WHEN whitgrp.ETHNIC_GRP_CD = 'WHITE' THEN '1'
WHEN blckgrp.ETHNIC_GRP_CD = 'AFRAM' THEN '2'
WHEN asiagrp.ETHNIC_GRP_CD = 'ASIAN' THEN '4'
WHEN amindgrp.ETHNIC_GRP_CD = 'AMIND' THEN '5'
WHEN pacifgrp.ETHNIC_GRP_CD = 'HAWAIIAN' THEN '7'
END AS VARCHAR2(1)) AS PRSN_DRVD_IPEDS_ETHNIC_CD,
CAST(CASE WHEN intl.EMPLID IS NOT NULL THEN intl.PRSN_DRVD_IPEDS_ETHNIC_DESC
WHEN hispdtl.HISP_LATINO = 'Y' OR hispgrp.ETHNIC_GRP_CD = 'HISPANIC' THEN
'Hispanic/Latino'
WHEN NVL(whitgrp.WHITE_NBR,0) + NVL(blckgrp.BLACK_AFR_AMER_NBR,0) +
NVL(asiagrp.ASIAN_NBR,0) +
NVL(amindgrp.AMER_INDIAN_ALASKA_NBR,0) + NVL(pacifgrp.PCFC_ISLDR_NBR,0) > 1
THEN 'Two or More Races'
WHEN whitgrp.ETHNIC_GRP_CD = 'WHITE' THEN 'White'
WHEN blckgrp.ETHNIC_GRP_CD = 'AFRAM' THEN 'Black/African American'
WHEN asiagrp.ETHNIC_GRP_CD = 'ASIAN' THEN 'Asian'
WHEN amindgrp.ETHNIC_GRP_CD = 'AMIND' THEN 'American Indian/Alaska Native'
WHEN pacifgrp.ETHNIC_GRP_CD = 'HAWAIIAN' THEN 'Native Hawaiian/Pacific Island'
END AS VARCHAR2(30)) AS PRSN_DRVD_IPEDS_ETHNIC_DESC
FROM ODS_PSE.PS_PERSON a
, QIPEDS_ETHNICITY_INTERNATIONAL intl
, (SELECT EMPLID, MAX(HISP_LATINO) as HISP_LATINO
FROM ODS_PSE.PS_ETHNICITY_DTL
WHERE HISP_LATINO = 'Y'
GROUP BY EMPLID) hispdtl
, (SELECT EMPLID, ETHNIC_GRP_CD
FROM ODS_PSE.PS_DIVERS_ETHNIC
WHERE ETHNIC_GRP_CD = 'HISPANIC') hispgrp
, (SELECT EMPLID, ETHNIC_GRP_CD, 1 AS WHITE_NBR
FROM ODS_PSE.PS_DIVERS_ETHNIC
WHERE ETHNIC_GRP_CD = 'WHITE') whitgrp
, (SELECT EMPLID, ETHNIC_GRP_CD, 1 AS BLACK_AFR_AMER_NBR
FROM ODS_PSE.PS_DIVERS_ETHNIC
WHERE ETHNIC_GRP_CD = 'AFRAM') blckgrp
, (SELECT EMPLID, ETHNIC_GRP_CD, 1 AS ASIAN_NBR
FROM ODS_PSE.PS_DIVERS_ETHNIC
WHERE ETHNIC_GRP_CD = 'ASIAN') asiagrp
, (SELECT EMPLID, ETHNIC_GRP_CD, 1 AS AMER_INDIAN_ALASKA_NBR
FROM ODS_PSE.PS_DIVERS_ETHNIC
WHERE ETHNIC_GRP_CD = 'AMIND') amindgrp
, (SELECT EMPLID, ETHNIC_GRP_CD, 1 AS PCFC_ISLDR_NBR
FROM ODS_PSE.PS_DIVERS_ETHNIC
WHERE ETHNIC_GRP_CD = 'HAWAIIAN') pacifgrp
WHERE a.EMPLID = intl.EMPLID (+)
AND a.EMPLID = hispdtl.EMPLID (+)
AND a.EMPLID = hispgrp.EMPLID (+)
AND a.EMPLID = whitgrp.EMPLID (+)
AND a.EMPLID = blckgrp.EMPLID (+)
AND a.EMPLID = asiagrp.EMPLID (+)
AND a.EMPLID = amindgrp.EMPLID (+)
AND a.EMPLID = pacifgrp.EMPLID (+)
/
CREATE UNIQUE INDEX QIPEDS_ETHNICITYI1 ON QIPEDS_ETHNICITY
(EMPLID)
/
-- Step 4 - Look for mismatches between the logic above and the Data Warehouse Person table.
SELECT a.EMPLID,
a.PRSN_DRVD_IPEDS_ETHNIC_CD,
a.PRSN_DRVD_IPEDS_ETHNIC_DESC,
b.PRSN_DRVD_IPEDS_ETHNIC_CD,
b.PRSN_DRVD_IPEDS_ETHNIC_DESC
FROM QIPEDS_ETHNICITY A
, DSS_RDS.PSE_HRS_PRSN_MT b
WHERE a.EMPLID = b.PRSN_UNIV_ID
AND (nvl(a.PRSN_DRVD_IPEDS_ETHNIC_CD,'X') <> nvl(b.PRSN_DRVD_IPEDS_ETHNIC_CD,'X')
OR nvl(a.PRSN_DRVD_IPEDS_ETHNIC_DESC,'X') <> nvl(b.PRSN_DRVD_IPEDS_ETHNIC_DESC,'X'))
AND rownum < 5
/