...
- 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 . In the current Course Management system there are weekly and monthly scheduled downtimes in order to account for the system's dependency on the BANNER data. This is a glaring deficiency we would like to correct in the Sakai External Database(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
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)
...