Sakai External Database BANNER Data
This page will describe the critical information about sourcing data to the External Database from Banner.
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.
...
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
In practice, not all of the design goals have been achievable at present. Currently the materialized views represent a one-to-one relationship with the source BANNER tables. For example: the SFTSTCR table in BANNER is Materialized on the External Database side as a view called Banner_Rosters, with its columns being a subset of the columns in the SFRSTCT table.
Commit-based refresh of tables requires a number of restrictions on the source database structure and can incur some overhead on the source side. Therefore, for the time being, all materialized views are being created to refresh every half-hour. However, rather than doing a complete refresh and copy of all data, the materialized views have been created using the FAST REFRESH option. This option allows the query to update only the changed rows from the source database. In order to enable fast refresh, materialized view logs must be created on the source database.
SED ERD.vsd - Original BANNER External Database ERD (Created in visio and unconvertable, will need to replace once a new ERD tool is found)
Banner Tables.vsd - BANNER Table descriptions (Created in Visio)
SQL Scripts
Banner MViews.sql - The sql ddl for the BANNER Materialized Views (deployed in the SED Schema)
Banner MV Logs.sql - Sql ddl for the BANNER Materialized View Logs (deployed on the BANNER instance)
Files
...
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.