Chapter 12 : Education

Chapter 12 : Education

In the case of applicant tracking, prospective students progress through a

standard set of admissions hurdles or milestones. Perhaps we’re interested in

tracking activities around key dates, such as receipt of preliminary admissions

test scores, information requested (via Web or otherwise), information sent,

interview conducted, on-site campus visit, application received, transcript

received, test scores received, recommendations received, first pass review by

admissions, review for financial aid, final decision from admissions, accepted,

244 CHAPT E R 12

admitted, and enrolled. At any point in time, people in the admissions and

enrollment management area are interested in how many applicants are at

each stage in the pipeline. The process is much like a funnel, where many

applicants enter the pipeline, but far fewer progress through to the final stage.

Admission personnel also would like to analyze the applicant pool by a variety

of characteristics.

The grain of the accumulating snapshot to track the applicant’s lifecycle is one

row per prospective student. This granularity represents the lowest level of

detail captured when the prospect enters the pipeline. As more information is

collected while the prospect progresses toward application, acceptance, and

admission, we continue to revisit and update the prospect’s status in the fact

table row, as illustrated in Figure 12.1.

Student Registration Events

There are many situations in which events need to be recorded as the simultaneous

coming together of a number of dimensional entities. For example, we

can track student registrations by term. The grain of the fact table would be

one row for each registered course by student and term. As illustrated in Figure

12.2, the fact table has the following dimensionality: term, student, student

major and attainment, course, and faculty.

In this scenario we’re dealing with fact data at the term level rather than at the

more typical calendar day, week, or month granularity. Term is the lowest level

available for the registration events. The term dimension still should conform

to the calendar date dimension. In other words, each date in our daily calendar

dimension should identify the term (for example, Fall AY2002), academic year

(for example, AY2002), and term season (for example, Winter). The column

labels and values must be identical for the attributes common to both the

calendar date and term dimensions

Events often are modeled as a fact table containing a series of keys, each representing

a participating dimension in the event. Event tables often have no obvious numeric

facts associated with them and hence are called factless fact tables.

The inevitable confusion surrounding the SQL statement, while not a serious

semantic problem, causes some designers to create an artificial implied fact,

perhaps called registration count (as opposed to dummy), that is always populated

by the value 1. While this fact does not add any information to the fact

table, it makes the SQL more readable, such as:

SELECT FACULTY, SUM(REGISTRATION_COUNT)... GROUP BY FACULTY

At this point the table is no longer strictly factless, but most would agree that

the 1 is nothing more than an artifact.

Explicit Rows for What Didn’t Happen

Perhaps people are interested in monitoring students who were registered for

a course but didn’t show up. In this example we can envision adding explicit

rows to the fact table for attendance events that didn’t occur. Adding rows is

viable in this scenario because the nonattendance events have the same exact

dimensionality as the attendance events. Likewise, the fact table won’t grow at

an alarming rate, presuming (or perhaps hoping) that the no shows are a small

percentage of the total students registered for a course. In this situation we’re

no longer dealing with a factless fact table because now the attendance fact

would equal either 1 or 0.

While this approach is reasonable in this scenario, creating rows for events that

didn’t happen is ridiculous in many situations. For example, if we think back

to our transportation case study, we certainly don’t want to build fact table

rows for each flight not taken by a frequent flyer on a given day.