IT Connect

Information technology tools and resources at the UW

Archive for category: News

Enterprise Data & Analytics and Enterprise Data Warehouse news item

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

 

New Features in the 2019.1 Tableau Version

As you may know, we recently upgraded our Tableau server version from 2018.1 to 2019.1. This new version includes several features that we want to summarize here:

Mixed content:

Tableau has improved the browsing experience by offering a mixed content view.  Workbooks, projects, and data sources within a project are now displayed in a single, filterable list. This makes it easier and more efficient to find what you are looking for.   

You can read more about this change here: New Browsing Experience

Ask Data:

This new version also includes a new feature called Ask Data, a natural language processing tool built into Tableau that interprets your data-related questions, written in plain English, and returns an answer, typically in a visual format. Read more about it here.

This month we begin a UW pilot of Ask Data in collaboration with a select group of users and folks from Tableau.

Questions:

As always, if you have any questions about this upgrade please write us at help@uw.edu, and put “Tableau Server Upgrade to 2019.1” in the subject line.

Archived BI Portal Reports: My People Reports No Longer Available on BI Portal

The Business Intelligence Team in UW-IT archived 4 HR/Payroll reports. As a result, these reports are no longer available in the BI Portal or on the SSRS Server.

This change further reduces the amount of technical debt the team carries and enables us to focus on building and maintaining more valuable analytics tools.

Change Summary:

During Workday go-live, many reports were updated to source Workday data via the EDW. Now a year after go-live, these reports were getting little to no usage. Rather than continue to maintain these tools, we opted to archive them.

Before archiving the reports, we reached out to users in order to understand legacy report usage. We learned that some legacy reports continue to offer business value, and those reports are still available on the BI Portal.

See the bottom of this post for a full list of the reports that we archived.

Questions:

If you have any questions about this change, or if you think we inadvertently archived a report that you still need in order to do your work, please write us at help@uw.edu and put “BI Portal Archived Reports Question” in the subject line.

Archived Report List:

  1. My People Menu
  2. My People by Home Department
  3. My People Search
  4. My People by Appointment

BI Portal Update: Reporting Services Update Completed

  • The audience for this message is all BI Portal Report users.

On Sunday March 10 the Business Intelligence Team and UW-IT Windows Engineering team completed the update and testing of our reporting software. After the update, reports have a new look and feel, but there are no changes to the report parameters or the underlying data that you use to do your work.

More Details:

Change Summary:

  • SQL Server Reporting Services(SSRS) is the software behind all of our BI Portal Reports.
  • The Business Intelligence team updated and tested the SSRS version from 2012 to 2017.
  • The new SSRS version has many new features including an improved look and feel that will be noticeably different: 

TimeSched

Impact:

  • The impact to users is minimal. This message is just for awareness.
  • Users do not need to make any changes in response to this change, and there will be zero interruption of service as a result of the upgrade.
  • Users will notice that the report look and feel has changed. However, the report parameters, columns, and underlying data will not be affected by this update.

Questions?

If you have any questions in response to this change, please write help@uw.edu and put “BI Portal: Reporting Services Upgrade” in the subject line.

BI Portal Software Update: Reporting Services

  • The audience for this message is all BI Portal Report users.

In the coming days, the Business Intelligence Team will update our reporting software version. After the update, reports will have a new look and feel, but there will be no changes to the report parameters or the underlying data that you use to do your work.

More Details:

Timeline:

  • We have scheduled this update for Sunday March 10. When users log in on Monday, the new version will be available.

Change Summary:

  • SQL Server Reporting Services(SSRS) is the software behind all of our BI Portal Reports.
  • The Business Intelligence team is updating the SSRS version from 2012 to 2017.
  • The new SSRS version has many new features including an improved look and feel that will be noticeably different.

Impact:

  • The impact to users is expected to be minimal. This message is just for awareness.
  • Users do not need to make any changes in response to this change, and there will be zero interruption of service during the upgrade.
  • Users will notice that the report look and feel has changed. However, the report parameters, columns, and underlying data will not be affected by this update.

Questions?

If you have any questions about this change, please write help@uw.edu and put “BI Portal: Reporting Services Upgrade” in the subject line.

New Release: Helping Users Find Their EDW Authorizers

We are proud to announce the release of a new solution that helps enterprise data users find their ASTRA authorizers, to request access, and begin querying and analyzing UW enterprise data.

Background:

Enterprise data users need permission to access reports and visualizations on the BI Portal, UW Profiles, or to directly query the Enterprise Data Warehouse (EDW). Requesting an ASTRA role is the first step in any enterprise data user’s interaction with our teams and up to now it has been a painful one.

The process users follow to find their authorizer and request access has always been hard to navigate, prone to error, and required running multiple SQL queries (yes, manually!).  This convoluted process led our customer to become frustrated and confused at the very beginning of our partnership.

Requests for help with this process are our number one most common help ticket and represent about 10% of our help tickets from the last 12 months.

For Requestors: A Complete Overhaul

New and existing enterprise data users who need to either request a new role or update their existing role, now have a clean and simple tool that helps them find their authorizer and request access!

Screen Shot 2019-02-12 at 1.48.10 PMWith the new Find My Authorizer Tool, requestors no longer need to guess who their authorizer is. They simply click on a link from our site, or navigate directly to the tool: https://edwaccess.biportal.uw.edu/.

After logging in, they choose their role based on their work requirements and choose an authorizer from the ranked list. Users no longer need to draft an email, and wonder what to say. Instead, they send their request directly from the tool! A short email is automatically generated that notifies their authorizer of the request, and includes all of the information about the request, as well as next steps for following up.

But the web app isn’t the only exciting new solution! Behind this modern user interface is a new web API that pulls in a user’s organization information, compares it to a list of authorizers and their organization affiliation, and generates a ranked list of recommended authorizers based on a set of criteria. But, this new API isn’t just for this specific EDW use case, it can also be used to identify authorizers for other enterprise applications!

For Unit Authorizers: A Streamlined Process

For unit authorizers nothing changes in terms of how to grant permission to a requestor. After receiving a request, if you determine that the role and request is appropriate, you still navigate to the ASTRA admin page to complete the permissions process.

The difference is how requestors find you and contact you. In the past, the process was free-form and inconsistent. Now, a requestor sends a consistent email message with the click of a button that includes their name, netid, and requested role. Here’s an example of the request email.

email

More Work on the Horizon:

To be clear, this new tool only removes the pain for users whose permission can be granted by a local unit authorizer, an estimated 80-90% of all requests.

For roles that require Data Custodian approval, the process continues to be unclear,  painful for both requestors and approvers, and requires much manual intervention.The Find My Authorizer Tool can be built to accommodate the Data Custodian approval process, but more resources are required to clarify the existing process and design a more robust solution.

Questions?

As always if you have any questions about this release, the Find My Authorizer tool, or the underlying web service, please write help@uw.edu and put “Find My Authorizer Tool” in the subject line.


This news is brought to you by two teams in UW-IT, Information Management: Enterprise Reporting & Analytics and Enterprise Data Integration

Report Update: Student Degree Information

The Report Prioritization Group released a small update to the Student Degree Information Report. This post summarizes those changes.

Change Summary:

Based on user feedback, we added 2 new data elements to the report:

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

Questions?

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