IT Connect

Information technology tools and resources at the UW

Archive for category: News

Enterprise Data & Analytics and Enterprise Data Warehouse news item

2019 Biennium Crossover: EDW Data, Reports, Cubes

On July 1, the University of Washington entered the 2019 biennium.

EDW Data

‘CurrentBiennium’ now refers to 2019 biennium data.

Cubes

The Financial Activity 2019 Biennium cube was released this week. The EDW now supports financial cubes for 7 biennia, going back to 2007. The 2017 Biennium cube will continue to refresh until August 1, 2019, then will be frozen.

Visit the Business Intelligence Portal to learn more about available cubes; for a quick reference on connecting to cubes, visit  Connect to Cubes page.

Report Update: Undergraduate Eligible to Register (Major/Minor Filter)

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

Change Summary:

    • The Major / Minor report  filter now list values for students Major and Minor.

Questions?

If you have questions about “Major” or “Minor” fields please explore the links above or find more detail in the Knowledge Navigator.

As always, if you have any questions, please write help@uw.edu and put “RPG: Undergraduate Eligible to Register” in the subject line.

Report Update: Student Grade Report by Major (Gender Filter)

The Report Prioritization Group released an update to the Student Grade Report by Major. This post summarizes those changes.

Change Summary:

  • “Gender” Filter updated with an option to display blank values to:
    • include students who do not have a value in a gender field, i.e. it is blank
    • and to be consistent with other reports that have an option of a blank value

Questions?

If you have questions about “Gender” field please explore the links above or find more detail in the Knowledge Navigator.

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

Report Update: All Applications

The Report Prioritization Group released an update to the All Applications. This post summarizes those changes.

Change Summary:

    • SAT – a student’s SAT Scores are displayed in the report regardless of when they completed the test.
    • Last Institution Name – The applicant’s valid last institution name displays regardless of whether it was a high school, or other type of institution.
    • Other Email – The “other email” Columns to Display field label is updated to read “External Email” to be consistent with other Academic Reports.
    • Advising Type and Date fields are added by request from UW Bothell.
      • Advising Date – date assigned for student to attend new student orientation.
      • Advising type – all confirmed in-coming new students (FTFY and Transfer) are required to sign up for an orientation/advising. “Session Type” new student orientation D = DAY ONLY O = OVERNIGHT C = COMMUTER (NOT USED)

Questions?

If you have questions about the meaning of Advising Date or any other terms used in the report, please find more detail in the Knowledge Navigator.

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

Report Replacement: Learning Spaces Policy (previously known as Total Class Utilization by Hour)

The Report Prioritization Group released a new report, the Learning Spaces Policy report (Access to B.I. Portal report requires access to EDW and it is limited to Time Schedule coordinators.) This report replaces the Total Classroom Utilization by Hour report.

The Learning Spaces Policy report is intended to be used by time schedule coordinators to comply with the Learning Spaces Policy. Although the policy only applies to the Seattle campus, Tacoma and Bothell course distribution data is included. The chart and reference lines are intended to help time schedule coordinators to see when course distribution is out of alignment with the policy.

The Total Class Utilization by Hour report will be decommissioned from BI Portal on June 26, 2019.

Change Summary:

  • The report was redeveloped to correctly handle curricular joints and ad hoc joints ‘withers’.
  • The report also removes courses that are responsible by another scheduling unit.
  • A bug in the old report is being addressed in the new report where classrooms scheduled after 2:30 pm were not being counted.
  • The performance of the main dataset query has been greatly improved. The old query took more than 15 minutes to run with 2 curriculum abbrs. The new report can run the entire college of arts and sciences in 2 minutes.
  • A table with time blocks, primary instructors, SLN, courses and classroom locations has been added.

Questions?

If you have questions about the new report please write to help@uw.edu, and put “RPG: Learning Spaces Policy” in the subject line.

Report Update: Student Grade Report by Major (Intended Major Filter)

The Report Prioritization Group released an update to the Student Grade Report by Major. This post summarizes those changes.

Change Summary:

  • “Include Intended Major” Filter added: by request from UW Bothell and UW Tacoma, a filter was added to allow you to include/exclude students that have listed the selected major as an “intended major”. The filter has a Yes and No Option:
    • Yes = Returns “pending” and “real” majors and also includes students that have listed “intended” and “requested majors” for the selected major.
    • No = Only returns students with majors in “pending” status and “real” majors (a student that is already in a given major). (This is the default selection)

Questions?

If you have questions about the meaning of intended major, pending major, or major requested, please explore the links above or find more detail in the Knowledge Navigator.

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

Report Update: Current Student Information By Major (Intended Major Filter)

The Report Prioritization Group released an update to the Current Student Information By Major report. This post summarizes those changes.

Change Summary:

  • “Include Intended Major” Filter added: by request from UW Bothell and UW Tacoma, a filter was added to allow users to include/exclude students that have listed the user’s selected major as an “intended major”. The filter has a Yes and No Option:
    • Yes = Returns “pending” and “real” majors and also includes students that have listed the selected major as an “intended” or “requested major”.
    • No = Only returns students with majors in “pending” status and “real” majors (a student that is already in a given major). (This is the default selection)

What’s Next:

Questions?

If you have questions about the meaning of intended major, pending major, or major requested, please explore the links above or find more detail in the Knowledge Navigator.

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

 

Check Out an Awesome New Tool: Tableau Prep

Are you tired of manually cleaning and preparing datasets before you can begin your data analysis? It’s a rule of thumb among data analysts that 3/4 of any analysis project is spent on sourcing and preparing data. But it doesn’t have to be this way!

A new self-service data preparation toolset from Tableau now makes it easier than ever to connect to, union, join, pivot, and clean your data: Check out Tableau Prep

Use Cases:

If you source all of the data for your solution from the EDW, then this solution may not be right for you.

But if you’re one of the many Tableau users who sources data from the EDW and merges it with local data, or if your analysis is built on a spreadsheet, CSV, or multiple disparate sources, and you spend hours merging spreadsheets, and writing complicated functions to clean and prepare the data, then Tableau Prep may help save you time on your next project!

Learn More:

You can access these online resources to learn more about Tableau Prep:

Get Started:

If you have a Tableau Desktop license distributed and managed by UW-IT, then Tableau Prep is available to you. Here’s what you need to do to get started: 

  1. Confirm that you have a UW-IT managed Tableau Desktop license.
  2. Request a product key. To request your Tableau Prep product key simply write help@uw.edu with “Request: Tableau Prep Product Key” in the subject line.
  3. Download the latest version of the software here on Tableau’s site to get started
  4. Install the software and begin prepping your data

Important to Know:

  • You must already have a UW-IT managed Tableau desktop license to use Tableau Prep. If you don’t have a UW-IT managed Tableau desktop license, UW-IT will not be able to provide you with a Tableau Prep license. You can learn more about how to request a Tableau Desktop license here.
  • If you work for UW Medicine or its affiliates and are interested in using Tableau Prep, you should contact UW Medicine IT to learn more.
  • If you are a student and you are interested in using Tableau Prep, you should contact Tableau directly to learn what options are available.

Report Update: Student Degree Information

The Report Prioritization Group released two updates to the Student Degree Information Report. This post summarizes those changes.

Change Summary:

  • Bug Fix: There was a bug in the report code that was causing valid majors to be excluded from the majors dropdown. This bug has been resolved.
  • First Generation Attributes added: by request two new student attributes have been added to the report:

FirstGenAttributes

To include these columns in the report, simply select them from the “columns to display” dropdown menu.

Questions?

If you have questions about the differences between these two First Generation Student definitions, please explore the links above or find more detail in the Knowledge Navigator.

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

Now Available in the EDW: Finance Date Functions

We are proud to announce the release of pre-defined date functions in the EDW. 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 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

SQL Examples:

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

If Parameters are a 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

If Parameters are a rolling window (2 years back from a given 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: Date Functions” in the subject line