Snakepit or Shangri-La?

1. 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

2. 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

3. 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

4. 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!

5. 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

6. 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)

7. Convening the Right People • Steering Committee consisted of a core group of IT staff, registrars, and IR staff • Total of 10 to 12 members

8. 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

9. 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

10. 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

11. … 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

12. 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

13. Changing Business Practices • Data from multiple areas requires good communication and strong inter-departmental relationships • Ex., IPEDS ethnic • Still in progress – items not tracked in ISIS • 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

14. 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)

15. 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

16. Which model do I choose?

17. 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

18. “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

19. Sample Reports

21. 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 on-going projects in addition to the Student DW • Considering hiring an administrator fully devoted to the DW to help with ongoing upgrades, enhancements, and training

22. 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?

23. Our “Wish List”

24. 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 In Conclusion: Takeaway Points

25. So… Snakepit or Shangri-La?

26. 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?

27. It’s still a work in progress, but so far the benefits definitely outweigh the costs!

28. Thank you! For more information, please contactJessica atJessica.Foster@rochester.eduor Kathleen atKathleen.Moore@rochester.eduPowerPoint presentation available atwww.rochester.edu/provost/ir/