Resolved: "Gender (IPEDS)" Field Displays "Null" Values Across Several ERS Dashboards

Gender (IPEDS) was updated in production across a number of Chancellor's Office Reports dashboards to account for changes needed in the logic to use codes rather than descriptions.

Most dashboards where this was an impact are found in: 

Shared folders / Dashboards / Chancellor's Office Reports / IPEDS.

GC0007634 - Campus Data Warehouse: Gender IPEDS Logic Broken in Several SMTLs

The Campus Data Warehouse team is aware of a problem in production where the "Gender (IPEDS)" field displays NULL values across various ERS-based data models/dashboards (e.g. Admissions Funnel, ERS Student, ERS Degree, and Institution Summary). We are currently working on a solution. 


 

Ticket: 23636029 
Date created: Fri 11/15/24 10:27 AM Pacific Standard Time
Service: Report a Problem / Computer Software/Application Problem

Title: Gender (IPEDS) Logic Broken in Several SMTLs: Adm Funnel, ERS Student, ERS Degree, Institution Summary


The field "Gender (IPEDS)" is showing all NULL values across dashboards in both QuickSight and OBIEE due to a recent change to the description field.
Uploaded Image
Logic in the SMTL procedures in AWS needs to be updated to use codes rather than descriptions. 

Data models impacted: 
  • Admissions Funnel
  • ERS Student
  • ERS Degree
  • Institution Summary
Existing logic example from csdw.wcs_csu_ersd_smsp procedure using descriptions:
person_d.sex_descr = 'Male' THEN '1 - ' || 'Male'
WHEN wcs_person_d.sex_descr = 'Female' THEN '2 - ' || 'Female'
WHEN wcs_person_d.sex = 'N' AND WCS_CSU_GENDER_D.csu_gender_id IN ('10','12') THEN '1 - ' || 'Male'
WHEN wcs_person_d.sex = 'N' AND WCS_CSU_GENDER_D.csu_gender_id IN ('11','13') THEN '2 - ' || 'Female'
WHEN (wcs_person_d.sex = 'N' AND WCS_CSU_GENDER_D.csu_gender_id NOT IN ('10','11','12','13') AND SUBSTRING(wcs_person_d.person_id FROM 9 FOR 1) IN ('0','2','4','6','8')) THEN '2 - ' || 'Female'
WHEN (wcs_person_d.sex = 'N' AND WCS_CSU_GENDER_D.csu_gender_id NOT IN ('10','11','12','13') AND SUBSTRING(wcs_person_d.person_id FROM 9 FOR 1) IN ('1','3','5','7','9')) THEN '1 - ' || 'Male'
END AS "Gender (IPEDS)",

instead of this logic:
person_d.sex_descr = 'Male' THEN '1 - ' || 'Male'
WHEN wcs_person_d.sex_descr = 'Female' THEN '2 - ' || 'Female'

this should work: 
person_d.sex = 'M' THEN '1 - ' || 'Male'
WHEN wcs_person_d.sex = 'F' THEN '2 - ' || 'Female'

Please assign this ticket to Smit Panchal. Think this will be SMTL procedure changes and migration only.

Requested by: Michael Dorsch
Created by: Michael Dorsch on Fri 11/15/24 10:27 AM Pacific Standard Time