1 / 42

Data Warehousing at The University at Albany

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.

sandra_john
Download Presentation

Data Warehousing at The University at Albany

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 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

  2. 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.

  3. Presentation Our PeopleSoft Implementation Problem Description The Solution The Issues

  4. PeopleSoft at UAlbany 4

  5. PeopleSoft at UAlbany 5

  6. The Problem – A Common Question “Can I get a list of all of the grad students in my department who are currently registered?”

  7. 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

  8. 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

  9. Criteria

  10. Generated SQL

  11. 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

  12. What Are We Going to DO??? Buy a reporting product? Write SQRs? Write Queries?

  13. THE SOLUTION A CUSTOM UNIVERSITY AT ALBANY DATA WAREHOUSE

  14. The Solution – Student Data Warehouse • Primary goals User Access Answer many varied questions/needs Real-time management/monitoring tool

  15. 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

  16. 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

  17. The Issues ● What students to include? ● How many semesters to include? • How to define registered? • How to handle addresses?

  18. 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.

  19. The Issues What semesters to include? ● Current ● Future (for Advance Registration) ● Past (comparative purposes)

  20. 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

  21. 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.

  22. The Issues Last Enrollment Term Deceased Students Managing Size of the Data Warehouse FERPA

  23. The Solution – Student Data Warehouse • Active students for past and future semesters • Academic program information • Student and Course Enrollments • Course information

  24. 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

  25. 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

  26. 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

  27. 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

  28. Queries Most academic departments have “run only” query security Most changes to existing queries or new queries are handled by IR

  29. 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

  30. Once it’s done, it’s not done • Training • Documentation • User Evaluation • New queries • Support • Tweaking

  31. 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

  32. 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

  33. 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

  34. 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

  35. Student Attributes PeopleSoft Tables Data Warehouse Table STDNT_CAR_TERM UADW_STDNT_ATTR ACAD_PROG STDNT_ATTR_DTL TERM_TBL 4 Tables

  36. Student Groups PeopleSoft Tables Data Warehouse Table STDNT_CAR_TERM UADW_STDNT_GRPS ACAD_PROG STDNT_GRPS_HIST TERM_TBL 4 Tables

  37. 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

  38. 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

  39. 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

  40. But what about ?? • Official ‘third week’ data • Financial Aid data • Faculty/Staff data

  41. QUESTIONS?

  42. 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

More Related