Transcript Snakepit or Shangri-La? Issues and Potential Pitfalls in

Snakepit or Shangri-La?
Issues and Potential Pitfalls in
Implementing a Student Data Warehouse
Kathleen Moore, Jessica Foster, Nancy Speck, Carl Dickinson
University of Rochester
AIR Forum 2007 - Kansas City, MO

Presenters:



Kathleen Moore, Assistant Provost
Jessica Foster, Assistant Provost for Institutional Research
Co-authors:


Nancy Speck, Assistant Dean for Institutional Research
and Registrar
Carl Dickinson, Associate Registrar
2
About the University of Rochester

Doctoral Research-Extensive institution located in upstate
NY

6 schools: Arts, Sciences, & Engineering; Music;
Business; Education; Medicine & Dentistry; and Nursing

Fall 2006 Undergrads: 4904, Graduates: 3539, MD’s: 403

Decentralized philosophy

Some centralized administration exists
3
Why Was a Data Warehouse Needed?

Current student data system (ISIS) – homegrown “legacy“
system, 20 yrs old, no plans to replace for 3-5 yrs

Process for retrieving data from the ISIS system:



Natural programs are run by the Registrar’s office to create SAS
extracts
IR then writes mainframe SAS programs to retrieve data from
extract files
. . . FOR ANY REPORT!!!!

Time to get data for simple questions takes hours; more
complex projects can take weeks

The idea of a data warehouse seemed like a dream come
true!
4
Getting Buy-In and Finding Funding





CIO and University Registrar were instrumental in getting
the pilot underway
To implement the student DW project and other projects, a
Director of Administrative Computing was hired
In initial stages, funding the development of the DW was a
serious issue
Central IT ultimately provided all funding for the pilot (over
$200K to date)
Funding for continued development is under discussion
5
Choosing a System


Decision-drivers:

Web-based user interface

Sufficient flexibility for a complex institution

Related systems already in use or planned

Cost

Ease of use
Final choice: COGNOS 8 (with Oracle database)
6
Convening the Right People


Steering Committee consisted of a core group of IT
staff, registrars, and IR staff
Total of 10 to 12 members
7
Arcane Architecture, Confusing Acronyms



Developing the warehouse
 Different ways of thinking: IT vs. End-User
 Limitations of current system posed problems with fitting
some data into a DW hierarchy
 In the end, no real data hierarchy was adopted
Nomenclature of warehouse elements was confusing
 Variable names not always the same as in ISIS
 Organization of the warehouse into Models, Dimensions,
Facts, and Attributes is very different
Many lengthy meetings!
 Have to be willing to invest time
 Meeting weekly at a set time made it possible to meet
project deadlines
8
Guerilla Databases



Schools have created their own databases outside of ISIS
Transferring information from these auxiliary databases to
the warehouse is an on-going issue

Office of Special Programs

Study Abroad

CIP and NY State codes
Interest in the data warehouse is promoting use of one
central reporting system
9
Testing, Testing…


Testing environment confusing!

Different web addresses

Changes in the COGNOS tool due to upgrades

Moving reports from one environment to another
Finding time to test was a big issue (still is!)



Core user group tried to do this on their own
Weekly group testing meetings in a computer lab
with IT staff available
Commitment to making this a success was our
motivator
10
… and More Testing



Slow response time during group testing was
frustrating (the dreaded “spinning box/hour glass”)
Verification process was hampered because data was initially
refreshed only once a week
Testing enlightened users and programmers to ISIS
limitations, processing quirks, and the need to make changes
to daily business practices


Going through this process will eventually make it easier to transition
to a new student system
Originally only tested data from 5 most recent terms to make
sure everything worked – eventually loaded ~20 years worth
of data
11
Data Cleanup


Testing revealed a number of problems with how ISIS
data was stored

Student addresses

Degree conferral and award dates
Data entry cleanup – making sure data were initially
entered and coded consistently in ISIS

Take Five students

McNair students
12
Changing Business Practices

Data from multiple areas requires good communication
and strong inter-departmental relationships


Still in progress – items not tracked in ISIS



Ex., IPEDS ethnic
Remedial and developmental courses
Students in combined degree programs (3/2,
MD/PhD, etc.)
Each school defines a “registered student” differently
and uses variables differently
13
Training and Support

An on-going issue




Initially IT consultants trained core group; IT & core
group developed training documents for new users
Currently more demand for training than we have
time available to devote to it, which may be slowing
adoption of the DW by new users
Two types of training might be appropriate: IT
training on the tool and core group training on the
data
Identifying who will be responsible for training prior to
implementation is crucial (this was a shortcoming in our
project)
14
Training We Currently Offer




Two members of the core group offer brief presentations
followed by a hands-on session ~ 3 times per year
Monthly hands-on work sessions in computer lab where
volunteers from core group help new users with queries
Created a listserv for core group and current users to post
questions, concerns, etc.
A “decision tree” of what model to use is given to all users


Models can consist of either unduplicated or duplicated
records per student
Users need to fully define the question being asked in
order to pick the correct model
15
Student Data Warehouse
Which model do I choose?
Which model do I choose?
What kind of report do you
want?
Statistical data
(e.g. Hours
earned, GPA)
from end of term
or cumulative?
Each student
program
displayed
separately (use
this if creating a
report by
program)
Student Term
Statistics
Degree
information
Student Degree
One student
program line,
regardless of how
many programs
Student Term
Statistics
Unduplicated
Current
Student Hold
information
Student Counts/
Lists
Student Hold
Do you want a
report of all
students who have
a particular major?
(regardless of
major1,2,)
Student Counts/
Lists by major
Program Activity
changes
(e.g.major, minor,
time status)
within a term.
Student Program
Activity
Do you want a
report of all
students who have
a particular
program?
(regardless of
program1,2,)
Student Counts/
Lists by program
Is it a University
level (vs. college)
report where it is
important that the
student not be
counted twice if
they have multiple
programs?
Student Counts/
Lists unduplicated
Just want to look
at the values in
the lookup tables.
Student Table
Lookups
Only most
recent
registration
information for
the term.
Multiple records
for duplicate
programs.
Student Course
Registration
Course
registration
information
(e.g.add, drop,
grades)
Only most
recent
registration
information for
the term. (Use
this one most of
the time)
Student Course
Registration
Unduplicated
Registration
changes within a
term are required.
(1 or more
actions for a
term)
Student
Registration
Activity
16
A New Way of Thinking

New users initially experience a lot of frustration




Different naming conventions for DW fields from names
they’re familiar with in ISIS
The way data fields are grouped into models involves an
entirely new way of thinking
DW users aren’t always ISIS users, so ISIS documentation
is also available in the DW for structural reference and logic
New DW users need to break out of their “this is how we’ve
always done it” mindset
17
“Hey, this thing is cool – and it
really works!”




Ad hoc reporting is MUCH easier
Makes longitudinal analysis much more manageable – can
easily report across years without merging extract files
Cool projects completed using the DW:

National Research Council study on PhD degree completion

Tuition transfer study

Credit hour analysis

Classroom scheduling

Music school instrument groupings
Currently looking into using DW to create our Fact Book
18
Sample
Reports
19
20
Reality vs. Wish List: Reality




Initially our DW was a PILOT project – we now need to
secure more funding, IT resources, etc. to continue
development
Finding some areas need a different structure, more data
fields, historical data, or custom/calculated fields
Our IT development team is now committed to other ongoing projects in addition to the Student DW
Considering hiring an administrator fully devoted to the
DW to help with ongoing upgrades, enhancements, and
training
21
Reality vs. Wish List: Wish List


The core team has created a “wish list” prioritized by level
of importance (currently has about 20 items):
 Things we wish we’d thought of earlier:
 Ex., General Subject Area field (Humanities, Social
Sciences, etc.)
 Who is a “registered student?”
 Things we want for the “second round” of the project:
 Ex., Financial aid dimension
 Things that just need to be changed or fixed:
 Ex., Grade value should be a fact and not an attribute
Usage of SSN – who can access this? Can it be included in
DW but “hidden” from most users?
22
Our “Wish List”
23
In Conclusion: Takeaway Points
Things we think we did well:
 Selected the right core
group
 Chose a software platform
that fits our university’s
needs
 Core user group worked
well with IT
 Hired an independent
consultant who was
familiar with DW structure
and COGNOS tool to
review our business
practices
Where we missed the boat:
 One key person added too late to
the project – missed perspective
 More explicit communication
between IT and core users – user
group assumed IT fully understood
our practices and needs at a very
detailed level
 IT needed to educate core users
more on the role of models in the
DW structure
 Planning in advance for future
restructuring of some models
 Needed better planning for user
training upfront
24
So… Snakepit or Shangri-La?
25

85-90% of Rochester’s schools have now adopted the
DW





The more we demonstrate it, the more people get
interested
Has increased communication among offices which
typically don’t work together
Forced us to review data policies and business practices
Has served as important preparation for adopting a new
central student information system in a few years
Conclusion?
26
It’s still a work in progress, but so far the
benefits definitely outweigh the costs!
27
Thank you!
For more information, please contact
Jessica at [email protected]
or Kathleen at [email protected]
PowerPoint presentation available at
www.rochester.edu/provost/ir/