Transcript HEUG PowerPoint Template

Data Warehousing at
The University at
Albany
Wendell G. Lorang
Director of Institutional Research
University at Albany, SUNY
AIRPO Winter 2006 Conference
Albany, New York
Overview
UAlbany implemented a Student
Records data warehouse that
denormalizes the data from over 60
PeopleSoft tables into seven tables.
More than 100 queries are available
to departments to use to obtain the
information they need.
Presentation
• Our PeopleSoft Implementation
• Problem Description
• The Solution
• The Issues
PeopleSoft at UAlbany
1996
Project Began
March 1999
June 2000
Undergrad Prospects
Human Resources
November 2000
Undergraduate and
Graduate Admissions
March 2001
Orientation and
Summer Planning
Conferences
4
PeopleSoft at UAlbany
June 2003
Student Records
January 2004
Financial Aid
May 2005
Full Student Financials
5
The Problem – A Common Question
“Can I get a list of all of the grad
students in my department who are
currently registered?”
The Problem – A Common Question
What data fields are needed?
Term, Empl ID, Name, Career,
Department, Program, Registered
Credits, Full/Part Time, Admit Term,
Statute of Limitations Term, Advisor,
Last Term Registered, Total Credits
The Solution - Using Query Tool
• To get the 13 fields requires linking to
9 tables.
•Logic to find the most recent
program/plan that has not been
discontinued is too complicated for
query tool
Criteria
Generated SQL
The Problem
• Too many PeopleSoft tables and
users don’t know table structure
• Need to join many tables to get data
• Queries run slow against tables
because of joins and security tree
What Are We Going to DO???
Write
Queries?
Buy a
reporting
product?
Write
SQRs?
THE SOLUTION
A CUSTOM
UNIVERSITY AT ALBANY
DATA WAREHOUSE
13
The Solution –
Student Data Warehouse
• Primary goals
User Access
Answer many varied questions/needs
Real-time management/monitoring tool
Design and Implementation
• Design led by Institutional Research
• Met with departments and worked
with programmer to define the table
organization
• Defined the fields to be included
• Defined the population of students to
be included
• IR tested
The Solution –
Student Data Warehouse
•Data NOT included:
• Scores (ACT, SAT, high school &
transfer GPAs, GRE, etc)
• Student Billing data
• Financial Aid
• Racial/ethnic data
• Disability information
• Student’s SSN
The Issues
● What students to include?
● How many semesters to include?
• How to define registered?
• How to handle addresses?
The Issues
What students to include?
Any student who was registered or got
a grade (even a W or Z) is included.
In addition, all active (eligible to
register) students are included.
The Issues
What semesters to include?
● Current
● Future (for Advance Registration)
● Past (comparative purposes)
The Issues
How to Define Registered?
a)Registered Flag showing that a
student was registered at some
point in the semester but
subsequently withdrew (with a
grade) from all courses
b)Presently Registered Flag indicates
that the student is currently (as of
the day before) registered
The Issues
How to Handle Addresses?
Developed concept of Priority Address
-- the student’s ‘closest’ address
If a student lives on campus, that is
their Priority Address; if not on
campus, but locally, that is the Priority
Address; otherwise, their permanent
address is their Priority Address.
The Issues
Last Enrollment Term
Deceased Students
Managing Size of the Data Warehouse
FERPA
The Solution –
Student Data Warehouse
• Active students for past and future
semesters
•Academic program information
• Student and Course Enrollments
•Course information
The Solution –
A Student Data Warehouse
• data on majors (demographics,
academic performance, registered vs
not-registered, etc.)
•Demographic and academic
characteristics of students in classes
• Output to prepare labels
The Solution –
Student Data Warehouse
•Data from more than 60 PeopleSoft
tables are placed in seven tables
• Tables reside in the same instance as
our Production database
• Queries are quick – no performance or
response time issues
25
The Solution –
Student Data Warehouse
• Refreshed daily based on parameters
• Parameters (terms to be included) can
be reset each day
• DW currently includes Fall 2003
through Fall 2006
26
Queries
• Over 200 public queries written by IR
• 90 queries primarily for academic
departments
• Return data elements depending on
requirements
• Prompts for fields such as term,
department, plan, advisor, course
• Can link to other PeopleSoft tables
Queries
• Most academic departments have “run
only” query security
• Most changes to existing queries or new
queries are handled by IR
Documentation
• What is the Data Warehouse?
• Terms in the Data Warehouse
• Data Warehouse Translation Values
• Data Warehouse Public Queries Log
• Data Warehouse Data Dictionary
• Running a Query
• Downloading Query Results to a CSV Text File
• Printing labels using PeopleSoft Query
• Creating Pivot Tables in Microsoft Excel
Once it’s done, it’s not done
• Training
• Documentation
• User Evaluation
• New queries
• Support
• Tweaking
What Else?
• Added a table for degrees awarded to the Student
Data Warehouse in Summer 2004
● Graduate Admissions Data Warehouse
implemented in Summer 2004
• Undergraduate Admissions Data Warehouse
being implemented in January 2006
Student Demographics
PeopleSoft Tables
STDNT_CAR_TERM
PERSONAL_DATA
NAMES
CITIZENSHIP
TERM_TBL
STDNT_ENRL
ACAD_PROG
RESIDENCY_OFF
CITIZEN_PSSPRT
STDNT_GRPS_HIST
VISA_PMT_DATA
VISA_PERMIT_TBL
COUNTRY_TBL
STATE_NAMES_TBL
UASA_CSC_TBL
XLAT
Data Warehouse Table
UADW_STDNT_DEMO
16 Tables
Student Addresses
PeopleSoft Tables
STDNT_CAR_TERM
ACAD_PROG
PERSONAL_PHONE
PERSONAL_DATA
TERM_TBL
ADDRESSES
EMAIL_ADDRESSES
Data Warehouse Table
UADW_STDNT_ADDR
7 Tables
Student Academic Info
PeopleSoft Tables
STDNT_CAR_TERM
TERM_TBL
ADM_APPL_PROG
ACAD_STDNT_ACTN
ACAD_PROG
ACAD_PLAN
ACAD_SUBPLAN
ADM_APPL_DATA
STDNT_ENRL
ENRL_REQ_DETAIL
ACAD_PROG_TBL
STDNT_ADVR_HIST
UASR_AVN_TBL
ACAD_PLAN_TBL
XLAT
UASR_STDNT_STAT
EXT_ORG_TBL
EXT_DEGREE
ACAD_PROG_OWNER
ACAD_PLAN_OWNER
NAMES
Data Warehouse Table
UADW_STDNT_ACAD
21 Tables
Student Attributes
PeopleSoft Tables
STDNT_CAR_TERM
ACAD_PROG
STDNT_ATTR_DTL
TERM_TBL
Data Warehouse Table
UADW_STDNT_ATTR
4 Tables
Student Groups
PeopleSoft Tables
STDNT_CAR_TERM
ACAD_PROG
STDNT_GRPS_HIST
TERM_TBL
Data Warehouse Table
UADW_STDNT_GRPS
4 Tables
Student Enrollments
PeopleSoft Tables
STDNT_CAR_TERM
ACAD_PROG
TERM_TBL
STDNT_ENRL
ENRL_REQ_DETAIL
CLASS_TBL
Data Warehouse Table
UADW_STDNT_ENRL
6 Tables
Course Characteristics
PeopleSoft Tables
CLASS_TBL
CRSE_OFFER
CLASS_INSTR
CRSE_ATTRIBUTES
NAMES
PERS_NID
TERM_TBL
CRSE_CATALOG
Data Warehouse Table
UADW_COURSE
8 Tables
Statistics
UADW_STDNT_DEMO
UADW_STDNT_ADDR
UADW_STDNT_ACAD
UADW_STDNT_ENRL
UADW_COURSE
* As of September 2005
50,802
50,802
227,843
366,789
39,292
But what about
??
• Official ‘third week’ data
• Financial Aid data
• Faculty/Staff data
40
QUESTIONS?
Contacts
Marybeth Salmon
Director, University Applications Development
University at Albany, State University of New York
E-mail: [email protected]
Shahnaz Sadeghi
Technical Lead – Data Warehouse
PeopleSoft Administrator
University at Albany, State University of New York
E-mail: [email protected]
Wendell Lorang
Director of Institutional Research
University at Albany, State University of New York
E-mail: [email protected]