Data Warehousing

Background

The implementation of Ellucian’s Banner student information system in 2012 became Bowdoin’s entry point into data warehousing and a new model for reporting. To facilitate the reporting of data out of Banner, the College licensed and implemented Ellucian’s delivered data repository called the ODS (Operational Data Store) and an accompanying reporting tool from IBM called Cognos. Refreshed nightly, the ODS acts as a data warehouse for operational data for the Registrar’s Office, Admissions, and other offices using student data. Bowdoin has customized parts of the ODS to fit its business logic and has expanded its data to include frozen snapshots of data for IRAC reporting, degree audit data from Degree Works, and most recently, student aid data from PowerFAIDs. Cognos reporting is available to users in the offices of Admissions, Registrar, Athletics, Student Affairs, Student Aid, Bursar, and Residential Life, among others. Cognos excels at providing users with push-button access to reports, differentiated security, and automated report delivery. The implementation of the ODS and Cognos highlighted the value of shared data and common reporting tools at Bowdoin.

Data Hub Data Warehouse

In January 2015, the Office of Institutional Research, Analytics, and Consulting (IRAC) was created with staff from IT and Institutional Research. This reorganization positioned the College to move forward on a broader vision for a data warehouse, one that would integrate data from source systems beyond Banner. In this SQL Server-based data warehouse environment called the Data Hub, IRAC envisions integrating data centered around applicants, students, student aid, human resources, courses, alumni/development, historical IR archives, and surveys.

Dimensional Data Models

The Data Hub is built on a dimensional data model. Dimensional data models are designed to align with how the data is used by the business, not how it is stored in the database. The model captures a business process (e.g., students enroll in a class), thus, those who know the business process naturally understand the data model and can create their own reports. Dimensional modeling also allows for tracking history and changes in data fields in a way that the ODS does not. For example, the ODS does not offer a way to track changes in a student’s major; we only know what it was as of yesterday. Dimensional modeling accommodates the tracking of that change. Furthermore, in this environment in which queries could be run across several dimensional models, we would be able to tie together the results of multiple business processes.

Current Work

Beginning in spring 2015, IRAC focused on building the infrastructure of the Data Hub: working through design choices of the data warehouse and partnering with IT to ensure that the server infrastructure was robust, secure and scalable. IRAC collaborated with Bowdoin’s Alumni/Development division to build the Data Hub’s first dimensional model, oriented around annual giving to the College, and an initial set of reports.

Beginning in January 2016, IRAC focused on bringing into the Data Hub the historical census archives of enrolled student data, snapshotted approximately three weeks into each semester. These archives, extending back to fall 1999, hold critical data for the College around students’ demographics, attributes, enrollment status, athletic participation, and majors/minors. The census archives are often used to connect to other data sets, such as financial aid or admissions data. These snapshots serve as the source of numerous external reports, historical trend analyses, and countless ad-hoc queries from across the College.

In subsequent work, IRAC has incorporated into the Hub the history of courses taught since fall 2004, including course enrollment, course attributes, cross-listings, instructors, and the departments offering the courses. Student transcript data, which captures a student’s curricular path and academic performance, is in the process of being added to the Hub.

IRAC is using Tableau to create reports and dashboards sourced from the Data Hub.

For questions related to data warehousing, please contact Peter Wiley.