SAM Position Paper January-2001

This Position Paper was written by Ray Burnley, SAM Developer, for a meeting on Thursday 25-Jan-2001 14:15 to discuss the direction and future of SAM.

Notes from the meeting, produced by Laurie Burbridge, can be viewed here.
If you have any thoughts on this subject and/or comments on this paper you can email myself or other members of the meeting, the member list and their emails follow. If you wish to spark a more "SAM public" debate you could email the SAM-Users maillist.
Derek Lewis, SAM Manager, D.R.Lewis@exeter.ac.uk
Elizabeth Stewart, SML Administrator, E.A.Stewart@exeter.ac.uk
John Buckett, Developers Line Manager, J.Buckett@exeter.ac.uk
Keith Zimmerman, SHiPSS Administrator, K.A.Zimmerman@exeter.ac.uk
Laurie Burbridge, Director of IT Services, L.Burbridge@exeter.ac.uk
Ray Burnley, SAM Developer, R.D.Burnley@exeter.ac.uk
____________________________________________________

Contents

Overview of SAM

Introduction

SAM, the Schools Administrative Management system, is a Microsoft Access database application targeted at Schools administrators and their staff. Its goal is to provide the tools to simply, effectively & efficiently manage the data necessary for the School's student body. The user interface is designed to be simple and quick to use with a consistent look and feel across the system, promoting rapid familiarity with the system. The internal structures of SAM enable new features and functionality to be easily and quickly integrated into this consistent interface. SAM's distribution and installation procedures are well developed and simple for both the users and the developer.

The system is implemented as a per PC, user installable Front End (FE) MS-Access97 database application. The FE database has no data but has links to tables in the individual School's shared Back End (BE) database located on a Novell NetWare fileserver. The FE databases are the user interface elements of the application the forms, queries, reports and procedures. This division enables enhancements and updates to the application to be made with no danger to the underlying data as it is the FE database that we are changing. One consequence of this design strategy is that there is a separate BE database for each School running SAM. There is some data duplication between the BE's and hence data inconsistency can and does occur.

Task orientated

SAM takes a task orientated approach, the user need know little or nothing of the underlying data structures. The following description outlines the various task contexts along with the major functionality available in that context.

Day-to-day Student tasks:
looking-up & maintaining student contact information;
maintaining comprehensive notes on students
Module Registration tasks:
student's current session modules & assessments;
student's module choices for the next session;
Group Membership & Timetabling:
timetabled teaching groups;
personal tutor groups;
ad-hoc groups;
membership lists;
timetable reports;
Examinations & Assessments:
entering exam & assessment marks;
printing exam board reports;
export/import of marks for inter School collaboration;
upload of module marks to central SBS system
Administrative tasks:
Module & Assessment Profile definition;
Programme Profile definition;
Data Integrity checks & resolution;
Data Reconciliation between Central DB and SAM;
Progression of students to new session;
download of new student intake at session start;
Reports:
relevant reports are available from a button on almost every form;
report options allow: printing, previewing, and saving in various formats: RTF for Word, CSV for Excel, crude HTML, plain text;
report options also allow selection of layout, data content & ordering;

Central system integration

One difficult aspect for SAM is the integration of SAM's data with the central administration's procedures and database systems. At SAM's inception the University was, and still is, using the SBS system, sometimes referred to as CHA. At the present time a replacement system, SITS, is being rolled-out with (I think) an expected end-date of end of the 2001/02 academic year.

For any one School's SAM database there is an initial download of data relevant to the School. This includes student details and past & current module registration details (including past marks). The data is initially extracted from the central SBS system via QUIZ programs and is then imported into a UNI BE database. The UNI BE has the same data structures as the individual School's BE's but the tables store all the University's student and module data. The School's BE databases import "their" data from this UNI BE. The process of extracting data from the central systems and then importing it into the SAM "central" database is a manual one carried-out by the SAM Developer (Ray Burnley).

This manual updating of SAM's UNI BE can take almost a day from the running of multiple QUIZ programs to the import & restructuring of the data in UNI BE. The frequency of the updates is on a "needs-be" basis, that is when there have been significant changes to the central data. Updates are made as the bulk of admissions have been processed, then probably once a week for the next 4 weeks for changes in admissions, then again for 4 weeks over the module registration period. An update is then taken when the Exams Office have processed the Module Registration "Red Forms" for the module data and allocated Candidate Numbers. Other sources of data are a download of minimal Staff data from the Personnel systems, and IT usernames and email addresses from IT Services systems.

System topology

The topology of SAM is a File-Server one, where the data for an individual School is located in a backend (BE) MS-Access database. This database resides on a NetWare 4 server (currently Minos) in a folder specific to the School. Each SAM user in the School has an MS-Access front end (FE) database installed on their PC. This FE database has effectively no data but all the forms, queries, procedures & report definitions that make-up the users view of the database. The FE databases use table links to access data in the tables of the shared BE database. The security and accessibility of the databases is controlled via allocation of users to NetWare groups which have the relevant access rights to the required server based folders.

The alternative topology for systems such as SAM is the Client-Server approach. Here the data again resides away from the users PC (Client), in a BE database on a server so that it can be shared by all users. But now the FE client database doesn't link directly to the tables in the BE database as if that database was on the users own PC, it makes requests for data to a database server. The database server is an application running on the remote server that (simplistically) receives requests for data in the form of SQL statements from the client PC. The database server effects the SQL and returns the resultant data to the client PC for display in forms or inclusion in a report. In many application this can be a more efficient approach as only the data required by a form or report is passed over the network and is processed by the local database application. This may be only one record! In the File-Server topology every record involved in executing the SQL statement must traverse the network and be assessed by the local database application.

Why is SAM designed as it is?

Partly because we didn't start with an IT green-field site. SAM is a development of an original MS-Access database written for SHiPSS, which in itself followed an earlier R:Base database in Politics. Microsoft Access 97 was also deemed to be the only viable application vehicle for the project because of it's widespread availability. The use of separate BE databases for each School was based on the idea of giving each School responsibility over their own data. It also had the consequence of limiting the numbers of users accessing a single database to a level sustainable by MS-Access.

Even with these design considerations it is possible to adopt either the File-Server or Client-Server topology. SAM adopted the File-Server approach as the Novell NetWare resources where available, reliable and cheap. With a Client-Server setup we would have needed either Unix or NT server resources and a database server application like Oracle or MS SQL-Server. A much more expensive option on both counts. Additionally, developing in "pure" MS-Access is much easier and hence quicker and cheaper.

There are downsides to this approach: duplication of data across School BE's and the ensuing data inconsistencies; speed of response and load on the network; difficulty of interfacing SAM to other databases and web enabling technologies that normally reside on Unix or NT servers; difficulty in overall administration and updating of SAM.

Current Usage

The following section lists the University's Schools and their usage of SAM. Also included is a rough figure for percentage of FTE's in the relevant School (thanks to Keith Zimmerman for these figures). Usage is indicated as: Full where SAM is the primary or only system used and has been used for exams; Active using SAM but so far not for exams; Dormant have shown interest in SAM and have been setup with a database but do not seem to be using it; None for Schools who have never shown interest in the development or adoption of SAM;

  • Full SHiPSS (HPS) 11.0% fte's
  • Full Modern Languages (SML) 8.6% fte's
    including the Foreign Language Centre (FLC)
  • Full Law (LAW) 5.6% fte's
  • not including Centre for Legal Practice (CLP)
  • Full Geography & Archaeology (GOA) 5.6% fte's
    Archaeology used SAM for exams 1999/00
  • Active Biological Sciences (BIO) 4.8% fte's
    some parallel running of exams 1999/00
  • Active Chemistry (CHE) 2.5% fte's
  • Active Engineering & Computer Science (ECS) 7.1% fte's
    not Computer Science
  • Dormant English (EGL) 5.9% fte's
    used SAM for exams 1999/00
  • Dormant Classics & Theology (CTH) 4.2% fte's
  • Dormant Psychology (PSY) 4.0% fte's
  • Dormant Camborne School of Mines (CSM) 4.1% fte's
  • Dormant PG Medicine & Health Sciences (PMH) 6.3% fte's
  • Dormant Business & Economics (SBE) 11.3% fte's
  • Dormant Education (EDU) 3.4% fte's
  • None Continuing and Adult Education (CAE) 1.2% fte's
  • None Continuing & Professional Development (CPD) 0.6% fte's
  • None Drama & Music (DAM) 3.0% fte's
  • None Mathematical Sciences (MAS) 4.3% fte's
  • None Physics (PHY) 2.3% fte's

Status of Development

SAM is and has been a usable, useful, administrative tool for over two years now. Over that time the design has been refined and extra functionality has been added. These changes are generally incremental and developments appear in new releases with no break in service. In recent releases there has been a major updating of SAM functionality in two areas: "Groups" and "Notes".

The handling of Groups has become more generalised allowing their use for: teaching groups (lectures, tutorials, etc.); personal tutor groups; committees. All Group working uses shared forms and reports making the manipulation of group definitions and membership simple for the user and more easily maintainable by the developer.

In a similar vein, the ability to work with student notes has been generalised. Now notes can be associated with many of the data areas in SAM. There are notes fields available on each of the major student information forms: Module Registration; Study/Personal info; Contact info. Again, there is a single, simple interface for working with Notes. All of a student's notes can be viewed from a single form.

The other area of recent development is the extension of SAM's ability to publish it's data as web pages. This extension goes by the name of SAMweb. For more than six months now SAM has had a limited ability to export web pages, namely Module definitions. This capability has been extended so that almost any report in SAM can be re-defined as a web page or a set of web pages. When the resultant "report" is a set of pages, SAMweb can produce a index page to that set. Index pages can be automatically re-generated whenever a constituent page is updated. The impetus behind SAMweb is the need to extend the reach of SAM data to all the School's academics simply and cheaply via their web browsers. A School can now setup a SAM "website" on the file-server used for SAM backend databases, piggy-backing on the access control of the server. Any academic in the School can be given access permissions to view say the teaching group lists, timetables, tutorial group lists, etc.

SAMweb's overall design, data structures, and internal procedures are virtually complete. All that remains are some extensions to the internal procedures to finish SAMweb's "sub-report" and "indexing" capability, and some user interface forms to assist the Schools "webmaster". SAMweb can be demonstrated now and a initial release will be available some time in the week commencing 29-Jan.

Suggested Essential Development

At the meeting the items in the following sections where discussed and a 6-point development plan with times was agreeded. Number of man-days for the "essential" items are indicated in square brackets.
This, and the following two sections, outline the developers thoughts on areas for ongoing or further development.
  1. Full roll-out of SAM to the "dormant" Schools. With a couple of days of developer consultation for each (of the 7) Schools to get them back on stream. This may involve the implementation of further exam board reports. [21 days]
  2. Inter-School data exchange particularly for Schools with joint honours programmes. [15 days]
  3. Complete the SAMweb development to enable publication of static pages of information. Thus providing a cheap and easy means of disseminating the School's data to the School's academic staff. [10 days]
  4. Tuning of SAM's handling of Postgraduates with their different requirements for assessment reporting, progression, and thesis & dissertation recording. [5 days]
  5. Ability to emailing groups of students [5 days]
  6. Interface with MS-Word documents, mailmerge functionality [5 days]

Suggested Enhancement Development

  1. Produce a comprehensive set of Help web pages and a web based Tutorial. There are three different attempts at user documentation already in existence, none of which have been updated to reflect recent enhancements to SAM.
  2. Integration of SAM with the incoming central SITS system.
  3. More extensive functionality for defining Programme/Module profiles for more streamlined Student/Module allocation.
  4. Alumni data - where does a student's data go when they graduate.
  5. A Statistical module to generate reports for TQA's and other exercises.
  6. Investigate running SAM in Client-Server mode
  7. Investigate fully web-enable SAM: adapting SAMweb to generate dynamic, "on-the-fly" web pages; and enabling updating of data via web forms (a Client-Server implementation of SAM is a pre-requisite here).
  8. Audit of SAM functionality and internal database objects in order to rationalise the application design for faster working and easier maintenance & development.
  9. Technical documentation to make on-going maintenance easier and make the project less dependent on the single developer.
  10. Investigate the use of SAM with MS-Access 2000 and with Windows 2000

Possible Enhancement Development

  1. Enhance the Staff area to include: notes on research interests; workload management; etc.
  2. Extend SAM's timetabling capabilities to produce room timetables.
  3. Extend SAM to deal with the Admissions process

Postscript

This project has been on-going for almost two years now with varying degrees of resource input. As of now, the single developer of the system has 50% full-time to dedicate to SAM for at least the next 12 months. The size and nature of the project is such that much of the development work happens outside this nominal allocation. Total submersion in the development is much more effective than the "part-time" approach.

There are conflicting views on the extent to which SAM will continue to be used with arrival of the SITS system. One camp believes SAM will be redundant once SITS roll-out is completed around Summer 2002. The other camp believes that SAM may still have a role alongside SITS, assuming SAM and SITS can be made to communicate with each other. I would hope that at least a commitment to on-going maintenance can be made up until SITS is fully on-stream. I would also plead for some support for further web-enabling development.

The developer setup the Project SAM website at the start of the project, click on the link below to pay it a visit.

_______________________________________________ Project SAM
Initial release: 24-Jan-2001 15:15
Amended for wider distribution: 07-Feb-2001 12:48