Three Methods to Model the Student Lifecycle
In the last blog we looked at the overall University Admissions data flow and a lifecycle of student process. As we continue to dissect the data architecture, we face a few key data modeling decisions. A good working data model starts with a solid design and a thorough understanding of the business processes, events, actors and measures. First, we look at the nature of the business events and related data.
Business events are the atomic actions performed by the Staff, Faculty, Offices, and Departments in the course of the processes. In our case—a student—takes actions that move them along the student lifecycle such as: checking for education options, visiting our website, responding to the outreach, submitting an application, graduating. These actions leave a trace of data containing atomic and measurable details of the process. We further classify the event types as: (1) point-in-time, (2) cyclical, (3) periodical.
Model #1: Point-in-time events. Usually short in duration and transactional in nature, point-in-time events occur irregularly and are beyond our control. There is no predetermined correlation or a sequence between them. A point-in-time event typically reflects a raw transaction in our system of record (Financial Aid, Web logs, Student Information System, Course Catalog, CRM, email) and might be initiated by a student, a prospective candidate or an alumni. Imagine the events as points, randomly scattered on a timeline.
The point-in-time events are best modeled in a transactional fact table. A simplified view below illustrates an example of a transaction fact data view. Note, that in reality, the dimensional values will be replaced by the reference foreign keys.
Model #2: Cyclical events are long-running “stories” that can take days, weeks, or months to complete. They often represent a sequence of predetermined milestones or hurdles of a complex and intensive business processes. The student admission cycle process is a good example; usually begins with an application, followed by a number of steps specific and governed by the institution: application submitted → scores approved → admission decisions → financial aid approved → admitted → enrolled. As each milestone is completed, it leaves a trace of associated data including dates, decisions, results and other facts; they are crucial in the student lifecycle analysis and decision making process.
It is easy to notice in the illustration, the cyclical events being more structured and organized, reflecting the correlation and sequence of the steps ultimately moves the student along the lifecycle:
We want to model the cyclical event as a “pipeline” or “accumulating snapshot” fact table. We chose the admission process, with a handful of milestones as an example to make it easier to understand.
One can clearly see, how the admission process unfolds naturally for each student; the table is telling us the student is the lifecycle. We actually don’t need a complex query or a modeling tool to understand it—imagine achieving the same using the transactional fact model.
Model #3: Periodical events. Our last model works best for recurring events with the predetermined and constant intervals. They are typically used to sample and summarize the state of the processes, along with their cumulative measures in a certain point in time: weekly, monthly quarterly. To illustrate this type of events let’s think about a periodical review and analysis of the students’ credit balances. The Financial Aid Team examines students’ accounts at the end of each quarter:
To capture and analyze this type of event’s data at the given intervals, we are equipped with a snapshot fact table:
The three types of models are versatile and can be used standalone or in a combination, depending on a process and category of events modeled. To complete the design we ought to connect them with the properly conformed dimensions including Student, Academic Program, Calendar, Application, Demographics; altogether forming a robust student lifecycle star schema model.
If you would like to learn more about the model and the process, we will be presenting this concept in detail at the upcoming Higher Ed DW conference in April.
In our next and final blog we’ll bring all the design elements to life, demonstrating the visualizations and metrics that help answer vital business questions around the lifecycle of a student.