data warehousing at the university at albany l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Data Warehousing at The University at Albany PowerPoint Presentation
Download Presentation
Data Warehousing at The University at Albany

Loading in 2 Seconds...

play fullscreen
1 / 42

Data Warehousing at The University at Albany - PowerPoint PPT Presentation


  • 441 Views
  • Uploaded on

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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Data Warehousing at The University at Albany' - sandra_john


Download Now 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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
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

slide2

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.

slide3

Presentation

Our PeopleSoft Implementation

Problem Description

The Solution

The Issues

slide6

The Problem – A Common Question

“Can I get a list of all of the grad students in my department who are currently registered?”

slide7

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

slide8

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
slide11

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
slide12

What Are We Going to DO???

Buy a reporting product?

Write SQRs?

Write Queries?

the solution
THE SOLUTION

A CUSTOM

UNIVERSITY AT ALBANY

DATA WAREHOUSE

slide14

The Solution –

Student Data Warehouse

  • Primary goals

User Access

Answer many varied questions/needs

Real-time management/monitoring tool

slide15

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

slide16

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
slide17

The Issues

● What students to include?

● How many semesters to include?

  • How to define registered?
  • How to handle addresses?
slide18

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.

slide19

The Issues

What semesters to include?

● Current

● Future (for Advance Registration)

● Past (comparative purposes)

slide20

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
slide21

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.

slide22

The Issues

Last Enrollment Term

Deceased Students

Managing Size of the Data Warehouse

FERPA

slide23

The Solution –

Student Data Warehouse

  • Active students for past and future semesters
  • Academic program information
  • Student and Course Enrollments
  • Course information
slide24

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
slide25

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
slide26

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
slide27

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

slide28

Queries

Most academic departments have “run only” query security

Most changes to existing queries or new queries are handled by IR

slide29

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

slide30

Once it’s done, it’s not done

  • Training
  • Documentation
  • User Evaluation
  • New queries
  • Support
  • Tweaking
slide31

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

slide32

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

slide33

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

slide34

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

slide35

Student Attributes

PeopleSoft Tables

Data Warehouse Table

STDNT_CAR_TERM

UADW_STDNT_ATTR

ACAD_PROG

STDNT_ATTR_DTL

TERM_TBL

4 Tables

slide36

Student Groups

PeopleSoft Tables

Data Warehouse Table

STDNT_CAR_TERM

UADW_STDNT_GRPS

ACAD_PROG

STDNT_GRPS_HIST

TERM_TBL

4 Tables

slide37

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

slide38

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

slide39

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
But what about ??
  • Official ‘third week’ data
  • Financial Aid data
  • Faculty/Staff data
slide42

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