University of Exeter IT Services
Managers Meeting - SDM
IT8280
Project Proposal: Student Record Data Warehouse Issued by
Ray Burnley
18 June 2003

This project is about continuation of service for provision of student record related data via a data warehouse of a subset of SITS and other data fields.

I suggest the project name of:
 STRAW .. STudent Records Administrative Warehouse
Other options might be:
 AdmInfo .. Administrative Information
 STAR .. STudent Administrative Repository
 SAW .. Student Administrative Warehouse
 STAW .. STudent Administrative Warehouse (pronounced "store")
 STAIR .. STudent Administrative Information Repository
 STIR .. STudent Information Repository
 SID .. Student Information Distribution
 ROSI .. Repository of Student Information
 ROSIe .. Repository of Student Information (electronic)
Votes for the above and other suggestions welcome.

Requirement

This project proposes to fulfil a requirement from several SITS Phase4 Schools for access to Student Record data for in-house applications that cannot, at present, be replaced by SITS or eVision. Many of these application currently use the services of the SAM3 (Schools Administrative Management) database system which will be terminated this September, 2003.

The requirements are two-fold:

  1. A student record datasource for use by School's in-house, mainly web-based, applications.
  2. An application for teaching group management and timetabling to replace existing SAM3 functionality not yet available through SITS.

More specifically:

  1. Maths datafeed for own web-based student record system
  2. SHiPSS SAM3 replacement datasource for:
    1. Web based Module descriptions
    2. Web based Student Evaluation of Programmes & Modules
  3. SHiPSS and Modern Languages application to replace SAM3 functionality for Teaching Group Management & Timetabling

The issue of student evaluation of programmes and modules over the web is not confined to SHiPSS. A recent proposal to Teaching & Development Committee suggested a University wide system similar in functionality to the SHiPSS and Chemistry applications. That proposal resulted in the formation of a working party to further explore the proposal. The data warehouse suggested by this current proposal could be a primary source of data for whatever, if any, system is approved.

Proposed Project

Discussions have been taking place since 28 March 2003 between various of: Ray Burnley (IT Services, SAM3 Developer); Sue Milward (IT Services, Admin Computing team leader); Tom Rosenbloom (SHiPSS, Website Developer); Raymond Thompson (Maths, Senior Computing Officer); Keith Zimmerman (SHiPSS, School Adminsitrator). The outcome of these discussions is the following project:

Leverage the work done on the SAM3 project to provide a nightly download of a small subset of SITS data. The SITS server procedures will be tuned and automated versions of those currently used by SAM3.

This download to be loaded into a MySQL database (data-warehouse) on a suitable server (currently Helios). The data-warehouse's table and column structure will be a pared-down version of the current SAM3 schema.

Access/Permissions and SQL queries to the data-warehouse will be provided for Maths and SHiPSS for their web applications.

The Group functionality of SAM3 will be re-engineered as a web application and access given to SHiPSS and Modern Languages.

There will be an on-going (low maintenance) need to monitor and bug-fix the system until the projects requirements can be fulfilled by the standard central administrative systems.

Other solutions have been discussed and rejected in favour of this current proposal. These included: a flat-file comma separated value datadump; an Oracle database as a datadump; an XML documents dump of the data.

Timescales

In a sense the project has already begun with several consultations between the interested parties. The References section at the end of this document has links to the notes of most of those meetings.

The Maths datafeed is required by early September (2003).

The SHiPSS and Modern Languages requirements are replacement of SAM3 functionality and as such will be needed when SAM3 is turned-off. This is expected to be, again, early September once the resits have been processed.

When SITS/eVision functionality is available to satisfy the requirements, the project should be reviewed with the expectation that the Schools involved will migrate to these new systems.

Resources

It is suggested that the main resource for the project will be Ray Burnley's (RDB) time. As RDB is the (only) SAM3 Developer and the proposed system is a trimmed-down and re-engineered version of SAM3, he is best suited for the task.

There will need to be crucial, but not arduous, consultations with the following players for which their time will be a required resource:

RDB's current commitments are mainly focussed on supporting SAM3 Schools during the exam period and again at resits. RDB also has a nominal allocation of 2 days a week to SITS project implementation. RDB has several steering and strategy group duties. RDB would expect to be able to work 50% of his time on this project until mid September, 2003, assuming this project to take the majority of his SITS time. Two weeks leave will probably be taken during this period.

In terms of server resources, the proposed system is, fundamentally, a replacement of SAM3 and as such should not impose any significant further demands. If anything the filestore and processing requirements should be less as the number of Schools and hence students, is reduced.

Project Plan

A simple project plan table:
What Who Days Starts Ends
Draft detailed data requirement (RDB) 5 in-prog Jun.24
Finalise data requirements & document (RDB)+SITS
(RDB)+Maths
(RDB)+SHiPSS
1
0.5
0.5
Jun.25 Jul.01
Create data-warehouse db & document (RDB) 2 Jul.02 Jul.04
Amend export scripts, test & document (RDB) 3 Jul.07 Jul.11
Create dataload procedures, test & document (RDB) 3 Jul.14 Jul.18
Create SQL for Maths, test & document (RDB) 1 Jul.21 ...
Create SQL for SHiPSS (Modules), test & document (RDB) 1 ... ...
Create SQL for SHiPSS (Evaluation), test & document (RDB) 1 ... Jul.25
Re-engineer SAM3 Groups as a webapp (RDB) 5 Jul.28 Aug.08
Test & document Groups webapp (RDB) 3 Aug.11 Aug.15
(Holiday) (RDB) 10 Aug.18 Aug.29
Sign-off development phase (RDB)+clients 2 Sep.01 Sep.05
Ongoing support (RDB) 0.25pw max. Sep.08 ...

Specific Technical Aspects

LDAP .. Several data fields required of the data-warehouse would seem to be prime candidates for being part of the LDAP service. Namely, University ID codes, IT Services Username and IT Services Email address (both for students and staff). Many of the "client" applications are PHP based and could perform their authentication of users and data lookup via LDAP.

Server Location .. The only target server at present is Helios with the Apache webserver and MySQL database system, it is also the current home of SAM3 which this proposed system will replace. There are plans for the fourth quarter of this year for a replacement web server which may see MySQL services dispersed to another server. There will be server implications to rolling-out the eVision service.

MySQL release .. is currently 3.23 on Helios. There are certain features in the newer release 4.0's that would simplify some operations of the proposed database. The expectation is that a MySQL 4.0 release will not be generally available at Exeter until the web server review in the Autumn.

Writeable Fields .. Data warehouses by nature are read-only database objects. For the control and internal, as well as administrator, management of the database, several extra tables and extra fields will be required. It would seem prudent to secure the core data fields as exported from the central administrative systems. To this end, it is proposed that the imported tables are strictly read-only and that mirror tables of synchronised (much shorter) records are maintained alongside them. The fields in these mirror records would be writeable by system procedures and selected applications.

Lookup Tables .. should be removed where appropriate to simplify the data-warehouse structures and SQL queries. Lookup can be done at export time and extended data fields written.

References

Location: \\remus\users\its\docs\it8200\it8280.html
Author: Ray Burnley, IT Services, 18-Jun-2003