Skip to content

UW Profiles Release: Bug Fix in Graduation/Retention Data Model

Based on user feedback, we released an update to the UW Profiles Graduation/Retention Data Model to resolve a bug. The issue and resolution are described in the Change Summary Below.

This update affects the following UW Profiles dashboards:

Change Summary:

The issue in the data model was that for the spring  cohort, the 1 Qtr Retention rate displayed on the UG Retention and Graduation Rates dashboard was incorrectly showing the rate based on summer quarter enrollment, not fall quarter enrollment. This resulted in incorrect 1 Qtr Retention rates displaying in the dashboard (i.e. a 1 Qtr retention rate in the 40s range, instead of in the 80s/90s range). Please note, the other quarters (fall and winter) were and are correctly calculating 1 Qtr retention.

 

The data source in the UG Graduation/Retention Data Model was updated to look at the appropriate quarter for the 1 Qtr retention calculation. The update was tested internally, and validated with users.

Questions:

If you have any questions, please don’t hesitate to write help@uw.edu with “UW Profiles: UG Retention/Graduation Bug Fix” in the subject line.

UWSDBDataStore Reorg on June 6, 2020

The student database (SDB) will go through a reorganization on June 6, 2020 which will cause changes in the Enterprise Data Warehouse database, UWSDBDataStore. These changes to the objects will be available in the UWSDBDataStore on Monday, June 8, 2020.
However, data on the new objects will be available as soon as the Data Custodians submit the  security role definitions and the SIS team loads the data.

(last modified: 6.04.2020)

*Please Note:The scope listed below isn’t final until 1 week before the reorg. Please continuously monitor this site for the most current update.

New columns to be added

Table Name Column Name Data Type Description
budget_resources br_tuition_cred tinyint How many credits their budget is based on
sa_distribution distr_chrg_sa_due  datetime Corresponding due date in SA-Account charge
sa_distribution distr_chrg_sa_amt  decimal(7,2) Corresponding amount in SA-Account charge
sf_evaluation s_asla_cmplt char(1)  Stafford Loan Acknowledgement
 sf_evaluation s_asla_cmplt_dt datetime  Stafford Loan Acknowledgement Date
 sf_evaluation s_asla_cmplt_id char(12)  Stafford Loan Acknowledgement ID
 sf_evaluation u_asla_cmplt  char(1)  UNSUB Loan Acknowledgement
 sf_evaluation u_asla_cmplt_dt  datetime  UNSUB Loan Acknowledgement Date
 sf_evaluation u_asla_cmplt_id  char(12)  UNSUB Loan Acknowledgement ID
 sf_evaluation p_asla_cmplt  char(1)  Parent Loan Acknowledgement
 sf_evaluation p_asla_cmplt_dt  datetime  Parent Loan Acknowledgement Date
 sf_evaluation p_asla_cmplt_id  char(12)  Parent Loan Acknowledgement ID
 sf_evaluation g_asla_cmplt  char(1)  Parent Loan Acknowledgement
 sf_evaluation g_asla_cmplt_dt  datetime  Parent Loan Acknowledgement Date
 sf_evaluation g_asla_cmplt_id  char(12)  Parent Loan Acknowledgement ID
sf_tuition t_pt_2cred int Part time tuition, 2 credits
sf_tuition t_pt_3cred int Part time tuition, 3 credits
sf_tuition t_pt_4cred int Part time tuition, 4 credits
sf_tuition t_pt_5cred int Part time tuition, 5 credits
sf_tuition t_pt_6cred int Part time tuition, 6 credits
sf_tuition t_pt_7cred int Part time tuition, 7 credits
sf_tuition t_pt_8cred int Part time tuition, 8 credits
sf_tuition t_pt_9cred int Part time tuition, 9 credits
sf_tuition_except te_pt_2cred int Part time tuition, 2 credits
sf_tuition_except te_pt_3cred int Part time tuition, 3 credits
sf_tuition_except te_pt_4cred int Part time tuition, 4 credits
sf_tuition_except te_pt_5cred int Part time tuition, 5 credits
sf_tuition_except te_pt_6cred int Part time tuition, 6 credits
sf_tuition_except te_pt_7cred int Part time tuition, 7 credits
sf_tuition_except te_pt_8cred int Part time tuition, 8 credits
sf_tuition_except te_pt_9cred int Part time tuition, 9 credits
sr_degree deg_visa_inelig bit Degree is not eligible for students with visas.
sr_student_grp_cd stu_grp_type char(6) type of student group, used for identifying similar types of groups for reporting.
sys_tbl_21_ethnic ethnic_inactive bit flag to indicate that an ethnic/hispanic code is inactive and should no longer be used.

New Table

Table Name Column Name Data Type Note
sys_tbl_16_stu_grp_type table_type tinyint value set to 16
table_key char(20)
record_type smallint
table_subkey tinyint
stu_grp_type_desc char(20) description of student group type specified in the associated table_key.
sys_tbl_17_astra_soc table_type tinyint  value set to 17
table_key  char(20)
record_type smallint
table_subkey tinyint
astra_soc_name char(12) short name of Astra Span of Control
astra_soc_desc char(40) description/long name of Astra Span of Control

Columns to be deleted

Table Name Column Name Note
sf_tuition  t_pt_amt1 Column was never used
sf_tuition t_pt_amt2 Column was never used
sf_tuition t_pt_amt3 Column was never used
sf_tuition t_pt_amt4 Column was never used
sf_tuition t_pt_amt5 Column was never used
sf_tuition_except te_pt_amt1 Column had either zeros or NULLs all this time
sf_tuition_except te_pt_amt2 Column had either zeros or NULLs all this time
sf_tuition_except te_pt_amt3 Column had either zeros or NULLs all this time
sf_tuition_except te_pt_amt4 Column had either zeros or NULLs all this time
sf_tuition_except te_pt_amt5 Column had either zeros or NULLs all this time

Send all questions and concerns to help@uw.edu, with the subject line: “UWSDBDataStore Reorg 6.6.2020“.

RPG Release: Student Grade Report by Major (Bug Fix)

In response to user feedback, the Report Prioritization Group just released an update to resolve a bug in the Student Grade Report by Major in the BI Portal.

Change Summary:

A user reported that the Course Grade Filter was incorrectly filtering out students. We identified and resolved the problem in the report’s query.

Now when you use the Course Grade Filter, the report filters down to only the grades of students that meet the filter criteria.

Consider the following scenario:

  • The student Potter, Harry completed three courses, and received three grades: 3.8, 3.9, 0.9
  • Harry’s adviser runs the report to see only the students who had grades below 2.0
  • The report returns Harry’s 0.9 grade.

Requesting Feedback:

While making this update, we heard two additional requests. Before we make these changes, we want to hear your feedback to understand if they will help or hurt you. Please write us at help@uw.edu with “RPG: Student Grade Report by Major” in the subject line.

  • Remove the Filter “Current Student as of”:
    • We heard that this filter is confusing and is often ignored, so we are proposing to remove the filter to reduce confusion.
    • We will not change the “Look up Transcript Year/Qtr” filter, and now it will be the primary “time” filter for the report.

  • Add a Filter “See All Grades”:
    • Consider the hypothetical Harry Potter case again. When the adviser applies the Course Grade filter, the report currently only returns the 0.9 grade. But the adviser may want to see the student’s other grades to see if the low grade is part of a pattern.
    • We are proposing to add a filter that would allow you to see all of the student’s grades for the quarter, if any of the student’s grades meet the filter criteria.
    • For instance, since Harry has one grade in the range 0-1.9, the report would return all three grades 3.8, 3.9, 0.9.

Questions and Feedback:

This report references many UW institutional concepts, including Cumulative GPA and Quarterly Grade Point Average. To get detailed definitions for this and other terms, please check out Knowledge Navigator!

As always, if you have any questions or if you would like to provide feedback, please write help@uw.edu and put “RPG: Student Grade Report by Major” in the subject line.

The Newest Addition to the EDW: Time Travel with dimTime

If you work with time data, a new reference table in the EDW makes it easier than ever to manipulate time in your SQL query.

What is it?

The new dimTime table has a row for each minute of the day for a total of 1,440 records. Each “minute record” has details including:

  • a minute key
  • a cleaned up and normalized minute field
  • the minute record rounded up or down the quarter-hour, half-hour, and hour
  • AM/PM Indicators

See for yourself:

--Server: edwpub.s.uw.edu
DECLARE
@startTime time = '11:19'
SELECT timekey
, StandardTime
, RoundDownQtrHour
, RoundUpQtrHour
, RoundDownHalfHour
, RoundUpHalfHour
, RoundDownHour
, RoundUpHour
FROM EDWPresentation.sec.dimTime
WHERE StandardTime = @startTim

Questions?

If you have a question about the data in any of the columns, the table and column definitions are documented in Knowledge Navigator: dimTime

If you have any questions about the new EDW table, or any requested changes, please write help@uw.edu, with “EDW: dimTime” in the subject line.

RPG Release: Updated Instructor Title on Time Schedule Information

Based on user feedback, the Report Prioritization Group just released an update to the Time Schedule Information report. Run the report to see for yourself!

Change Summary:

A user requested that we update the “Instructor Title” field on the report. They told us the data was often sparse and didn’t allow them to meaningfully group Instructors based on title. We replaced the Workday “Job Title Abbreviation” with the Workday “Business Title” in the report.

Here’s an example of the differences:

  • Before the Update:

  • After the update:

Please Note:

  • The Title displayed in the report is the Instructor’s current Business Title in Workday. It is not their title for the quarter that they taught the course.
  • A user’s ability to access EDW data is limited by their EDW ASTRA role. As a result, not all users will be able to view the Instructor Title data. Due to the security settings on this HR/P data, users with the Adviser/Academic Staff EDW ASTRA role cannot view Instructor Title, and will see blank values in the report. For more information on EDW Security, please visit IT Connect: https://it.uw.edu/work/data/data-security/

Questions and Feedback:

This report references many UW institutional concepts, including Business Title. To get detailed definitions for this and other terms, please check out Knowledge Navigator!

As always, if you have any questions, please write help@uw.edu and put “RPG: Time Schedule Information Report” in the subject line.

RPG Release: Registration Hold Information Now on Student Degree Info Report

Based on user feedback, the Report Prioritization Group just released an update to the Student Degree Information report. Run the report to see for yourself!

Change Summary:

Based on a user request, the report now includes Registration Hold Details. The primary use for this information is to allow advisers and others to look at students who have applied to graduate in a future quarter, but currently have a registration hold.

  • Columns to Display
    • This filter now includes a “Reg Hold” option.
    • When a user selects Reg Hold, the report now displays three new columns.
  • New Columns:
    • Reg Hold:
      • This column is sortable to easily identify students with registration holds (“Y”)
    • Hold Office:
      • This column displays the office that placed the hold.
      • If a student has more than one registration hold, those offices display as a comma separated list.
    • Hold Reason:
      • This column displays the free-text description of the reason for the registration hold.
      • If a student has more than one registration hold, those reasons display as a comma separated list.

Questions and Feedback:

This report references many UW institutional concepts, including Student Hold. To get detailed definitions for this and other terms, please check out Knowledge Navigator!

As always, if you have any questions, please write help@uw.edu and put “RPG: Student Degree Information” in the subject line.

Managed Workstation COVID-19 updates

UW-IT will be working from home until June. This includes all of the Managed Workstation team. In these uncertain times we’re doing our best to balance our customer needs with being cautious with our own staff.

Computer Imaging

The OSD image installation process is highly dependent on UW network resources, and doesn’t work from a remote location.

We offer instructions on the self-service process to image the machine and the steps to have it joined to the domain under the Managed Workstation Service here: https://it.uw.edu/wares/mws/mgmt/setup-computer/osd/

Our usual high-touch approach has a number of problems during this crisis, including lack of good social distancing protocols, disinfecting practices for computer hardware, and violating direction from the UW and WA state governor.  We’ve thought carefully about balancing customer value with the potential risk and self-service imaging is the best option we have.

If there is a specific need you have, please let us know and we can work together to find alternate solutions.

Maintaining workstation connection to the UW network

We have taken steps to ensure that computers are not dropped off the domain for inactivity during this time.  However, in order to maintain a good working relationship between computers and the UW network the Managed Workstation service suggests that all computers taken off the UW network be logged in and connected to the Managed Workstation VPN service for 2 hours a week.

VPN Server Capacity

Connections to the Managed Workstation VPN have been increasing in volume each day for the past 3 weeks.  With this in mind, we are taking steps to increase capacity.  This process should be completed within the next 2 weeks.

Office Hours

Managed Workstation staff will be available for virtual office hours via Zoom on Thursday, March 26 from 10-11:30. Send an email to help@uw.edu to get a meeting invitation.

 

Kim Frye
Managed Workstation Service Manager
UW Information Technology
University of Washington

RPG Release: Current Student Information – Bug Resolved

Based on this user feedback, the Report Prioritization Group released a bug fix to the BI Portal report Current Student Information.

Change Summary:

Due to an error in the logic, the report was inadvertently excluding 14 minors from the Major/Minor dropdown. This problem was reported by users via help@uw.edu and resolved in a recent release. The dropdown now includes a complete list of UW Majors and Minors.

A Few Reminders:

This report displays a population of students that have enrolled in courses during the current quarter based on the selected major or minor. This report has two key features:

  • Consolidation: You no longer have to access two separate reports to get information on current students by major and minor. The report now features a single filter that allows you to filter by the Major or Minor of interest in a single report!
    • In the coming weeks we will archive the existing Current Student by Major and Minor Reports. Please update your bookmarks!
  • Updated Timing: Based on user feedback, we also updated the logic for identifying the “current quarter” during the academic break periods between quarters.
    • In the old reports, during an academic break, the old reports looked at the quarter that had just completed. For instance, during summer break, users complained that there was a period before the start of Autumn quarter where the report was not usable because it displayed only information about the previous (completed) quarter.
    • In the new report, one day after a quarter ends, the report looks forward to the next quarter’s enrollment information. For instance, during Summer break, the report displays Autumn quarter enrollment information. We reviewed this change with many users, and all agreed that this was an improvement over the old reports.

Questions and Feedback:

This report references important institutional concepts like “Current Student”, “URM“, and many others. If you have questions about the meaning of these or any other term, please explore the links or find more detail in the Knowledge Navigator.

As always, if you have any questions, please write help@uw.edu and put “BI Portal Report Question: Current Student Information” in the subject line.

RPG Release: Study Abroad Students Bug Resolved

Based on this user feedback, the Report Prioritization Group just released a bug fix to the Study Abroad Students by Unit Report.

Change Summary:

At the start of a quarter, prior to census day, the report was inadvertently excluding some study abroad students from the report. This problem was identified and resolved in a recent release. Previously, the report sourced from an EDW table that was only updated quarterly, after census day. The report now sources data from a UWSDBDataStore table that updates daily with information from SDB.

Questions and Feedback:

This report references many concepts like Study Abroad Student and Study Abroad Program. To get detailed definitions for these and other terms, please check out Knowledge Navigator!

As always, if you have any questions, please write help@uw.edu and put “RPG: Study Abroad Students Report” in the subject line.

RPG Release: Time Schedule Enrollment by Day and Time Visualization in BI Portal

The Report Prioritization Group released a new visualization to the BI Portal: Time Schedule Enrollment by Day and Time.

This tool enables planning by displaying a heat map of current enrollment counts broken down by day and time in order to demonstrate times of high and low campus activity. The analysis tool divides the academic day into 30 minute increments. Student enrollments are counted during a 30 minute period if a course is scheduled during any part of a period.

How to Use this Visualization:

There are two primary ways to use this analysis tool:

Planning an Event:

A program coordinator who wants to schedule a speaker or event may have the question, “what are the time periods in between classes when students are on campus and would be more likely to attend a speaker session?”

If you filter down to a specific department, you can view all courses managed by a department. Look for the blank periods between courses:

Capacity Planning:

A planner in Student Services may want to answer the question, “When are students likely to be on campus, so I can staff my office to help students during peak times of activity?”

If you filter at the campus level, you can view the overall enrollment counts for a whole campus to view peak periods of activity:

Important Details:

Please note: This analysis tool should not be used for Learning Spaces Policy compliance. For Learning Spaces Policy work, please use the Learning Spaces Policy report in the BI Portal.

The heat map displays “current enrollment” counts as a table. The X axis displays the academic week, Monday through Saturday. The Y axis displays the academic day broken down into 30 minute increments. The Y axis time range represents the minimum and maximum times for the population of courses in the table. A heat map demonstrates periods of high and low campus activity.

The population of courses excludes those with the following attributes:

  • All Withdrawn courses
  • Courses with invalid days
  • Courses with 0 enrollment
  • Courses with SLNs equal to 0
  • Courses with invalid start or end times (e.g. 00:00, 25:00)

How are the counts calculated? Student enrollments are counted during a 30 minute period if a course is scheduled during any part of a period. For example, if a course is scheduled from 8:30 – 10:20, a single student will get counted in each of the time periods from 8:30 to 10:00, but will not be counted in the 10:30 time period. This means that student enrollments are intentionally duplicated many times throughout the visualization. This is intentional because the goal of the tool is to demonstrate when students are registered and are assumed to be “on campus” for a given time period.

Are these “current enrollment counts” or “census day counts”? The counts and times are based on current enrollment counts in the Time Schedule data that are updated daily. The data are not snapshots of census day counts. This means that if a student drops a course or registers for a course, these changes will be reflected in the visualization. The data are sourced from the EDW, are updated daily, but always reflect the previous day’s changes to Time Schedule. This analysis tool does not display real-time Time Schedule or registration changes.

Questions?

This visualization references important institutional concepts like “Section Type“, “Learning Type” and many others. If you have questions about the meaning of this or any other term, please explore the links or find more detail in the Knowledge Navigator.

As always, if you have any questions, please write help@uw.edu and put “BI Portal Visualization Question: Time Schedule Enrollment by Day and Time” in the subject line.