How To: URM Status SQL Function in the EDW

November 6, 2022

Do you get tired of maintaining the same business logic in many data products and spending too much time updating the logic in all those products?

Are you looking for one version of the truth across all your academic reports, data cubes, and dashboards?

The BI Team has great news for you. The EDW supports the first academic data-related SQL function, URM (Underrepresented Minority) Status, to help you reduce business logic maintenance, improve code readability, and keep one version of the truth for Underrepresented Minority students. Imagine that instead of spending time on updating, let’s say, 12 academic reports with the new URM logic, the EDW team can apply the URM Status SQL function logic just once and this modification will propagate to all 12 academic reports at the same time.

This function is most helpful to SQL developers working on academic reports, cubes, and dashboards that use students’ demographic data.

Context:

In SQL or any other programming language, a function is a program that takes some input and generates an output value. SQL has many built-in functions that you get out of the box. For example, one of the built-in system functions is REPLACE(), which expects to have three inputs:

  1. The expression you want to search (i.e. column or field),
  2. what you are searching for, and
  3. what you want to replace it with.

The EDW team has recently added the first academic data-related scalar SQL function called [dbo].[URMStatus] to the UWSDBDataStore] database in the EDW. A scalar SQL function returns a single value as opposed to a table of values.

By definition, a SQL scalar function takes one or more parameters in a SELECT statement and returns a single value, which in our case is one of the following: “International,” “URM” or “Not URM.”

How To Use the New Function:

The [dbo].[URMStatus] function takes four parameters:

  1. @underrepflag – an integer value of 9 represents that a student belongs a URM group
  2. @hispaniccode – an integer value of 7xx indicates a hispanic student
  3. @ethnicity  – a string value that could be one of the following: AFRO-AM, AMER-IND, ASIAN, CAUCASN, HAW/PAC, NOT IND
  4. @residency  – an integer value ranging from 0 – 6. If a value is 5 or 6, a function is going to return a value of “International”

The SQL code below provides an example of how ethnicity/race data is staged and then used in the [dbo].[URMStatus] function:

----------------------------------------------------------------------------- 
-- Race/Ethnicity temporary table 
-----------------------------------------------------------------------------
USE [UWSDBDataStore];
SELECT DISTINCT t.system_key,
    Race_Ethnicity = CASE 
        WHEN s1.resident IN (5, 6) THEN 'INTERNATIONAL'
        WHEN COUNT(DISTINCT et2.ethnic_group) >= 2 THEN 'TWO OR MORE'
        WHEN s1.hispanic_code <> 999 THEN 'HISPANIC'
        ELSE RTRIM(et.ethnic_desc)
        END
     , MAX(et2.ethnic_under_rep) AS ethnic_under_rep
    , MAX(sh.hispanic) AS Hispanic_Ethnic_Code
    , s1.resident
    , RTRIM(et.ethnic_desc) AS ethnic_desc
    , CAST(NULL AS CHAR(255)) AS  Hispanic 
    , CAST(NULL AS  CHAR(20)) AS Underrepresented
    , CAST(NULL AS CHAR(255)) AS Ethnic_Long_Description
INTO  #teth
FROM #t t  -- this your temp table with you an initial students’ population
INNER JOIN sec.student_1 s1 ON s1.system_key = t.system_key
LEFT JOIN sec.student_1_ethnic et1 ON et1.system_key = t.system_key
LEFT JOIN sec.sys_tbl_21_ethnic et2 ON et2.table_key = et1.ethnic
LEFT JOIN sec.sys_tbl_21_ethnic et ON et.table_key = s1.ethnic_code
LEFT JOIN sec.student_1_hispanic sh ON sh.system_key = t.system_key-- AND sh.hispanic &lt;&gt; 999
GROUP BY t.system_key
    , s1.resident
    , s1.hispanic_code
    , et.ethnic_desc   
    , s1.resident
-----------------------------------------------------------------------------
-- dbo.[URMStatus]  function call with parameters passed to this function
-----------------------------------------------------------------------------
;WITH UnderCTE AS
(
SELECT system_key
, Underrepresented = [UWSDBDataStore].[dbo].[URMStatus] 
    (
     REPLACE(ethnic_under_rep,0,99)     /* @underrepflag - a function validation outputs an error if an input value is 0 which is unexpected.
                                              so I replace 0 with 99 to pass a function validation for @underrepflag */
  , ISNULL(Hispanic_Ethnic_Code,999)    /* @hispaniccode - there are some NULL values in hispanic data source so we are passing 999 value that 
                                              is ignored in the function "WHEN @hispaniccode is 999" */
  , ethnic_desc                        /* @ethnicity */  
  , resident                           /* @residency */  
    )
FROM #teth
)

UPDATE t
SET t.Underrepresented = f.Underrepresented
FROM #teth t INNER JOIN UnderCTE f
ON t.system_key = f.system_key

Please Note:

The [dbo].[URMStatus] function has been implemented in 12 academic reports so far. Please check a SQL tab in the BI Portal for almost any academic report to see the function implementation.

Questions:

If you have questions about this function or how to use it in your solutions, please write to help@uw.edu and put “EDW Feature: URM Status Function” in the subject line.