Data Warehouse Implementation: Where We Are One Year Later

1
Data Warehouse Implementation Where We Are One
Year Later

• SouthEast SAS Users Group
• Atlanta, GA
• October 10, 2006 - Session AP12

2
Presenters

• Linda Sullivan
• Associate Director
• Evangeline (Angel) Collado
• Coordinator of Computer Applications /
• SAS Developer

3
Overview

• Over the past year, the university data warehouse
  project has added an additional 10 years worth of
  admissions data to the warehouse.
• The project team was expanded with the addition
  of 3 staff (2 developers and 1 functional project
  lead) and a project stakeholder group
  representing a cross-section of university users
  was established.
• A major focus of the development effort has been
  in data cleansing and validation, conversion of 8
  years worth of legacy student data to current
  student information system values, and
  identifying and populating missing values.
• SAS Business Intelligence tools have been used
  by both developers and power users for new
  project development and to convert existing SAS
  IntrNet programs to BI tools and warehouse data.

4
University of Central Florida (UCF)

• Metropolitan Research University
• First classes 1968
• Fall 2006 enrollment 47,200
• 10 colleges
• 5 regions with 16 distinct campus locations

5
Agenda / Contents

• Data cleansing and validation
• Conversion of existing SAS applications
• Admissions Profile
• Enrollment Profile
• Retention and Progression Profile
• SAS BI tools and power users
• Future project development plans

6
Data Cleansing and Validation

• Merged official state-required historical census
  data with additional elements from student
  information system
• SQL queries to extract data and load to files
• Mapping tables and formats to create new fields
• Data from 27 academic terms pushed through this
  process
• Data validation required to identify
  missing/incorrect values
• Frequency counts and summary tables used
• Identified and corrected invalid, mismatched,
  duplicate values
• Over 7 million rows of data reviewed and
  validated

7
Conversion of Existing SAS Applications

• Admissions Profile
• Enrollment Profile
• Retention and Progression Profile

8
Admissions Profile

• Web delivery via SAS/IntrNet Application
  Dispatcher
• New student application, acceptance, and
  enrollment statistics
• Tables, charts, and maps
• Counts, percentages, average scores, diversity,
  and geographic representation
• Trends and yields
• Term and annual data

9
Admissions Profile - Old

• Maintenance requirements
• Data update
• Multiple step process
• Various database formats
• Many large programs
• Application update
• Required program changes
• Web navigation
• Numerous choices
• Web forms

10
Admissions Profile - Old
11
Admissions Profile - Old
12
Admissions Profile - Old
13
Admissions Profile - New

• Easier application development
• Guided stored process creation
• Parameters from macro variables
• Simple modifications to generated code
• Concise presentation
• Multiple statistics on same page
• Charts and tables drillable
• Data maintenance
• Scheduled job to update each term

14
Admissions Profile - New
15
Admissions Profile - New
16
Admissions Profile - New
17
Admissions Profile - New
18
Enrollment Profile

• Respond to daily enrollment questions during key
  times
• Web delivery via SAS/IntrNet software technology
• User-friendly, dynamic and interactive
• Headcount numbers and percentages
• Multiple views
• College and major
• Undergraduate/graduate and classification
• Gender and ethnicity
• Full-time/part-time and residency status
• Replaced and enhanced numerous hard copy reports

19
Enrollment Profile - Old

• Maintenance requirements
• Data update
• Multiple step process
• Data for trends created separately
• Application update
• More than 30 programs to maintain
• JavaScript, HTML, and SAS
• Web navigation
• Dynamic links
• Menu structure

20
Enrollment Profile - Old
21
Enrollment Profile - Old
22
Enrollment Profile - Old
23
Enrollment Profile - Old
24
Enrollment Profile - New

• On Line Analytical Processing (OLAP)
• Provide pre-summarized dimensional data
• Meets UCFs business reporting requirements
• User-friendly, drill-down environment
• Very fast response times
• SAS OLAP Cube Studio
• Dimensions, levels, hierarchies
• Graphical interface
• BI Client Tools

25
Enrollment Profile - New
26
Enrollment Profile - New
27
Enrollment Profile - New
28
Retention and Progression Profile

• Track students each successive fall term
• Cumbersome hard copy reports
• Manual data entry for electronic format
• Networked database
• Web delivery via SAS IntrNet technology
• Available detailed reports
• Most recent cohort years
• Specific cohort year

29
Retention and Progression - Old

• Maintenance requirements
• Data update
• Numerous steps necessary
• Various database software
• Application update
• Required program changes
• HTML coding updates
• Many web pages to navigate
• PDF or Microsoft Excel reports

30
Retention and Progression - Old
31
Retention and Progression - Old
32
Retention and Progression - Old
33
Regression and Progression - New

• All cohort data in one warehouse table
• Existing programs easily modified
• Libname statements changed
• Code added to serve PDF file
• ProcessBody
• data _null_
• rc stpsrv_header('Content-type','application/
  pdf')
• rc stpsrv_header('Content-disposition','attac
  hment filenametemp.pdf')
• run
• Stored process created in SAS Enterprise Guide
• Same PDF or Excel output

34
Regression and Progression - New
35
Regression and Progression - New
36
Regression and Progression - New
37
Power Users SAS BI Tools

• Easy to use
• No programming skills needed
• Enable user community to independently create
  reports
• Customizable generated code
• Example
• Convert database from MS Access
• Information map for required data elements
• Web report for display

38
Power Users SAS BI Tools
39
Power Users SAS BI Tools
40
Future Project Development Plans

• Single Sign-on access through UCF portal to SAS
  Information Portal
• Expand beyond a census structure
• Finance Accounting and Human Resources data
• Faculty Activity Reporting system
• Institutional survey data
• Integrate with our Reporting Database Service
  data mart for trend analysis

41
Questions??
42
How to Contact Us

• Office of Institutional Research
• Email iroffice_at_mail.ucf.edu
• Web http//www.iroffice.ucf.edu
• 12424 Research Parkway, Suite 215
• Phone [phone] Fax [phone]
• Angel Collado ecollado_at_mail.ucf.edu
• Linda Sullivan lindas_at_mail.ucf.edu