EDW & EWS Data Changes Coming to Support Northwest Hospital (NWH) and UW Physicians (UWP)

This is a heads up on upcoming changes to both the Enterprise Data Warehouse (EDW) and Enterprise Web Services (EWS).

As you may be aware, UW Medicine is currently in the process of consolidating activities across two of its major entities:

Northwest Hospital (NWH) will become a second campus of the University of Washington Medical Center (UWMC)
UW Physicians (UWP) staff will become part of the UW Medicine Health System (UWMHS) supervisory org structure in Workday and part of the School of Medicine otherwise

A major component of this consolidation effort includes integration of ~2600 NWH and UWP employees into Workday and UW-IT systems. A dedicated project is in progress to support this transition. The project has successfully completed design, configuration, and 2 rounds of end-to-end testing. The final round of end-to-end testing is now in progress. The cutover and data migration activities are planned for the last two weeks of December 2019, with the final cutover planned for January 1, 2020.

We do not think the changes impact you.

However, we want to make sure you aware of what is happening. Over the last few months both EDW & EWS teams worked very hard to evaluate, adapt, and test the changes. The goal is to ensure a smooth transition for NWH and UWP units while minimizing impact on the rest of campus.

Existing fields are not changing as part of this work. There are no new data elements. So what are the changes?

New values will appear in existing fields. The new values will start showing up in mid-December with a final implementation date of January 1, 2020.

The new values fall into two categories: 1) data additions that could happen at any time, and 2) changes that are unique to this project.

1. Data additions that could happen any time

We’re communicating them here because they are being added as part of the NWH/UWP project:

Supervisory orgs
Cost centers
Locations
Job profiles
Job classifications (absence related)
Compensation grades
Bargaining units/unions
Job families, related to unions

2. Changes that are unique to this project

New employee sub type (worker type):
- This new type of employee (worker) was added to support paying some NWH hourly employees the actual hours they work
- This new type of employee (worker) is for positions known as “regular, hourly”
- Currently, only relevant to a subset of the NWH population
- “Regular, hourly” subtype will function the same as the subtype “regular”
- The assumption can no longer be made that an hourly employee is temporary
- Hourly employees can be temporary or regular, depending on their job profile
New pay component code (earning code):
- Associated with the new employee subtype
- The new value is RGH
- RGH is typically treated like regular earnings, REG, except the pay type is hourly
- RGH is treated as “REG Equivalent”

How can you tell if you are impacted by #1 or #2’s new values?

Consider how you filter or query on these concepts. If a new value shows up, will that impact your results?
If you do any filtering or querying around “regular earnings”, based on what you are trying to accomplish, you may need to include “RGH” in the long list of codes that are treated like “REG”.

We will continue to update this page with new information related to the NWH/UWP Implementation Project based on your questions and as new information emerges.

If you have any questions about these changes, as always, we’re here to help you. So are our partners with the NWH/UWP Implementation Project. The best way to reach us is:

For EDW questions, email help@uw.edu with the subject, “EDW Question about NWH/UWP”
For EWS questions, email hrpws-support@uw.edu

EA Showcase 2019 – Event Resources

The EA Showcase 2019 was a success. We had a good crowd from across campus join us for the morning. The morning started with high level challenges and trends and then moved on to table work on UW-specific concerns. All of this was used to demonstrate why Enterprise Architecture has moved away from developing “wiring diagrams” of the technology towards a much more collaborative and cross-cutting change leadership practice.

What did attendees take away?

“It was great to have a conversation at the enterprise level.” “Hearing from the panelists – their expertise and experience.” “It was good to talk to people from other parts of the University.” “I liked the direction and leadership based on the EA Charter and resources.” “There is so much to leverage from the website including the resources and the EA team’s expertise.” “Three hours was not enough time.”

Summary and Links to Resources:

Jim Phelps, Director of Enterprise Architecture & Strategy, started the morning talking about Digital Transformation and the impacts it will have on society and universities. Megan Kennedy, Interim Director of the UW Resilience Lab, followed up with a discussion about student well-being and student challenges around food, housing and mental health. Ann Anderson, Associate Vice President of Finance, then discussed the financial realities of higher education and how UW is meeting those challenges. Ann also discussed the drivers for the UWFT program.

These high-level challenges and trends set the stage for the rest of the morning which was spent doing group work at tables. The EA team; Rupert Berk, Jacob Morris and Piet Niederhausen; walked the room through the EA Value Chain and Architectural Value statements. The tables then worked on a architectural challenge in their space – developing a trends and drivers, a scope statement, and doing an engagement scan.

The closing highlighted how architectural problems are “adaptive problems” that is problems where the both problem is unclear and the solution is unclear. These problems require a collaborative and matrixed response to solve. The idea of the “virtual architect” – that is a collaborative architectural effort involving many participants – was introduced as the new way EA thinks about their practice.

The team highlighted resources (linked from our website) that are available to the attendees to help them in their own work.

Resources:

RPG: Updates to Student Course Distribution

Based on user feedback, the Report Prioritization Group released updates to the BI Portal visualization, Student Course Distribution.

Change Summary:

This is the first major update to the Course Progress in Major/Student Course Distribution visualization since it was implemented about 3 years ago. The changes discussed below are mostly usability enhancements based on feedback we collected from users.

Before

After

Display:
- Changed cells from circles to squares
- Removed Course Names from being displayed vertically on the top of a viz. Course information is now available in the tooltip.
- Updated the colors to be more intuitive (i.e. gray for “no activity”)
- Added a legend to clarify what is being displayed
New “Drill-down” Functionality:
- This visualization now includes a new drill-down represented by a “plus” sign next to a Student No column label
- This allows a user to drill to a course section level via curriculum code. If a user wants to go back to a starting point, just click on the “minus” sign next to a “Curriculum Code” label.
Filters:
- New Filters allow you to more easily narrow down the results
  - Campus
  - Curriculum Code
- Updated filters:
  - We moved the filters section from top of the viz to the left freeing up extra space for more filters.
  - We made the filters “cascading” so that each filter only shows relevant values based on the previous selections

What is Next?

We have additional updates planned for Course Progress in Major, including:

Splitting the three views into three distinct, but linked tools.
Update the data source to pull in more than just the 20 “most common” courses now that we have additional space in the chart.
Prototype the integration of MyPlan data to demonstrate if a student plans to take a course in the future.

Questions and Feedback:

These updates were based on feedback from users like you. We rely on your questions and comments to help us understand what we should build/change, so please don’t hesitate to write to share your experience, feedback, or suggestions for improvement.

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

EDWPresentation – UWProfilesStudent Reorg 11/4/2019

This table is scheduled to be updated on Monday, November 4^th, 2019.

Logic changes:

Table Name	Column	New Logic
UWProfilesStudent	VisaType	Blank values will show as NULL
UWProfilesStudent	RunningStart	Blank values will show as NULL

Columns to be deleted:

Table Name	Column Name	Notes
UWProfilesStudent	ToalUpdTransfer	There was a typo in the original column name. There is another column in the table, “TotalUpdTransfer”, that should be used instead.

Please send all questions and concerns to help@uw.edu, with the subject line “UWProfilesStudent Update 11/4/2019”.

EDW Disaster Recovery Exercise on October 23, 2019

On Wednesday, October 23, 2019 from 8 a.m. to 10:30 a.m. the Enterprise Data Warehouse team will complete our Annual Disaster Recovery Exercise. Because this exercise affects the EDWPUB SQL Server as well as Reporting Services Reports hosted on the BI Portal, your user experience may be impacted in the following ways:

System performance issues may occur during this time
Potential service disruptions
Potential delayed data

This exercise has been rescheduled from its original date of October 9, 2019.

If you have any questions or concerns, please contact us at help@uw.edu.

Update October 23, 9:22 a.m.: The EDW Annual DR Exercise is complete. All systems are back to normal.

EDW Disaster Recovery Exercise Rescheduled

Update Oct 9 2019 8:21AM: The EDW DR Exercise previously scheduled for this morning has been cancelled and will be rescheduled for a later date.

On Wednesday, October 9, 2019 from 8 a.m. to 10:30 a.m. the Enterprise Data Warehouse team will complete our Annual Disaster Recovery Exercise. Because this exercise affects the EDWPUB SQL Server as well as Reporting Services Reports hosted on the BI Portal, your user experience may be impacted in the following ways:

System performance issues may occur during this time
Potential service disruptions
Potential delayed data

If you have any questions or concerns, please contact us at help@uw.edu.

Report Update: Undergraduate Eligible to Register (Columns to Display, Major/Minor and Intended Major Filters)

The Report Prioritization Group released an update to the Undergraduates Eligible to Register. This post summarizes those changes.

Change Summary:

UPDATE – Minors
1. When “Default” value is selected in “Columns to Display” filter, Minors columns is displayed in the report.
2. “Minors” is no longer an option in “Columns to Display” filter.
3. The Majors/Minors filter order is consistent with updates to Student Degree Information.

NEW – Applied to Graduate
1. A field in the “columns to display” dropdown is added called “Applied to Graduate”
2. When the user selects the “Applied to Graduate” field the report displays a student’s year qtr that they applied to graduate in the format YYYYQ
3. If a student has not applied to graduate the field displays a blank.
4. A student’s Applied to graduate status is sourced from ACAD_dsetEligibleToRegister applied to graduate (degreeearnedyrqtr) column.

FIXED – Include Intended Majors filter
- We received questions on the behavior for the “Include Intended Majors” filter in Undergraduate Eligible to Register report. Previously, when users selected ‘Y’ for “Include Intended Majors”‘, the student population did not always display intended major students in the report. Students that showed up with intended major in a selected department were either declared majors within that department or pending major in the next quarter of enrollment. The report did not report other students who had intended major in the selected department. This issue has been resolved.
- Included below is a list of “Include Intended Majors” filter behavior:
  1. When “Include Intended Majors” filter is selected to value ‘Y’, and a student is a pre-major with the selected intended major, a student will be listed in the report.
  2. When “Include Intended Majors” filter is selected to value ‘Y’, and a student currently in other department/major, with the selected intended major, a student will be listed in the report.
  3. When “Include Intended Majors” filter is selected to value ‘Y’, and a student is a pending-major, with the selected intended major, a student will be listed in the report.

UWSDBDataStore Reorg on October 19, 2019

Hello,

The student database (SDB) will go through a reorganization on Oct. 19, 2019 which will cause changes in the Enterprise Data Warehouse database, UWSDBDataStore. These changes to the objects will be available in the UWSDBDataStore on Monday, Oct. 21, 2019 . (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.) *

New columns to be added:
(last updated: 10.14.2019)

Table Name	Column Name	Data Type	Description
budget_resources	br_pkg_formula_no	tinyint	The packaging formula being used for the student for the specified quarter
sa_sf_fund	osfa_group_code	char(1)	Code for grouping Awards (“G”rants “W”ork Study “L”oans “P”lus Loans blank=not assigned)
sdb_security	sdb_sf_power_user	char(1)	Specifies someone in OSFA who can edit restricted fields on finaid screens.
sf_evaluation	s_filed_sched1	char(1)	Student filed a Schedule 1? (2, yes 1, no 3, don’t know, or blank)
	s_untax_ira_pensn	int	Student’s Untaxed Portions of IRA Distributions and Pensions
	p_filed_sched1	char(1)	Parents filed a Schedule 1? (2, yes 1, no 3, don’t know, or blank)
	p_untax_ira_pensn	int	Parents Untaxed Portions of IRA Distributions and Pensions
	child_fallen_hero	char(1)	Children of Fallen Heroes Indicator (1=yes, 2=no, blank=no determination made)
sr_adm_appl	cnet_account_id	integer	CollegeNet account ID.
	slate_appl_id	integer	Slate application ID
	pg_schol_amt	integer	Purple and Gold Scholarship amount
student_1	cnet_account_id	integer	CollegeNet account ID.
student_1	slate_appl_id	integer	Slate application ID
student_2	s2_iso_stat_id	char(12)	ID of S2-ISO-STATUS updater.

*Please Note: The scope of the reorg may still change. Please check this site constantly to see changes to the scope.

EA Showcase October 22, 2019

Transformative change in higher education and at the UW is creating new opportunities to improve teaching and learning, research, and administration. These opportunities challenge us to find new ways to adapt and collaborate.

Please join the Enterprise Architecture (EA) team and your colleagues from across the UW in a hands-on workshop to:

Explore the digital transformation already under way and national trends in higher education with a panel of experts
Discuss how this context impacts your work
Learn how architecture work is changing in this context
Work with peers to scan the landscape and plan collaborations to address architectural problems
Understand how the EA team and its resources can support you in this work

UPDATE: Registration is now closed. Summary and resource materials from the event can be found here.

When: October 22, 2019 at 9:00am-12:00pm – Meet & greet over coffee starting at 8:30am

Where: Husky Union Building (HUB) Lyceum (1st floor)

More information on Enterprise Architecture

New Report and Data Release: Cost Share Commitments and Contributions

Now Available in the EDW: Cost Share Commitments and Contributions data and report

Do you find managing Cost Share with the Unmet Cost Share Report difficult because of the way commitments and contribution details are organized? If you could, would you like to dive deeper into how Cost Share commitments roll up to the Financial Organization at the Dean, Major, Department, and Subdivision levels? Would it make your life easier if you could filter out closed Cost Share Commitments or analyze data by the Award End Date?

Thanks to the partnership between Information Management team in UW-IT and Kirsten DeFries, Andra Sawyer, and Matt Gardner of the Post Award Fiscal Compliance team, access to this information and more is now available in the EDW, BI Portal, and Knowledge Navigator.

New Report:

The new Cost Share Commitments and Contributions report, available in the BI Portal, shows summary information on all active Sponsored Awards with a Cost Share Commitment entered into the Cost Share Summary system.

The report gives users detailed and summary information and the ability to analyze Cost Share commitments by the four levels of the Org Code (Dean, Major, Department, Subdivision), Award PI, and days until award end date.

New Data:

The report sources all of the data from a new table in the EDW. The new EDW data sourced from the Sponsored Award as entered into the SAGE suite and the Cost Share Summary system.

This data can be accessed via ODS.sec.CostShareBalancePeriod. You will find fields that relate revenue source, object code, expiration days, commitment descriptions, Cost Share type (mandatory or voluntary), and more. The table can be easily joined using budget number or account code to other financial tables within ODS.

Server: edwpub.s.uw.edu
Database: ODS
View: sec.CostShareBalancePeriod

Questions:

If you have any questions about the meaning of Cost Share or any other terms referenced in this report, please visit Knowledge Navigator.

If you have any questions about the report or would like to provide feedback, please write gcafco@uw.edu and put “BI Portal Cost Share Commitments and Contributions Report” in the subject line.