Enterprise Data Warehouse Design for Student Reporting and IPEDS Compliance
Founded in 1919, this well-regarded private university has an enrollment of roughly 7,000 students, with current areas of study that include liberal arts, music, design and social sciences, as well as certificate programs.
As a primary provider of post-secondary education, this university submits annual, federally mandated student data to the National Center for Educational Statistics (NCES), a division within the U.S. Department of Education. For this purpose, NCES established IPEDS (Integrated Post-Secondary Education Data System) to help universities achieve compliance through filling out an interrelated series of IPEDS surveys. These queries are designed to track various trends relating to enrollment, including but not limited to the following: incoming freshmen students from their first year through graduation; full-time versus part time students; student race/ethnicity; tuition, fees and financial aid; graduation rates; number and types of degree completions; and financial/staff resources available, among other data.
The university’s challenge required developing and implementing a comprehensive new source data technology structure for maintaining, updating, and reporting on this student population data for its own use as well as the NCES. Their existing source data system was out of date, and didn’t drill down into points of data at a level granular enough to comply with IPEDS, or to their own satisfaction. Going beyond the required annual reporting via IPEDS, the school also wanted the capability to pull specific student data out and massage it manually, with the flexibility to track students from application through acceptance, following up all the way to graduation.
CBIG’s initial assessment found that client expectations initially were too complex, particularly in wanting to track everything from a Type 2 dimensional standpoint that marked each time details changed. This can be an expensive database operation, making a Type 2 dimensional data model a poor choice if data is continually subject to change. Sticking to this model undermined their previous improvement attempts, as students altered their own data frequently. The CBIG team worked with the university team, headed by their Data Warehouse Project Director, to help them evolve toward a more manageable, snapshot-in-time, fact-based-details system versus a highly complex dimensional approach. The solution became a whole student focused EDW encompassing Student Applications, Admissions, Enrollment, Financial Aid, Registration, Curriculum, and Degree Awards.
Much of CBIG’s higher-ed experience involved pulling data from Banner®, a student information software system for higher education—and the software this higher-ed school uses. For this Phase 1 project, CBIG’s team designed an Enterprise Data Warehouse (EDW) in which all the data sets would continue to originate from Banner®. CBIG then incorporated a new capability for users who wanted to manually manipulate the information, achieving much needed flexibility lacking in the old system. Since many users still felt there was a need to track certain data when it changed, CBIG implemented a few ideas in this first phase, and added more complex processes as necessity demanded. The goal was to keep it simple and easy to maintain. This design took one year to complete, and the first-phase solution was implemented in seven months, with follow-on modifications.
The university now has access to much more timely and accurate student data information with much improved IPEDS compliance. One of the biggest benefits for the school itself, however, is in its ability to now properly manage “cohorts”—the name they use to group students—when they first arrive and then track them relative to their peers throughout their tenure. This is new and highly useful functionality, providing an asterisk to a very successful design and implementation. The university users understand that their new EDW can’t do everything they need yet, as they continue to learn the potential of the system, and look forward to building on its capability in the second phase of this exciting project.
- Oracle 11.2
- Custom-written PL SqL packages
- Argos Enterprise Reporting