Your School Already has a Report System?
The Paper Chase
If your school has an inadequate or paper-based system you will appreciate what a nightmare it can be, chasing bits of paper with reports getting lost or damaged. One report may pass through several hands - plenty of scope for mishap, frustration and stress.
Existing Computer-Based System
Alternatively your school may have developed a bespoke computer system. With such systems a common problem is lack of scalability: what copes well with a handfull of teachers may become unworkable when servicing many simultaneous users.
Maybe the in-house system has been developed and maintained by a member of staff. When that person leaves, detailed knowledge of how to operate and administer the system often leaves with them.
So:
- How easy it it for teachers to use? more...
- How easy it it to administer? more...
Does your current system use Microsoft Access?
Q. Is Microsoft Access Unsuitable?
A. Yes - For Several Reasons:
Some reporting systems (and often those built by schools "in-house") use Microsoft Access as the database of choice. CAIRO is written in Delphi (not Access) and uses SQL Server to store the data.
This page gives a more detailed look at the issues concerning client-server methods and compares them with file-based databases such as Access.
Either scroll down this column to see the explanations, or click on the bullet-points above to navigate to the items you wish to see.
Microsoft Access exhibits some characteristics that make it a poor choice for a multi-user environment. For example, in a multi-user networked application, if just one of the users looses connection with the Access database (mdb) file (they switch off the computer without closing the program, or there is an application or system crash - not exactly unheard of in the Windows environment), then Access errs on the side of caution (some would say, paranoia) and assumes that this may have caused corruption.
At this stage the database becomes locked such that no users can log on until:
- ...All current users have exited the program (i.e. all teachers currently using the database must close the reporting application and wait until the second step below has been completed before logging back on again).
- ...the Access database repair utility has been run. (For a database of, say, 100mb, the repair process can take up to an hour when run over a standard network. During this time, the database is inaccessible.)
This is not a rare occurrence and can cause a lot of administrative problems - exacerbated by the requirement for teachers to produce reports against tight deadlines. Unreliable software is the last thing you need under these circumstances.
(In one instance where the author was acting as consultant, a company had a 100mb Access database with at most 20 users simultaneously online. On a typical day three repair operations were required, leaving the database inaccessible almost as long as it was online. The understandable frustration and exasperation of the users was almost palpable. The move away from Access to a client-server system resulted in zero failures due to this particular class of problem and a 100% improvement in reliability.)
CAIRO currently uses Microsoft SQL 2000 or SQL 2005 as the database engine. This configuration completely bypasses the Access (mdb) database engine and is unaffected by users loosing connection, for whatever reason.
Back to the list
CAIRO is a Client-Server System - What does this mean?
It means that CAIRO is in two parts:
- The software on the PCs used by teachers to write reports is the client
- To manipulate data, clients make requests to the server - a shared computer where the data is stored. Typically there are many clients serviced by one server
A full definition of client-server is beyond the scope of this web-site but the thing that makes it different to a shared database file (as described below) is that the SQL server is a powerful and dynamic database system that is capable of servicing requests from many client PCs simultaneously with negligible performance degradation. It is an "industrial strength" data solution and it gives CAIRO reliability, scalability, security and performance. Server systems include Microsoft SQL 2000 & 2005, Oracle and MySQL.
If you are reviewing reporting packages for your school, you should be aware of the database technologies that are being utilised and the implications of their use. Ask the following:
Is the database powerful enough to cope with data for many academic years?
Can it reliably handle peak usage with a large number of simultaneous users?
Does it use your network efficiently?
How does the database cope with broken connections?
What follows is for the more technically-minded although we declare this a jargon-free zone as much as possible.
There are three technologies in common use for reporting systems:
Client-Server
As described above and further below.
Database File
The most common of these is the Microsoft Access Database (mdb) file, but other databases fall into this category, such as: Paradox, FoxPro and dBase. By placing the database file on a shared network drive, many users can open the file simultaneously to access their report data. However this may give some of the problems as described in the next section, below.
-
Mailmerge
This is most often a set of Microsoft Word templates and/or documents that are driven as a mail merge with Microsoft Excel spreadsheets or Microsoft Access database files acting as the data source. This technique works for small reporting requirements but becomes difficult to manage when dealing with a whole school:
- There is a high level of administration required to operate such a system
- Performance can become a problem when there are many reports to print and collate
- Management can be very difficult
- how do you restrict the amount of text typed into Word, particularly if you have limited space in the text panels?
- how do you control the use of fonts and font sizes in order to enforce a consistent style?
- how do tutors review what has been typed in the subject reports?
- Additional complexity and administration may be involved if more advanced features are used, such as:
- Course Content (the same text to be repeated for all students in a teaching set or class)
- Subject-based grades and criteria
- Such systems tend to work one term, or at most, one academic year at a time and so do not usually allow teachers to browse previous terms' or years' data.
Back to the list
Although Access databases can be up to 1GB in size, in reality, an Access database file of 100mb is at the upper end of what is manageable.
When dealing with databases of more than 100,000 records, Access begins to creak at the seams whereas MS SQL 2000/2005, Oracle and MySQL can handle many millions of records with ease.
This means that CAIRO can comfortably hold the records of all students and their entire school report history in a single database. Consequently, CAIRO can perform trend analysis that is not possible with only one year of data. Some competitive systems try to avoid this issue by having an archive mechanism to archive and then clear the previous year's data because they cannot reliably hold information for more than one academic year at a time.
Back to the list
"...wake up, lads, can't you! It's no longer tolerable to leave users banging away on Access-format databases because ... you're going to have a problem." "Microsoft knows very well that the natural progression from Access is SQL Server..."
Steve Cassidy. " Basically data".
Real World Computing - Networks,
PC Pro Magazine,
March Edition 2003, p220.
Look at that date - this was being said in 2003 - it's all the more relevant today.
Back to the list
Imagine a database that holds information on all the 100,000 staff of a large corporation. You want to interrogate the database to find details of all staff with a salary greater than £45,000 per annum. If you are using a non-client-server database such as Access, then the request is processed on your PC and you retrieve 100,000 records over the network in order to reject the majority of them because their salary is less than the specified value. This creates a lot of network traffic. (This example is a simplification, Access would be able to optimise the query if the appropriate indexes
were available, nonetheless, the principle is correct.)
In a true client-server system, the same request is sent to the server and processed there, with only the records that meet the conditions being transmitted back to your PC. In this example, say, only 5,000 staff meet the required condition so, 5,000 records are transmitted over the network instead of 100,000 - a twenty-fold reduction in network traffic. This example is a simplification of the true situation but serves to illustrate one of the great strengths of client-server methods - network efficiency.
Because all data processing is performed on the SQL server, the overall performance of the CAIRO system is not totally determined by the speed and capacity of the workstations. So, if you upgrade the speed of the shared SQL server system by, say, increasing the amount of RAM and/or upgrading the processor(s) then everybody sees the benefit in terms of improved response times and snappier performance even on quite modest workstations. As we have already seen, CAIRO is network efficient and this also improves performance.
Back to the list
Microsoft Access does not have all the tools that are necessary to maintain and support a serious database application. Such software tools come as standard with Microsoft SQL 2000 and enable the following:
- transaction logging (enables the database to be "wound back" to any position since the last backup)
- full data backup to tape and/or disk. This is automatic and can be scheduled as often a required. During reporting sessions some schools schedule two backups a day. Backups can be performed while teachers are logged on and keying-in data. The transaction log can also be backed-up as part of this process.
- re-indexing of data (to maintain optimum performance)
- integrity checks and repairs
- performance monitoring - allowing real-time graphical display of the many different aspects of the database engine, also allowing quick and easy diagnosis of any performance bottlenecks.
- automatic deletion of aged backups
- multiple database support - many databases can be serviced by the one server engine. If, for example, you use or intend to use the new release of SIMS as your school administration package then both CAIRO and SIMS can run concurrently using the same MS SQL 2000
or 2005 server engine.
Back to the list
Reporting systems that are developed by teachers "in-house" often use Microsoft Access as their database engine because it is cheap, usually already installed on the school network, relatively easy to program and appears to work well for single-user and small databases. For the reasons given above, and many others of a more technical nature, even Microsoft recommend that multi-user databases are programmed using client-server software. For multi-user databases Microsoft SQL 2000 and 2005 are fast, secure, reliable, maintainable, and
scalable. Access MDB files and other similar database-file solutions are not as well suited to this networked environment.
"Do not ask a boy to do a man's job"
...Anon
Back to the list
