420 likes | 927 Views
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.
E N D
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
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
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??? Buy a reporting product? Write SQRs? Write Queries?
THE SOLUTION A CUSTOM UNIVERSITY AT ALBANY DATA WAREHOUSE
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? • Registered Flag showing that a student was registered at some point in the semester but subsequently withdrew (with a grade) from all courses • 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
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
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 Data Warehouse Table STDNT_CAR_TERM PERSONAL_DATA NAMES CITIZENSHIP TERM_TBL STDNT_ENRL ACAD_PROG UADW_STDNT_DEMO RESIDENCY_OFF CITIZEN_PSSPRT STDNT_GRPS_HIST VISA_PMT_DATA VISA_PERMIT_TBL 16 Tables COUNTRY_TBL STATE_NAMES_TBL UASA_CSC_TBL XLAT
Student Addresses PeopleSoft Tables Data Warehouse Table STDNT_CAR_TERM ACAD_PROG PERSONAL_PHONE UADW_STDNT_ADDR PERSONAL_DATA TERM_TBL ADDRESSES EMAIL_ADDRESSES 7 Tables
Student Academic Info PeopleSoft Tables Data Warehouse Table STDNT_CAR_TERM TERM_TBL ADM_APPL_PROG ACAD_STDNT_ACTN ACAD_PROG ACAD_PLAN ACAD_SUBPLAN UADW_STDNT_ACAD ADM_APPL_DATA STDNT_ENRL ENRL_REQ_DETAIL ACAD_PROG_TBL STDNT_ADVR_HIST 21 Tables UASR_AVN_TBL ACAD_PLAN_TBL XLAT UASR_STDNT_STAT EXT_ORG_TBL EXT_DEGREE ACAD_PROG_OWNER ACAD_PLAN_OWNER NAMES
Student Attributes PeopleSoft Tables Data Warehouse Table STDNT_CAR_TERM UADW_STDNT_ATTR ACAD_PROG STDNT_ATTR_DTL TERM_TBL 4 Tables
Student Groups PeopleSoft Tables Data Warehouse Table STDNT_CAR_TERM UADW_STDNT_GRPS ACAD_PROG STDNT_GRPS_HIST TERM_TBL 4 Tables
Student Enrollments PeopleSoft Tables Data Warehouse Table STDNT_CAR_TERM ACAD_PROG UADW_STDNT_ENRL TERM_TBL STDNT_ENRL ENRL_REQ_DETAIL CLASS_TBL 6 Tables
Course Characteristics PeopleSoft Tables Data Warehouse Table CLASS_TBL CRSE_OFFER CLASS_INSTR UADW_COURSE CRSE_ATTRIBUTES NAMES PERS_NID TERM_TBL CRSE_CATALOG 8 Tables
Statistics UADW_STDNT_DEMO 50,802 UADW_STDNT_ADDR 50,802 UADW_STDNT_ACAD 227,843 UADW_STDNT_ENRL 366,789 UADW_COURSE 39,292 * As of September 2005
But what about ?? • Official ‘third week’ data • Financial Aid data • Faculty/Staff data
Contacts Marybeth SalmonDirector, University Applications Development University at Albany, State University of New YorkE-mail: msalmon@uamail.albany.edu Shahnaz Sadeghi Technical Lead – Data Warehouse PeopleSoft Administrator University at Albany, State University of New York E-mail: ssadeghi@uamail.albany.edu Wendell LorangDirector of Institutional Research University at Albany, State University of New YorkE-mail: wlorang@uamail.albany.edu