just do it! building a student data warehouse at the
TRANSCRIPT
![Page 1: Just Do It! Building a Student Data Warehouse at the](https://reader031.vdocuments.us/reader031/viewer/2022012508/618567934836c94e0465b6ce/html5/thumbnails/1.jpg)
Just Do It! Building a Student Data Warehouse
at the University of Houston
Presented at SAIR 2015 by Dr. Susan Moreno and Vyas Krishnamurthy
Office of Institutional Research
![Page 2: Just Do It! Building a Student Data Warehouse at the](https://reader031.vdocuments.us/reader031/viewer/2022012508/618567934836c94e0465b6ce/html5/thumbnails/2.jpg)
University of Houston
• 42,704 students• 13 colleges• 111 bachelors, 121 masters, 47 doctoral, and 3 professional programs
• 9,160 degrees awarded annually
![Page 3: Just Do It! Building a Student Data Warehouse at the](https://reader031.vdocuments.us/reader031/viewer/2022012508/618567934836c94e0465b6ce/html5/thumbnails/3.jpg)
Outline• Why Data Warehouse?• Decisions for DW Implementation• Model Schema• Type of Data and Tables• Data Validation Process• User Access• Documentation and Training• Reports Demonstration
![Page 4: Just Do It! Building a Student Data Warehouse at the](https://reader031.vdocuments.us/reader031/viewer/2022012508/618567934836c94e0465b6ce/html5/thumbnails/4.jpg)
Why a Data Warehouse?• UH Finance data first in 2007 then student data in 2009
• Longitudinal outlook and analysis of student admissions and enrollment data
• One source of data for the truth• Easily accessible historical trends of data
![Page 5: Just Do It! Building a Student Data Warehouse at the](https://reader031.vdocuments.us/reader031/viewer/2022012508/618567934836c94e0465b6ce/html5/thumbnails/5.jpg)
Why Data Warehouse?• Options for data view or query:
‐ IR Statistical Handbook‐ PeopleSoft Query Tool‐ Reporting Tables‐ Dashboard
• Collaboration between Institutional Research and the Data Warehouse team in IT
![Page 6: Just Do It! Building a Student Data Warehouse at the](https://reader031.vdocuments.us/reader031/viewer/2022012508/618567934836c94e0465b6ce/html5/thumbnails/6.jpg)
Decisions for DW Implementation• Data and reporting needs of the business user• Source Data: PeopleSoft, CB Reports, Derived fields
• Time period of data in the DW• Choice of schema model
![Page 7: Just Do It! Building a Student Data Warehouse at the](https://reader031.vdocuments.us/reader031/viewer/2022012508/618567934836c94e0465b6ce/html5/thumbnails/7.jpg)
Decisions for DW Implementation• Frequency of data capture each semester ‐snapshots/key dates
• Capture changes in major/department hierarchy for trending
• Accessing the DW tables by users• Data for UH Main campus only
![Page 8: Just Do It! Building a Student Data Warehouse at the](https://reader031.vdocuments.us/reader031/viewer/2022012508/618567934836c94e0465b6ce/html5/thumbnails/8.jpg)
User Access• Data Warehouse is license restricted• Shared between Student, Finance and Research DW users
• Each college is given two licenses
• Power user ‐ Hyperion Interactive Reporting Studio tool should be installed on user’s machine
![Page 9: Just Do It! Building a Student Data Warehouse at the](https://reader031.vdocuments.us/reader031/viewer/2022012508/618567934836c94e0465b6ce/html5/thumbnails/9.jpg)
Model Schema• The initial proposed design was star schema using facts and dimensions
• The current schema is a normalized model • This minimized redundancy and easy to maintain
• Key fields in tables
![Page 10: Just Do It! Building a Student Data Warehouse at the](https://reader031.vdocuments.us/reader031/viewer/2022012508/618567934836c94e0465b6ce/html5/thumbnails/10.jpg)
Snapshots• Pulled on key dates in a semester• Enrollment and admission tables have the snapshot date
• Enrollment tables 9 snapshots • Admission snapshots are weekly
![Page 11: Just Do It! Building a Student Data Warehouse at the](https://reader031.vdocuments.us/reader031/viewer/2022012508/618567934836c94e0465b6ce/html5/thumbnails/11.jpg)
Enrollment Tables Snapshots• Fall 2014 Semester
Snapshot Period Description Capture Date
PFC ONE WEEK PRIOR FIRST CLASS DAY 8/18/2014FCD FIRST CLASS DAY 8/25/2014CEN CENSUS 9/10/2014TCD TWENTIETH CLASS DAY 9/25/2014LDD LAST DROP DATE 10/31/2014TED TERM END DT 12/18/2014GD1 GRADE DT 1 1/8/2015GD2 GRADE DT 2 2/7/2015FGD FINAL GRADE DT 3/22/2015
![Page 12: Just Do It! Building a Student Data Warehouse at the](https://reader031.vdocuments.us/reader031/viewer/2022012508/618567934836c94e0465b6ce/html5/thumbnails/12.jpg)
Admissions Tables Snapshots• Fall 2014 Admissions
Snapshot Period Description Capture Date
WK1 WEEK 1 9/8/2013
WK10 WEEK 10 11/10/2013
WK20 WEEK 20 1/19/2014
WK30 WEEK 30 3/30/2014
WK40 WEEK 40 6/8/2014
WK50 WEEK 50 8/17/2014
WK57 WEEK 57 10/5/2014
![Page 13: Just Do It! Building a Student Data Warehouse at the](https://reader031.vdocuments.us/reader031/viewer/2022012508/618567934836c94e0465b6ce/html5/thumbnails/13.jpg)
Data Warehouse Tables• CB tables: Data from Fall 2007 to present • Setup tables: Facilitates DW functionality• Enrollment tables: Data from Summer 2000 to present
• Admissions tables: Data from Fall 2010 to present
• Matrix tables: Maps latest organizational hierarchy
• Retention Tables: Retention & Graduation data for specific cohorts
![Page 14: Just Do It! Building a Student Data Warehouse at the](https://reader031.vdocuments.us/reader031/viewer/2022012508/618567934836c94e0465b6ce/html5/thumbnails/14.jpg)
![Page 15: Just Do It! Building a Student Data Warehouse at the](https://reader031.vdocuments.us/reader031/viewer/2022012508/618567934836c94e0465b6ce/html5/thumbnails/15.jpg)
Data Validation Process• Compare DW vs IR office data• Frequency counts of different fields and look at differences
• CB tables ‐ counts should have exact match• Enrollment and Admissions tables ‐ close match
• If large discrepancy, understand the difference
![Page 16: Just Do It! Building a Student Data Warehouse at the](https://reader031.vdocuments.us/reader031/viewer/2022012508/618567934836c94e0465b6ce/html5/thumbnails/16.jpg)
Documentation & Power User Training
• Hyperion Interactive Reporting Studio tool• Data Warehouse tables• Examples for users to work• Work Sessions
![Page 17: Just Do It! Building a Student Data Warehouse at the](https://reader031.vdocuments.us/reader031/viewer/2022012508/618567934836c94e0465b6ce/html5/thumbnails/17.jpg)
Reports Demonstration
![Page 18: Just Do It! Building a Student Data Warehouse at the](https://reader031.vdocuments.us/reader031/viewer/2022012508/618567934836c94e0465b6ce/html5/thumbnails/18.jpg)
![Page 19: Just Do It! Building a Student Data Warehouse at the](https://reader031.vdocuments.us/reader031/viewer/2022012508/618567934836c94e0465b6ce/html5/thumbnails/19.jpg)
![Page 20: Just Do It! Building a Student Data Warehouse at the](https://reader031.vdocuments.us/reader031/viewer/2022012508/618567934836c94e0465b6ce/html5/thumbnails/20.jpg)
![Page 21: Just Do It! Building a Student Data Warehouse at the](https://reader031.vdocuments.us/reader031/viewer/2022012508/618567934836c94e0465b6ce/html5/thumbnails/21.jpg)
![Page 22: Just Do It! Building a Student Data Warehouse at the](https://reader031.vdocuments.us/reader031/viewer/2022012508/618567934836c94e0465b6ce/html5/thumbnails/22.jpg)
![Page 23: Just Do It! Building a Student Data Warehouse at the](https://reader031.vdocuments.us/reader031/viewer/2022012508/618567934836c94e0465b6ce/html5/thumbnails/23.jpg)
![Page 24: Just Do It! Building a Student Data Warehouse at the](https://reader031.vdocuments.us/reader031/viewer/2022012508/618567934836c94e0465b6ce/html5/thumbnails/24.jpg)
![Page 25: Just Do It! Building a Student Data Warehouse at the](https://reader031.vdocuments.us/reader031/viewer/2022012508/618567934836c94e0465b6ce/html5/thumbnails/25.jpg)
![Page 26: Just Do It! Building a Student Data Warehouse at the](https://reader031.vdocuments.us/reader031/viewer/2022012508/618567934836c94e0465b6ce/html5/thumbnails/26.jpg)
![Page 27: Just Do It! Building a Student Data Warehouse at the](https://reader031.vdocuments.us/reader031/viewer/2022012508/618567934836c94e0465b6ce/html5/thumbnails/27.jpg)
![Page 28: Just Do It! Building a Student Data Warehouse at the](https://reader031.vdocuments.us/reader031/viewer/2022012508/618567934836c94e0465b6ce/html5/thumbnails/28.jpg)
![Page 29: Just Do It! Building a Student Data Warehouse at the](https://reader031.vdocuments.us/reader031/viewer/2022012508/618567934836c94e0465b6ce/html5/thumbnails/29.jpg)
![Page 30: Just Do It! Building a Student Data Warehouse at the](https://reader031.vdocuments.us/reader031/viewer/2022012508/618567934836c94e0465b6ce/html5/thumbnails/30.jpg)
![Page 31: Just Do It! Building a Student Data Warehouse at the](https://reader031.vdocuments.us/reader031/viewer/2022012508/618567934836c94e0465b6ce/html5/thumbnails/31.jpg)
![Page 32: Just Do It! Building a Student Data Warehouse at the](https://reader031.vdocuments.us/reader031/viewer/2022012508/618567934836c94e0465b6ce/html5/thumbnails/32.jpg)
![Page 33: Just Do It! Building a Student Data Warehouse at the](https://reader031.vdocuments.us/reader031/viewer/2022012508/618567934836c94e0465b6ce/html5/thumbnails/33.jpg)
![Page 34: Just Do It! Building a Student Data Warehouse at the](https://reader031.vdocuments.us/reader031/viewer/2022012508/618567934836c94e0465b6ce/html5/thumbnails/34.jpg)
![Page 35: Just Do It! Building a Student Data Warehouse at the](https://reader031.vdocuments.us/reader031/viewer/2022012508/618567934836c94e0465b6ce/html5/thumbnails/35.jpg)
![Page 36: Just Do It! Building a Student Data Warehouse at the](https://reader031.vdocuments.us/reader031/viewer/2022012508/618567934836c94e0465b6ce/html5/thumbnails/36.jpg)
![Page 37: Just Do It! Building a Student Data Warehouse at the](https://reader031.vdocuments.us/reader031/viewer/2022012508/618567934836c94e0465b6ce/html5/thumbnails/37.jpg)
![Page 38: Just Do It! Building a Student Data Warehouse at the](https://reader031.vdocuments.us/reader031/viewer/2022012508/618567934836c94e0465b6ce/html5/thumbnails/38.jpg)
![Page 39: Just Do It! Building a Student Data Warehouse at the](https://reader031.vdocuments.us/reader031/viewer/2022012508/618567934836c94e0465b6ce/html5/thumbnails/39.jpg)
Future• Modules to add data: Financial Aid, Student Financials
• Building Reports: Admissions, Financial Aid, Honors College
• On‐going data improvements• Access DW tables by SAS, SPSS