Knowledge Navigator gets a refresh, modernizes for information and business excellence

— Data freeze starting Feb 6 —

What is Knowledge Navigator?

Knowledge Navigator (KN) is the University of Washington’s web-based application that displays business terms, their definitions, and data relationships used by everyone at UW. KN provides context to data used across the University and is UW’s one source for finding and understanding information across critical subject areas (Student/Academic, Human Resources, Financial, Research Administration) which enables informed decision-making.

Knowledge Navigator

Why is Knowledge Navigator being redesigned?

Since 2015, when Knowledge Navigator first launched, it has run in Microsoft Azure using a classic virtual machine. This virtual machine will no longer be supported by Microsoft after March 2023, so KN’s infrastructure is being redesigned and upgraded to use a cloud-based, database-as-a-service platform known as AuraDB by Neo4J, which provides expanded capabilities, minimizes costs and increases security.

Bart Pietrzak, director for Enterprise Reporting & Analytics, notes, “To use and make decisions with data, it needs to be discoverable and understood. A centralized repository for the University’s data terms and definitions is essential to its decision-making capabilities. This redesign work ensures that Knowledge Navigator continues to be the University’s most critical and comprehensive tool that empowers end users to make informed decisions.”

What changes will customers experience?

Much of the redesign work won’t be visible on customer-facing pages; it’s the unseen critical infrastructure that will be upgraded and refactored, but which provides users with background benefits like maintenance efficiencies and increased security. And KN users will experience a new streamlined home page, while favorite and familiar features remain intact and run exactly as you expect.

Streamlined home page
Maintenance efficiencies
Increased security

Who’s working on the KN redesign?

KN redesign work started in late September 2022 with experts from UW-IT’s Information Management division and the Program Management Office. The team includes:

Developers: Moon Koslowsky, Tech Project Lead, Andrew Nelson, Senior Full-Stack Engineer and Indra Narayan, Data Engineer
Business SME: Keith A. Van Eaton, Metadata Manager
Quality Assurance: Jean Darlington, Tester
Product Owner: Katayoon Moazzami, Business Systems Analyst
Key Stakeholder: Bart Pietrzak, Director, IM: Enterprise Reporting and Analytics
Sponsor: Anja Canfield-Budde, Associate Vice President, Information Management
Project Manager: Colleen Butler, Project Manager, UW-IT Research Computing & Strategy, Program Management Office

What’s happened so far?

Home page modernization – a reveal will be coming soon!
Upgrade of KN application with latest .NET and Neo4J client frameworks
Migration of data to AuraDB

What’s happening now?

Code refactoring to leverage AuraDB
Implementing a new search engine using Microsoft Azure Cognitive Search
Testing and debugging

When will the redesigned KN go live?

The team is on track to launch the redesigned application in mid-February 2023.

NOTE: Data Freeze starting Feb 6

In preparation for the Knowledge Navigator redesign launch in mid–February, there will be a data freeze starting Feb 6. Please keep the following in mind:

Favorites created during the freeze may not be available after the launch.
Some of your current bookmarks may change after the launch.

Questions?

If you need more information or have questions, please contact us at help@uw.edu with “KN Redesign” in the subject line.

Knowledge Navigator: Autumn Quarter 2022 Metadata Update

UW Metadata Manager, Keith A. Van Eaton, from Information Management’s Enterprise Reporting & Analytics team, has added or updated metadata objects in Knowledge Navigator.

In their ongoing partnership, the UW’s data domain stewards, subject matter experts, and metadata manager ensure metadata objects are vetted and approved so that UW data users have a common understanding around UW data.

New terms added

New structured objects or reports or other added

1 Diagram
2 Enterprise Reports
17 columns EDWPresentation database
26 columns UWSDBDataStore database
48 tables & 570 columns UWODS database
1 table & 51 columns WDFinDataMart database

UW Metadata Objects

The Tableau visualization below shows, over time, all objects brought into Knowledge Navigator. Hover over any graphic element to get more info. https://bitools.uw.edu/t/Transitional/views/UWMetadataObjects/UWMetadataObjects?:showAppBanner=false&:display_count=n&:showVizHome=n&:origin=viz_share_link

UW Metadata Engagement

The Tableau visualization below shows, over time, the Subject Matter Expert who drafted a business definition and the Data Steward who approved the definition for any given data. Hover over any graphic element to get more information. https://bitools.uw.edu/t/Transitional/views/UWMetadataEngagement/UWMetadataEngagement?:showAppBanner=false&:display_count=n&:showVizHome=n&:origin=viz_share_link

To access these links from an off-site location, please do one of the following:

Remote into your work computer.
Use the F5/Big VPN (not UW MWS VPN) with your personal computer.

Knowledge Navigator is the UW’s metadata repository which contains information about the following collections and object types:

Glossaries of business terms
Database tables and columns
Web Service resources and fields
Cube dimensions and measures
Enterprise reports and dashboards

Terms and objects are updated regularly. To learn more about Knowledge Navigator, where terms come from, and how they get included in Knowledge Navigator, visit the information page at https://it.uw.edu/work/data/understand-data/data-definitions/.

If you’d like a demo of Knowledge Navigator for your department, or if you have questions, please contact help@uw.edu with “Knowledge Navigator” in the subject line.

EDW for Finance Transformation Release 16 is Now Available

Enterprise Data Warehouse (EDW) has released an update in UWODS and WDFinDataMart in support of UW Finance Transformation (UWFT). For an overview of how EDW is adapting to support UWFT, refer to EDW for Finance Transformation.

Monthly Scheduled Releases

Updates to UWODS and WDFinDataMart are released on the same monthly schedule. This allows us to better synchronize with Enterprise Data Platform’s release schedule for Finance Transformation. For more information, refer to the Data Availability Timeline.

What’s New in EDW Release 16

Database	Overview of Release
UWODS	This release includes 16 new tables. An additional 41 columns were added to existing tables. Introduced Breaking changes by renaming and removing fields in multiple tables. Known Data Issues are as follows: Some Tables are missing data. See: UWODS Tables Missing Data Truncated data due to bad test data not following expected standards Some definitions are unknown New Frequently Asked Questions (FAQ) available – See UWODS’s FAQs to learn more about this updated FAQ! I use several ODS tables (for example, Person, JobProfile, etc.). Where should I look for comparable data in the future? What is the new/future version of the ODS table currently called BudgetActivityDetail? Is that table continuing? What’s the difference between a WID and a RefID? For more information, refer to UWODS EDW Release 16. For support, refer to Support l UWODS.
WDFinDataMart	No new tables were added in R16. A breaking change was introduced by removing a single field from an existing table in R16. Known Data Issues Some Tables are missing data. See: Tables Missing Data \| WDFinDataMart Truncated data due to bad test data not following expected standards Some definitions are unknown For more information, refer to WDFinDataMart EDW Release 16. For Support, refer to Support l WDFinDataMart

Database

Overview of Release

UWODS

This release includes 16 new tables. An additional 41 columns were added to existing tables.
Introduced Breaking changes by renaming and removing fields in multiple tables.

Known Data Issues are as follows:

- Some Tables are missing data. See: UWODS Tables Missing Data
- Truncated data due to bad test data not following expected standards
- Some definitions are unknown

New Frequently Asked Questions (FAQ) available – See UWODS’s FAQs to learn more about this updated FAQ!

I use several ODS tables (for example, Person, JobProfile, etc.). Where should I look for comparable data in the future?
What is the new/future version of the ODS table currently called BudgetActivityDetail? Is that table continuing?
What’s the difference between a WID and a RefID?

For more information, refer to UWODS EDW Release 16. For support, refer to Support l UWODS.

WDFinDataMart

No new tables were added in R16.
A breaking change was introduced by removing a single field from an existing table in R16.

Known Data Issues

Some Tables are missing data. See: Tables Missing Data | WDFinDataMart
Truncated data due to bad test data not following expected standards
Some definitions are unknown

For more information, refer to WDFinDataMart EDW Release 16. For Support, refer to Support l WDFinDataMart

EDW Release Schedule

We have already started working on next month’s release. Bookmark Release Notes | UWODS and Release Notes | WDFinDataMart to follow our progress.

Support Available to Transition Human Resource/Payroll Data from ODS to UWODS

When UW Finance Transformation (UWFT) goes live, the current state ODS database will be frozen and the UWODS database will be introduced. For more information, refer to Transitioning HR Data from ODS to UWODS.

New EDW Data Load Status for Production and Test Environments

Based on our requests from customers, we have published a new stand alone EDW Data Load Status page for both EDW Production and Test environments. This replaces the Data Load Status information that was previously embedded in the EDW help page. Please update your bookmarks accordingly.

Winter 2023 Content Manager Update

Hello IT Connect Content Managers,

This is the Winter 2023 edition of the quarterly IT Connect Content Manager Update.

Reviews needed for top 100 most viewed pages on IT Connect
Technical Writer support for IT Connect content
Web content best practices: user experience testing

WHAT’S NEW?

Reviews needed for top 100 most viewed pages on IT Connect

A recent Google Analytics analysis showed that 49.5 percent of the top 100 most viewed web pages on IT Connect had not been reviewed in the last year.

Regular content review is important for keeping information accurate, up-to-date and relevant to users.

Toward ensuring that IT Connect is up to date and accurate for our customers, content managers for pages that need to be reviewed will receive UW Connect request tickets.These content managers will be asked to do a comprehensive review of the content to ensure it is accurate, up to date, and clear. This process will be done quarterly moving forward, to ensure that the most viewed content on IT Connect is kept up to date.

This need for regular reviews is spurred by findings from the 2021 customer experience survey, which found IT Connect content as one of the critical areas that UW-IT needed improvement.

We strongly recommend that you review your content a minimum of once per year. Information about these Strategic Leadership Team-approved guidelines and how to manage content review can be found in IT Connect’s documentation for content managers.

Technical Writer support for IT Connect content

During the past quarter, a student technical writer intern supported several service teams and helped update dozens of pages of content for more than three services.

We are hoping to bring on another student technical writer to help support service teams with creating and updating documentation on IT Connect, and want to know which teams would like support with their content. The UW-IT Communications team can also help you with your IT Connect content through brainstorming, working sessions, and editing support. If you need help with your content and/or need technical writing support, then please fill out this form to let us know.

Web content best practices: User experience testing

User experience testing can help you understand how well your web content works for your target audiences, and help you improve your content. Creating a great user experience is worth the investment of time and resources because your content will be more useful to end users, reduce frustrating user experiences, and reduce the need for users to contact UW-IT for support.

Start simple.

Identify a set of common tasks that your target audience typically does, such as set up a tool, download a product or request a service.
Solicit a small set of your audience to participate..
Set up a time to meet with these individuals and ask them to walk through each of these common tasks using your content on IT Connect.
Listen to their feedback, and use it to learn where there are issues or alternatives that might improve your content.

UW Information Technology also offers online resources and consultations to help you create a great user experience.

DOCUMENTATION, TRAINING & CONSULTATIONS

Do you need some help creating IT Connect content, another head to brainstorm content organization and layout, or want to learn how to use the features available in IT Connect? There are several ways to get help:

Content Manager Documentation: Extensive documentation ranging from the basics of WordPress to advanced features for pages is available on the IT Connect Content Manager Documentation.
Guide to writing documentation: Use the Getting started with documentation guide to help you start writing documentation. The guide breaks down a process you can use to help you get information to your end users in documentation.
Training: One-on-one training for managing content on IT Connect is available from the IT Connect product manager, Nick Rohde, over Zoom or in person (once normal operations continue after the coronavirus pandemic). Get tips on correctly formatting content using the tools in IT Connect or learn how to organize your content. To arrange training, fill out this intake form.
Consultation: Consultation sessions for individuals or teams are available from the IT Connect product manager, Nick Rohde. To schedule a consultation, fill out this intake form.

EWS EVAL Release 16 is Now Available

Enterprise Web Services (EWS) has released an update in EVAL for multiple web services in support of UW Finance Transformation (UWFT). For an overview of how EWS is adapting to support UWFT, refer to EWS for Finance Transformation.

Monthly Scheduled Releases

EVAL: Updates to EVAL environments for all EWS services are released on the same monthly schedule. This allows us to better synchronize with Enterprise Data Platform’s release schedule for Finance Transformation. For more information, refer to the Data Availability Timeline.
PROD: Updates to PROD environments for all EWS services now reflect changes from the previous month’s EVAL release (for example, if EVAL reflects Release R16, then PROD will reflect Release R15).

What’s New in EWS Release 16

Web Service	Overview of Release
FWS v2 (Outbound)	Introduced a Breaking Change by removing PayrollProcessingID from the /PayrollJournal resource. Updated the /PayrollJournal resource by updating the data source, removing some search parameters, and renaming a search parameter. Updated the /Journal resource by removing one search parameter and renaming another. Fixed bugs for the following resources: /BillingSchedule /ReferenceType /WorkdayHierarchy All FWS v2 resources with an active_status search parameter. Added the /CustomListValue resource. For more information, refer to FWS Reference Data (Outbound) – Release 16. For support, refer to Support \| FWS.
FWS v2 (Inbound)	Restricted the length for the DocumentId field to 100 characters to avoid truncation by Workday and specified allowable characters: alphanumeric, dashes, and underscores. For more information, refer to FWS Common Transactions (Inbound) – Release 16. For support, refer to Support \| FWS.
HRPWS v3	Introduced a Breaking Change by removing PayrollProcessingID from the /PayrollResult resource. Fixed a bug related to the changed_since_date and changed_to_date search parameters in the /PayrollResult resource. Fixed a bug for all HRPWS v3 resources with the active_status search parameter. For more information, refer to HRPWS v3 – Release 16. For support, refer to Support \| HRPWS.
SpaceWS v2	There were no customer-facing changes for SpaceWS v2 included in this release.

Reminder for HRPWS v2 and SpaceWS v1 Users

HRPWS v2 and SpaceWS v1 are ready to retire. If you have not transitioned to the new version(s) and need additional time, please submit an extension request ASAP:

HRPWS v2 – Email hrpws-support@uw.edu with subject line: “HRPWS v2 extension request”. Include the following:
- Certificate you are using for PROD.
- Name of application/service you are integrating.
- Estimated date your transition to HRPWS v3 PROD will be complete.
SpaceWS v1 – Email spacews-support@uw.edu with subject line: “SpaceWS v1 extension request”. Include the following:
- Certificate you are using for PROD.
- Name of application/service you are integrating.
- Estimated date your transition to SpaceWS v2 PROD will be complete.

EDW for Finance Transformation Release 15 is Now Available

Reminder for ODS users: When UW Finance Transformation (UWFT) goes live, the current state ODS database will be frozen and the UWODS database will be introduced. For more information, refer to Transitioning HR Data from ODS to UWODS.

Monthly Scheduled Releases

What’s New in EDW for Finance Transformation Release 15

Database	Overview of Release
UWODS	This release includes 29 new tables. An additional 18 columns were added to existing tables with 14 column name changes. Introduced Breaking changes by renaming and removing fields in multiple tables. Known Data Issues are as follows: Some Tables are missing data. See: UWODS Tables Missing Data Truncated data due to bad test data not following expected standards Some definitions are unknown For more information, refer to UWODS EDW Release 15. For support, refer to Support \| UWODS. Refer to FAQ \| UWODS for frequently asked questions by UWODS users.
WDFinDataMart	There were no customer-facing changes for WDFinDataMart introduced in this release. Known Data Issues Some Tables are missing data. See: Tables Missing Data \| WDFinDataMart Truncated data due to bad test data not following expected standards Some definitions are unknown For more information, refer to WDFinDataMart. For support, refer to Support l WDFinDataMart. Refer to FAQ l WDFinDataMart for frequently asked questions by WDFinDataMart users.

Database

Overview of Release

UWODS

This release includes 29 new tables. An additional 18 columns were added to existing tables with 14 column name changes.
Introduced Breaking changes by renaming and removing fields in multiple tables.

Known Data Issues are as follows:

- Some Tables are missing data. See: UWODS Tables Missing Data
- Truncated data due to bad test data not following expected standards
- Some definitions are unknown

For more information, refer to UWODS EDW Release 15. For support, refer to Support | UWODS. Refer to FAQ | UWODS for frequently asked questions by UWODS users.

WDFinDataMart

There were no customer-facing changes for WDFinDataMart introduced in this release.

Known Data Issues

Some Tables are missing data. See: Tables Missing Data | WDFinDataMart
Truncated data due to bad test data not following expected standards
Some definitions are unknown

For more information, refer to WDFinDataMart. For support, refer to Support l WDFinDataMart. Refer to FAQ l WDFinDataMart for frequently asked questions by WDFinDataMart users.

EDW Release Schedule for the Holidays

The UWODS and WDFinDataMart December releases are moving to early January to accommodate for Christmas and New Year holidays:

Release 16 – 01/09/2023.

EWS EVAL Release 15 is Now Available

Monthly Scheduled Releases

EVAL: Updates to EVAL environments for all EWS services are released on the same monthly schedule. This allows us to better synchronize with Enterprise Data Platform’s release schedule for Finance Transformation. For more information, refer to the Data Availability Timeline.
PROD: Updates to PROD environments for all EWS services now reflect changes from the previous month’s EVAL release (for example, if EVAL reflects Release R15, then PROD will reflect Release R14).

What’s New in EWS Release 15

Web Service	Overview of Release
FWS v2 (Outbound)	Introduced Breaking Changes by renaming or removing fields in the following resources: /Customer /Journal /PayrollJournal /Supplier Introduced Breaking Changes by fixing data types across various resources that previously required values when they should be nullable. Added search parameters to most FWS v2 resources. Updated and renamed multiple fields in the /SearchID resource. Updated a majority of FWS v2 resources to return active values only by default. For more information, refer to FWS Reference Data (Outbound) – Release 15. For support, refer to Support \| FWS.
FWS v2 (Inbound)	There were no customer-facing changes for FWS v2 (Inbound) introduced in this release.
HRPWS v3	Added search parameters to most HRPWS v3 resources. For more information, refer to HRPWS v3 – Release 15. For support, refer to Support \| HRPWS. Reminder for HRPWS v2 users: HRPWS v2 is scheduled to retire at the end of 2022. For more information, refer to Transitioning from HRPWS v2 to v3.
SpaceWS v2	There were no customer-facing changes for SpaceWS v2 included in this release. Reminder for SpaceWS v1 users: SpaceWS v1 is scheduled to retire at the end of 2022. For more information, refer to Transitioning from SpaceWS v1 to v2.

EWS Release Schedule for the Holidays

The December EWS releases will occur the week between Christmas and the New Year:

12/27/22 – EWS Production Release (includes updates from EWS EVAL Release R15)
12/29/22 – EWS R16 EVAL Release

Support Available to Transition Human Resource/Payroll Data from ODS to UWODS

The Enterprise Data Warehouse (EDW) recently introduced two new databases to support UW Finance Transformation (UWFT): UWODS and WDFinDataMart. For more information, refer to EDW Databases and Resources Available to Support Finance Transformation.

The UWODS database, which goes live with UWFT, will enable EDW users to access Finance and Human Resource/Payroll (HRP) data from Workday. The UWODS will replace the current ODS database as the EDW resource for this data.

In the UWODS, the HRP data model is different than in the current ODS database. This change better reflects Workday’s HRP data structure and creates better parity between the UWODS and HRP Web Service (HRPWS). This better alignment allows the EDW to quickly adapt to future changes and provide more clarity and consistency to users who leverage this data.

Watch this short video for an introduction to how HRP data is changing in the UWODS:

Transition Support is Available

Documentation is available to help users transition from the ODS to UWODS for HRP data. Refer to the following pages to get started:

Transitioning HR Data from ODS to UWODS – This resource guides users through the transition from ODS to UWODS, which includes:
- Mapping tables and columns from current to future state.
- Reviewing new HRP tables.
- Exploring HR Data Domain concepts and associated sample queries to help answer common questions.
UWODS Customer Portal – This page provides additional information on this new database, including the different environments available and how to get started.

Additional Resources for Support

Join Us for Office Hours

EDW Subject Matter Experts are now available during the Enterprise Reporting and Analytics (ERA) Office Hours every Friday from 9 to 10. Please drop by to ask your UWODS questions.

You can either sign up ahead of time or drop by during the session:

Drop by – Zoom (During session only)

Subscribe to Our Email List

When you request access to UWODS, you are automatically added to the respective mailman list(s) to receive periodic communications related to these databases.

If you are not ready to get access, but want to stay in the loop, we encourage you to sign up for the following list today: uwods-ft-users

Email Us

If you have questions or would like to provide feedback, please write to edw-help@uw.edu with “UWODS” in the subject line.

EDW Databases and Resources Available to Support Finance Transformation

The Enterprise Data Warehouse (EDW) is a secure central repository that integrates data from many sources across the University. It stores current and historical data that are used to support operational reporting and strategic analysis. The goal of the EDW is to support better and faster data-informed decision making. For more information about the EDW and how it fits within the Enterprise Data Platform, refer to Enterprise Data Platform.

EDW and UW Finance Transformation (UWFT)

UW Finance Transformation (UWFT) is much more than a system update – it is a redesign of our finance-related policies and processes, with the help of new technology. The EDW is supporting UWFT with the introduction of two new databases: UWODS and WDFinDataMart. These databases, which are under development and provision data from Workday, are currently available to help users prepare for UWFT go live.

For more information on how the EDW is supporting Finance Transformation, including planned dispositions for affected databases, refer to EDW for Finance Transformation.

New UWODS Database

When UWFT goes live, the EDW’s current state Operational Data Store (ODS) will freeze, and the new UW Operational Data Store (UWODS) will be introduced. This new database reflects the Foundation Data Model (FDM), which is the technical framework that organizes financial data in Workday. This new database will support reporting for the following:

Workday Foundation Data Model (FDM) and Financial Reference Data
Remediated HR/Payroll Data

To learn more about UWODS, request access, or get support, refer to the UWODS Customer Portal.

New WDFinDataMart Database

When UWFT goes live, the EDW’s current state FinancialSumMart will freeze, and the new Workday Financial Data Mart (WDFinDataMart) will be introduced. This new database will support reporting for the following:

Accounting & Operational Journal Transactions
Grant & Financial Budgets
Current Budget Balances
Payroll Journal Line Detail

To learn more about WDFinDataMart, request access, or get support, refer to the WDFinDataMart Customer Portal.

Additional Resources for Support

In addition to the customer portals noted above, there are a number of resources available to help users stay informed, ask questions, and get support with these new databases.

Reach Out to UWFT

Reach out to your FT Unit Readiness Lead or submit a request to uwftask@uw.edu for guidance.

Tip: To identify your Unit Readiness Lead, go to the UWFT System Remediation & Retirement Portal, locate your unit, and then click the information icon next to the unit name. For example,

An Information/Overview page displays for your unit, which includes the name of your assigned Unit Readiness Lead.

Join Us for Office Hours

EDW Subject Matter Experts are now available during the Enterprise Reporting and Analytics (ERA) Office Hours every Friday from 9 to 10. Please drop in to ask your UWODS and WDFinDataMart questions.

You can either sign up ahead of time or drop by during the session:

Drop by – Zoom (During session only)

Subscribe to Our Email Lists

When you request access to UWODS or WDFinDataMart, you are automatically added to the respective mailman list(s) to receive periodic communications related to these databases.

If you are not ready to get access, but want to stay in the loop, we encourage you to sign up for the following lists today:

Email Us

If you have questions or would like to provide feedback, please write to edw-help@uw.edu with “UWODS” or “WDFinDataMart” in the subject line.

How To: URM Status SQL Function in the EDW

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:

The expression you want to search (i.e. column or field),
what you are searching for, and
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:

@underrepflag – an integer value of 9 represents that a student belongs a URM group
@hispaniccode – an integer value of 7xx indicates a hispanic student
@ethnicity – a string value that could be one of the following: AFRO-AM, AMER-IND, ASIAN, CAUCASN, HAW/PAC, NOT IND
@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.