Transcript Data Warehousing at Notre Dame

Data Warehousing at
Notre Dame
October 7, 2004
Dale Carter, Manager, Decision Support
Jared Barnard, Database Administrator
Agenda







Context
Our legacy data warehouse
Lessons learned
Challenges and demands of the new ERP
(Enterprise Resource Planning)
New data warehouse, sourced from ERP
Expected benefits
Questions
Context



Limited DW effort from 1998 – 2002
Limited use of BI (Business Intelligence) tool, Business Objects
Historically

End-users downloaded from HP3000



Used Excel, MSAccess
Centralized IT ad hoc reporting
First steps




Nightly extract of HP3000 data into Oracle (ODS)
Built UGA and HR datamarts
Power users used MSAccess and Business Objects to access data
Operational type reporting
Benefits of ODS
(Operational Data Store)
to the Registrar Office




Ability to monitor student/course
corporate data  data integrity
Ability to query data and retrieve results
quickly
Ease of gathering data not just from
student database but across other DBs
User friendly reporting capabilities,
facilitating increased report writing
Benefits of HR Datamart to
Human Resources

The datamart and Business Objects tool
enables





Reporting of pay histories for an individual
Monitoring of completion of Performance
Review Summaries by departments
Providing detailed termination information
Tracking of benefit plan membership
Pulling information together for ND
Auditing Department requests
Benefits of Course Datamart





Had been a long-time request from
deans to Dr. Pace
Student count by college/building
Quick refresh
Crosstab and chart rendering
Show example in Business Objects
Lessons learned


Underestimated amount of effort
Stakeholders expected “quick”




Development of datamart
Development of Bus.Objects universe layer
Development of reports
Stakeholders surprised by



Time savings
Value of the Bus.Objects universe layer
Robust functionality of Bus.Objects
Reporting:
Business Intelligence Evolution

Copyrighted slide
Banner Information Access
Strategy

Copyrighted slide
Structure of the EDW

Star schema – data model technique


Different from relational model
Improve response time



Due to fewer needed joins
Measures are pre-aggregated
Two major components


Fact table – stores all amounts/counts
Dimension tables – descriptive attributes
Expected Benefits from
Course Registration Star Schema

Understand trends in course registration and
to plan for courses to meet student needs

Analyze credits generated, attempted, earned,
total number of students based on






Time (academic period)
Demographic data (ethnicity, gender)
Student information (residency, class level)
Course information (course level, department)
Enrollment status
Instructor
Expect Benefits from the
Financial Aid Pre-Student


To understand the trends in pre-student
acceptance and enrollment based on how
fin.aid amounts are allocated
Analyze # applicants who are aid recipients,
award amts offered, accepted, declined,
cancelled and paid



Pre-student info (inquired, applied, accepted,
enrolled)
Fin. Aid award status (offered, accepted, declined)
Fund information (fund, type, source)
Ongoing Challenges for Data
Warehousing Implementation

Roll-out of data warehousing requires
 Technical aspects
 Coordinate and implement frequent upgrades
 Ensure refresh schedule does not interfere with
ERP functions and scheduled reports
 Functional aspects
 Training of end-users on tool and data
structures
 Mentoring and work sessions for power
users/external report writers
 Addressing report distribution needs
Ongoing Challenges for Data
Warehousing Implementation



Address team needs (project matrix
format…long hours)
Obtain support and direction for data
warehousing effort
 More involvement from IT director & core
team sponsor
 BI governance committee
Roll-out dashboard to university
executives….see vendor example
Questions