Academic List Technical Details

Owned by Julio Polo
Last updated: Jun 30, 2014 by mhodges

Banner data

Data about major and degree pursued comes from the hawaii.edu:menu:sis:curricula groups in UH Group Store.  These groups get their data from Banner's SGBSTDN table, where there is an effective term column which roughly equates to the semester when the student began in the program.  However, there isn't a column that tells us when the student is no longer in the major.  We need to figure that out based on other information:

  • Student graduated
  • Student dropped out
  • Student changed majors

That last situation, student changing majors, actually does provide SGBSTDN with the semester where one major ends and the new major begins.  For all other scenarios, we cannot rely on SGBSTDN to tell us when the person graduated or dropped out.  SGBSTDN could easily have hundreds of thousands of active records for people who started on a major several years ago, but who are no longer at the University.

Step 1: Find SGBSTDN that are relevant for the desired term

The first challenge is to figure out which SGBSTDN records are applicable to the desired term:

  • Find all people in the program whose SGBSTDN effective term is equal to or prior to the desired term
  • Then remove all people who were in the program and who changed majors on or prior to the desired term

The above is a composite group created under UH Group Store upon request of an academic list.  For example, here is the composite group for Spring 2014 Art BA majors:

hawaii.edu:menu:sis:curricula:MAN:10:ARTH:ART:UG:BA:201430 is a composite group defined as:

hawaii.edu:menu:sis:curricula:MAN:10:ARTH:ART:UG:BA:201430:begin-on-or-before

complement

hawaii.edu:menu:sis:curricula:MAN:10:ARTH:ART:UG:BA:201430:end-before

where hawaii.edu:menu:sis:curricula:MAN:10:ARTH:ART:UG:BA:201430:begin-on-or-before
is a group of these groups:

hawaii.edu:auto:sis:curricula:MAN:10:ARTH:ART:UG:BA:200930:begin (majors who began Spring 2009)
hawaii.edu:auto:sis:curricula:MAN:10:ARTH:ART:UG:BA:201010:begin (majors who began Fall 20009)
hawaii.edu:auto:sis:curricula:MAN:10:ARTH:ART:UG:BA:201030:begin (majors who began Spring 2010)
hawaii.edu:auto:sis:curricula:MAN:10:ARTH:ART:UG:BA:201040:begin (majors who began Summer 2010)
hawaii.edu:auto:sis:curricula:MAN:10:ARTH:ART:UG:BA:201110:begin (majors who began Fall 2010)
hawaii.edu:auto:sis:curricula:MAN:10:ARTH:ART:UG:BA:201130:begin (majors who began Spring 2011)
hawaii.edu:auto:sis:curricula:MAN:10:ARTH:ART:UG:BA:201140:begin (majors who began Summer 2011)
hawaii.edu:auto:sis:curricula:MAN:10:ARTH:ART:UG:BA:201210:begin (majors who began Fall 2011)
hawaii.edu:auto:sis:curricula:MAN:10:ARTH:ART:UG:BA:201230:begin (majors who began Spring 2012)
hawaii.edu:auto:sis:curricula:MAN:10:ARTH:ART:UG:BA:201240:begin (majors who began Summer 2012)
hawaii.edu:auto:sis:curricula:MAN:10:ARTH:ART:UG:BA:201310:begin (majors who began Fall 2012)
hawaii.edu:auto:sis:curricula:MAN:10:ARTH:ART:UG:BA:201313:begin (majors who began Fall/Winter 2013)
hawaii.edu:auto:sis:curricula:MAN:10:ARTH:ART:UG:BA:201330:begin (majors who began Spring 2013)
hawaii.edu:auto:sis:curricula:MAN:10:ARTH:ART:UG:BA:201340:begin (majors who began Summer 2013)
hawaii.edu:auto:sis:curricula:MAN:10:ARTH:ART:UG:BA:201410:begin (majors who began Fall 2013)
hawaii.edu:auto:sis:curricula:MAN:10:ARTH:ART:UG:BA:201430:begin (majors who began Spring 2014)

and hawaii.edu:menu:sis:curricula:MAN:10:ARTH:ART:UG:BA:201430:end-before
is a group of these groups:

hawaii.edu:auto:sis:curricula:MAN:10:ARTH:ART:UG:BA:201210:end (those who switched majors from ART BA on 201230, Spring 2012)
hawaii.edu:auto:sis:curricula:MAN:10:ARTH:ART:UG:BA:201310:end (those who switched majors from ART BA on 201330, Spring 2013)

 

Keep in mind that this composite group could have people who were in the major many years or decades ago, and who graduated or dropped out.  We can set a "floor" on how far back we will take a SGBSTDN record by looking at registration records.

Step 2: Intersect with recent registration activity

The composite group described above is already intersecting with recent registration activity by setting a "floor" term (greater than or equal to 201410, Fall 2013).  If there is no registration activity since this floor term, that student will not be included in the list for the major.  This is the reason we do not see any majors prior to Spring 2009 in the example above.  Even though there are active SGBSTDN records for majors that started in 2008, 2007, 2006 and so on, the lack of registration activity by that population allows us to keep them out of recent terms.

We can also use registration activity in lieu of graduation of approved leave data.  Since a person can be on approved leave for two consecutive semesters, we could set this floor (registration) term to two terms prior to the desired term.  However, we also need to detect that the person has graduated, so we need to detect lack of registration activity for the term after they have graduated.  That means we would need to set the floor term to three terms prior to the desired term.