Skip to content

Archives: Service News

News content type for use by UW-IT Services on IT Connect

New PAS Data Enhancement in the EDW

Procurement Credit Card Spend Data Now Matched to True Supplier

In October of 2018, a project was initiated to capture all suppliers from the University’s Procurement Accounting System (PAS) in the Enterprise Data Warehouse (EDW). The project was comprised of three major steps:

  • First, to capture all existing Suppliers from the PAS system in EDW 
  • Second, to secure all Spend financials and diversity indicators attributed to those suppliers 
  • And last, to attribute Procurement Credit Card transactions to the true supplier, not simply the credit card merchant (JPMorganChase)

In late January 2021, this final planned enhancement to the EDW for the PAS spend and diversity data was completed. 

What’s New
Now, users who query the EDW for procurement data have the ability to see credit card (Procurement Card, ProCard) transactions attributed to the true suppliers of those transactions. Rather than the default supplier designation of JP Morgan Chase, procurement card transactions show the true supplier*.  With the true supplier designations for the majority of credit card transactions, users can easily determine which transactions were made with diverse suppliers (e.g. women-owned, veteran-owned and minority-owned businesses).

The credit card transaction data provides specific supplier information which in turn, provides details for diverse spend. The PAS data in the EDW shows:

  • Over half a million ProCard transactions assigned to the true supplier
  • Over 140,000 ProCard transactions assigned to diverse suppliers

Why This Matters
Accurate, secure and accessible data is critical for:

  • Financial Transformation
  • Regulatory reporting requirements
  • Department analysis, accountability, and decision-making
  • The University’s commitment to diversity, equity and inclusion efforts

What Can This New Data Tell You?

  • In the calendar year XXXX, how much did my department spend with diverse suppliers?
  • In fiscal year XXXX, what percentage of our credit card transactions were attributed to diverse suppliers?
  • In the last biennium, which diverse suppliers did the majority of our credit card transactions go to?
  • And more

The project to provide this much needed PAS data is the result of the successful partnership between Ann Anderson, Associate VP, UW Finance, Doug Divine, Director, DATAGroup/Global Operations Support, Financial Management and the UW-IT Information Management division.

Business Analysts can execute SQL queries to access and analyze PAS data. See Eric Elkins’ Knowledge Base Article for basic queries and additional insights into using PAS data in the EDW.

KNOWLEDGE BASE

To access EDW data, you need to be authorized via ASTRA. To get more information on accessing the EDW, click the link below.

ACCESS EDW DATA

If you have questions or need support, please send your correspondence to help@uw.edu with “EDW PAS data” in the subject line.

_____________

*Please Note: Due to some technical limitations in the data provided by JPMorgan Chase, not all true suppliers can be attributed to procurement credit card transactions.

BI Portal Update: Consolidated Budget Status Reports

Update (5/26/2021): This work is complete and now the following reports are no longer available on the BI Portal: Consolidated Budget Status Report Menu and Consolidated Budget Status Report by MyFinancialDesktop Static Budget List.

The Enterprise Reporting and Analytics team just released an update to the BI Portal Consolidated Budget Status reports. This update contributes to our goal of preparing for Finance Transformation by simplifying and consolidating reports in our BI Portal report catalog.

There are three Budget Status reports currently accessed from the Consolidated Budget Status Report Menu in the BI Portal. The updates described below apply to these 3 reports and the menu:

 

Why Should You Care?

You may currently use the Consolidated Budget Status Report Menu report as a point of access for three reports that allow you to see budget status, with breakdowns by:

  • Organization Code
  • MyFD Static Budget List
  • Parent Grant/Budget

Please Note: We are in the process of making updates to the three reports and the menu that will change how you access this information. In short, we’re trying to make it easier and clearer to get the information you need, rather than forcing you through an unnecessary “menu” report.

Change Summary:

You can now access these reports directly from the BI Portal, without having to search for the “report menu”!

In this release we made the following changes:

In the coming weeks we will make the following changes:

  • We will archive the “menu report” Consolidated Budget Status Report Menu (Yay! Fewer clicks to get where you’re going!)
  • We will archive the ‘Report by MyFinancialDesktop Static Budget List’. The BI Team will work with users of this report to help them begin using the Financial Activity Cube to replace this report.

Questions and Feedback:

For definitions of any of the concepts included in this report, please see the Definitions tab in the BI Portal or visit Knowledge Navigator.

If you have any questions or feedback about the changes to this report, please write help@uw.edu with “BI Portal Question: Budget Status Reports” in the subject line.

This work is part of our team’s effort to prepare for Finance Transformation by consolidating and simplifying BI Portal reports. We appreciate your patience and partnership!

SpaceWS EVAL Release: Support for migrating to the new Space Inventory Management System

During the last week of March 2021, the Capital and Space Management team will be migrating to a new Space Inventory Management System.

We are adapting Space Web Service (SpaceWS) to the source system changes which will result in:

  • Some field type and length changes
  • Some fields will no longer provide any data (We will NOT remove any fields at this time)

Change Details:

The list of field changes that may impact you are available on our Wiki at https://wiki.cac.washington.edu/display/EBWS/SpaceWS+v1+Source+Change


Opportunity to Evaluate:

These changes are in the SpaceWS EVAL environment and available for you to test. Please see https://wiki.cac.washington.edu/display/spacews/Environments for instructions on connecting to this environment. We are scheduled to release these changes into Production in coordination with the Capital and Space Management team on March 31, 2021

Support:

Please contact us at spacews-support@uw.edu

We will send an additional communication when these changes are in Production.

BI Portal Update: New Filters on Fiscal Year Income and Expense Summary

The Enterprise Reporting and Analytics team just released an update that combines two BI Portal Finance reports:

  • Fiscal Year Income and Expense Summary by OrgCode and Funding Source
  • Fiscal Year Income and Expense Summary by OrgCode and Function

Into one report, now called

Why Should You Care?

If you use the …by Org Code and Funding Source report you’ll notice it now has new filters and a new (shorter) name! If you use the …by OrgCode and Function report, you should now use the updated report (Fiscal Year Income and Expense Summary).

Please note:

  • If you exclusively use the …by OrgCode and Function report, in the next two weeks, it will be archived and no longer available on the BI Portal. Please take time to familiarize yourself with the updated Fiscal Year Income and Expense Summary report!
  • Update: If you exclusively use the …by OrgCode and Function report, it has been archived and  is no longer available on the BI Portal. Please take time to familiarize yourself with the updated Fiscal Year Income and Expense Summary report

Change Summary:

Previously the two reports differed mainly in the way the columns displayed – either by Function or by Funding Source. Now, you can get either view by going to a single report (Fiscal Year Income and Expense Summary) and simply selecting your preferred view in the ‘View by’ parameter (it defaults to funding source, since that’s the way most people were viewing it).

 

There are also filters to choose which funding sources and functions you’d like to include.

This update makes the Fiscal Year Income and Expense Summary by OrgCode and Function report obsolete. Since it is no longer necessary now that we’ve included all functionality in the new report, we will archive this report in the next 2-3weeks.

Questions and Feedback:

For definitions of any of the concepts included in this report, please see the Definitions tab in the BI Portal or visit Knowledge Navigator.

If you have any questions or feedback about the changes to this report, please write help@uw.edu with “BI Portal Question: Fiscal Year Income and Expense Summary” in the subject line.

This work is part of our team’s effort to prepare for Finance Transformation by consolidating and simplifying BI Portal reports. We appreciate your patience and partnership!

How To: Finance Date Functions in EDW

Do you ever get tired of building complicated date logic in your EDW queries?

The EDW now supports several finance date functions to help you simplify your queries! These functions help enterprise data users to easily convert calendar dates, months, and years to UW fiscal calendar objects. These functions are most helpful to SQL developers working on reports and visualizations that use fiscal calendar parameters/variables.

Context: 

In SQL, a function is a program that takes some input and generates a value. SQL has many built-in functions that you get out-of-the-box.  For example, one of the built-in system functions is GETDATE().  This function returns the current date and does not require an input or argument. Another example of a system function is REPLACE(), which expects to have 3 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 UW-IT EDW team recently added 5 new functions to the EDW. The following functions are now available to use:

Function and Syntax Description
FiscalYear(date) Returns the fiscal year as a number based on the date input you provide.
FiscalMonth(date) Returns the fiscal month as a 2-character string based on the date input you provide.
BienniumYear(date) Returns biennium year as a number based on the date input you provide.
BienniumMonth(date) Returns biennium month as a 2-character string based on the date input you provide.
LastMonthEndDate(date) Returns last date of the prior calendar month based on the date input you provide.

How you can use these functions today: 

If you ever do your own calculations in SQL to determine things like Fiscal Year or Biennium start or end dates, or create a rolling window, then you might benefit from using these functions.

These functions reside in EDWPresentaton database.  To call a function simply follow examples below:

SELECT 
EDWPresentation.dbo.FiscalYear('4/4/2018') as FiscalYear,
EDWPresentation.dbo.FiscalMonth('4/4/2018') as FiscalMonth,
EDWPresentation.dbo.BienniumYear('4/4/2018') as BienniumYear,
EDWPresentation.dbo.BienniumMonth('4/4/2018') as BienniumMonth,
EDWPresentation.dbo.LastMonthEndDate('4/4/2018') as LastMonthEndDate

Please Note:

  • When functions are used as parameters in a report or visualization, they should be set as variables.
  • Also, the BienniumMonth function returns values ranging from 01-24; however, UW accounting months are 01-25 or in previous years may even have fallen between 01-27. This happens because some transactions are entered in a new biennium, but should be attributed to the prior one. Examples below show how to use functions in variables and how to handle accounting months

Examples

SQL – if parameters are start and end dates

--Report parameters DECLARE @MyDate date = '4/4/2018' 
DECLARE @FiscalYear varchar(4) = EDWPresentation.dbo.FiscalYear(@MyDate)  DECLARE @AcctngMonth int = 
case 
   when EDWPresentation.dbo.BienniumMonth(@MyDate) = '24' 
      then '27'  
   else EDWPresentation.dbo.BienniumMonth(@MyDate) 
end DECLARE @BienniumYear varchar(4) = EDWPresentation.dbo.BienniumYear(@MyDate) SELECT @FiscalYear FiscalYear, @BienniumYear BienniumYear, @AcctngMonth AcctngMonth

SQL – if parameters are month and year

--Report parameters DECLARE @calmonth int = 9 DECLARE @calyear int = 2017 

--Converting calendar month and year to a date that can be used by functions
DECLARE @MyDate 
   date = convert(date, convert(varchar(4),@calyear) + '-' + convert(varchar(2),
       @calmonth) + '-01') 
DECLARE @FiscalYear varchar(4) = EDWPresentation.dbo.FiscalYear(@MyDate)  DECLARE @AcctngMonth int = 
case 
   when EDWPresentation.dbo.BienniumMonth(@MyDate) = '24' then '27'  
      else EDWPresentation.dbo.BienniumMonth(@MyDate) 
end
DECLARE @BienniumYear varchar(4) = EDWPresentation.dbo.BienniumYear(@MyDate)
SELECT @FiscalYear FiscalYear, @BienniumYear BienniumYear, @AcctngMonth AcctngMonth

SQL – rolling window (2 years back from end date)

--Report parameters DECLARE @EndDate date = '4/4/2018'
 --Converting end date into a 2-year rolling window  DECLARE @LastMonthEndDate date = EDWPresentation.dbo.LastMonthEndDate(@EndDate)
DECLARE @BienniumYear varchar(4) = EDWPresentation.dbo.BienniumYear(@LastMonthEndDate)
DECLARE @AcctngMonth varchar(2) = 
case 
   when EDWPresentation.dbo.BienniumMonth(@LastMonthEndDate) = '24' then '27'  
      else EDWPresentation.dbo.BienniumMonth(@LastMonthEndDate) 
end 
DECLARE @EndRollingPeriod varchar(6) = @BienniumYear + @AcctngMonth DECLARE @StartRollingPeriod varchar(6) = CONVERT(varchar(4), CONVERT(int, @BienniumYear)-2) + @AcctngMonth SELECT @StartRollingPeriod, @EndRollingPeriod

Questions:

If you have any questions about functions or how to use them in your solution, please write help@uw.edu and put “EDW Feature: Finance Date Functions” in the subject line.

Financial Activity 2019 Cube Update: New Attributes on Finance Cube

Based on input from users, we just published updates to the Financial Activity Biennium 2019 cube (finance cube). We added several new attributes to the cube that make it easier to get the finance data you need to do your work.

In addition to the cube changes, we also want you to know about some other great content we recently published!

  • Data Definitions: Finance cube data definitions are now in Knowledge Navigator
  • Training/Help Videos: We recently published a suite of videos to help you answer several common use cases with the finance cube. Check out our Finance Data Cube Resources page (EDW Access required).

Change Summary:

In this release we added several attributes to existing cube dimensions. These fields were added to support users that want to migrate from using BI Portal finance reports to the cube. If you’re tired of running a report, then exporting the data, filtering, and organizing it in the format you want, consider using the finance cube!

We added the following attributes to the Org-Budget Dimension in the “More Fields” grouping:

  • Budget Status
  • Old Budget Nbr
  • Parent Budget Nbr
  • Current Period Begin Date
  • Current Period End Date
  • Grant Tracking Nbr
  • Grant Contract Nbr
  • Total Period Begin Date
  • Total Period End Date

More Changes Are Coming:

We are currently designing a new Financial Activity Biennium 2019 cube. This new cube design will still provide you the data you need to do your work, but it will consolidate several dimensions, improve some of the dimension and attribute names, and overall make the cube easier to use!

In addition to the finance cube redesign, we are also preparing to release a new Financial Activity cube for Biennium 2021 in the next fiscal year. Please stay tuned for more information about the new cube.

If you would like to get involved with the finance cube redesign and share your feedback with the BI Team through our user review sessions, please write us at help@uw.edu with “BI Portal: Finance Cube Redesign” in the subject line.

Questions:

The finance cube references core institutional concepts like Budget Number, Account Code, and so many others. For definitions to these and other institutional terms, please explore Knowledge Navigator!

As always if you have any questions or feedback, please write help@uw.edu with “BI Portal: Financial Activity 2019 cube” in the subject line!

RPG Release: Student Groups on Satisfactory Progress Policy Report

Based on user feedback, the Report Prioritization Group just released an update to the report: Satisfactory Progress Policy List

The report now allows you to filter down to specific SDB Student Groups! This helps users answer the question, “are any students in a group I care about currently or projected to be in danger of producing unsatisfactory progress?”

Please note, in order to align with BI Portal naming standards, in this release we also updated the report’s BI Portal title:

  • From: Satisfactory Progress Policy Report
  • To: Satisfactory Progress Policy List

Background:

The SDB includes a feature called “Student Groups”. The Student Groups feature allows advisers, program coordinators, and others to work with the Office of the University Registrar (OUR), to create groups in SDB. The requester can then associate students with the groups in SDB. You can think of this like “tagging” students in the database with a group name. This is useful for tracking students beyond courses and majors. Some examples include:

  • Geography Departmental Honors
  • Fraternity Sigma Chi

You can explore the list of existing SDB Group Codes in the SDB Code Manual: UW Student Data

If you think you could benefit from this SDB Student Groups feature, or would like to learn more, please write studentdata@uw.edu with “SDB Student Groups Inquiry” in the subject line.

Change Summary:

We added two new Student Groups filters to the report:

Student Group Type

There are scores of Student Groups across all three campuses. To try to make this long list more manageable, we added a Student Group Type filter.

Student Group Types are just collections of Student Groups. For instance the Student Group Type “FRATERNITY/SORORITY” is a collection of groups like:

  • FRATERNITY CHI PSI
  • FRATERNITY DELTA CHI

Please note, most Student Groups do not have an associated Student Group Type, so we also included an option “No Group Type”

If you manage SDB Student Groups and you would like to associate them with an existing or new type, please write studentdata@uw.edu with “SDB Student Groups Inquiry” in the subject line.

Student Group

The Student Group filter allows you to filter down to a specific cohort of students that are associated with the group in SDB.

If you think you could benefit from this SDB Student Groups feature, or would like to learn more, please write studentdata@uw.edu with “SDB Student Groups Inquiry” in the subject line.

Columns to Display

We also added Student Group information as an optional column to display. Please consider:

  • The report does include Student Group information by default.
  • If a student is in multiple groups, then the report will display student group information in a comma-separated list.

Questions and Feedback:

This report references many UW institutional concepts, including Student Group and Student Hold. 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: Satisfactory Progress Policy List” in the subject line.

EDW Upgrade to SQL Server 2019 Complete

The EDW successfully upgraded our user-facing servers (EDWPub.s.uw.edu) to SQL Server 2019. 

We believe you will experience minimal impact, however, if you do experience any issues, please reference our IT Connect article titled, “Preview available for upcoming EDW server upgrade” which contains a list of known issues.

Reporting issues
If you experience any issues you are not able to resolve, please write help@uw.edu and put “EDWPub Server Upgrade” in the subject line.

Explore the Canvas course template

UW-IT and UW Learning Technologies developed a basic course template to help instructors quickly create courses that are easy for students to navigate. The template, which organizes course content within modules, is designed to accommodate a range of disciplines and teaching approaches. The design of the template was guided by two priorities: 1) to ease and simplify course creation, 2) to help students find the information they need. Our goal is for the template to empower instructors: to help them confident that they are creating a course that students will find easy to use.

Resources for Teaching Remotely

Explore resources for teaching remotely: UW information hubs from Bothell, Seattle, and Tacoma, including the Roadmap to Effective Online Teaching. Also included are Canvas course templates, Canvas model courses, a Canvas course readiness checklist and guides for teaching with UW technologies. Get support for remote teaching from video resources and workshops and webinars. Review the resources now.