SED BANNER

Sakai External Database BANNER Data

The BANNER Student Information System is an Oracle Forms Application that has been in existence for over 15 years. It is the system of record for course, instructor, and student information. Therefore, it represents most of the external information required by Sakai for the purposes of provisioning and maintaining course sites.

BANNER Data Architecture

The BANNER system consists of thousands of tables, views, sequences, and pl/sql procedures. Luckily, only a handful of those objects are of interest to the Sakai project. The following diagram and table will describe each of the objects Sakai will be collecting data from.

Name

Object Type

Description

Example

STVTERM

Table

Academic Term Infomation

Spring Quarter 2007

STVCOLL

Table

Academic Colleges

AE - Agricultural and Environmental Sciences

STVDEPT

Table

Academic Departments

ENH - Environmental Horticulture

STVSUBJ

Table

Academic Subjects

LDA - Landscape Architecture

SCBCRSE

Table

Course Information

LDA 001 - Spring Quarter 2007

SSBSECT

Table

Section Information

LDA 001 - Section A01 - Spring Quarter 2007

SSRMEET

Table

Section Meeting Times

MWF 9:00-10:15 - LDA 001 - Section A01 - Spring Quarter 2007

SIRASGN

Table

Instructor Assignments

John Smith - LDA 001 - Section A01 - Spring Quarter 2007

SFRSTCR

Table

Student Enrollments

Jane Doe - Waitlisted - LDA 001 - Section A01 - Spring Quarter 2007

Requirements

  • Availability: The BANNER data used by Sakai must be available on a 24x7 basis.
  • Fast Refresh: Updated Student Registration information must be available as soon as possible. In general, the other BANNER data sources do not need to be updated as frequently.
  • Flexibility: The resultant data store must be felxible enough to adapt to changes in Sakai's data requirements, as well as to include and augment data with data from external sources.

Design

BANNER, while a core campus system, is not yet a 24x7 application (24x7 database availablility is expected in Summer 2006). Therefore, in order to avoid periods of downtime due to BANNER database maintenance, Materialized Views have been chosen as the method to pull data from BANNER to the External Database.

Oracle Materialized Views work similarly to normal views, but rather than storing the query results in memory, the query results are stored in the local oracle database. Subsequent queries using the view then use the local data rather than accessing the view source every time. Materialized Views include refresh information indicating when and how the information in the view is updated from the source system.

In the ideal design, BANNER data will be extracted in a normalized format similar to the structure of course information in Sakai. Additionally, data in the Materialized Views will be updated automatically when data is changed on the source side, resulting in near-realtime data refresh.

Implementation

For the 2.4.x Course Management implementation of Sakai, the Materialized Views have been re-designed to closer meet the original design goals. Each MView maps closely to a single object in the Course Management schema, and converts most ids to a Course Management compatible format during the refresh process. However, this approach required sacrificing the fast-refresh capability of most of the materialized views. Fast refresh has been preserved on a few tables that contain time sensitive data such as enrollments and instructor assignments. The documentation for the Course Management External Database implementation is located at Course Management Object Relational Mapping.