Local API - Improving Session Attendance Data Performance

This guidance has a new worked example

Local Integration - Change tracking Example | ESS Portal (sims-partners.com)

Everyone wants attendance marks!

Our technical integrators (TIs) extract millions of attendance marks each day from school's data held in SIMS systems.  As we look to a transition to web interfaces and in anticipation of web based write APIs which are due soon; this article looks at how we can optimise this data exchange for the benefit of the school, hosting platforms and TIs of course.

Our hosted platform gives us some interesting insights in to the use of our stored procedures and we can easily see the number of calls to the most used stored procedures.  Without the need to detective work we discover that 'Get Group Session Attendance' was called on average 4000 times per day per school.  With over 1000 schools on our hosted platform then that level of use is certainly worth exploring with our partners.

What the metrics don't tell us is the scope of the call which requires a 'group' and a date range.  We know attendance marks are often updated retrospectively. If a TI requires a true copy of attendance data, their code needs to ensure that there are no changes missed and will often request data for a period of 3 months (to ensure that long summer holidays are covered) and often request data updates as frequently as every 5 minutes.

We recommend the use of change tracking APIs for this activity to protect the performance of a schools SIMS System and to enable TIs to benefit from the efficiencies of handling much less data.  The evidence below should convince TIs of the need to change and of the benefits from that change both to the SIMS system and the TI.

Worked Example (Local)

Our training data for secondary schools is Green Abbey and has the following number of pupils per year group.

A typical year group has say 150 pupils.  A request to get marks for 100 days would return

• 150 x 2 x 100 = 30k marks for a year group.
• 1000 x 2 x 100 = 200k marks for the whole school. 

On any give day each pupil will get 2 marks and say 10% would get a mark modified for previous or future days.

• Year group changes = 150 x 2 +10% = 330 marks per day.
• Whole school changes  = 1000 x 2 + 10% = 2200 marks per day.

In addition to extracting the marks, TI systems would need to merge the marks in to their data store.

SIMS offers a number of ways to get this data and many of the routes are tried below.

DateTime t1 = DateTime.Now;
if (SIMSInterface.LoginHelper.SIMSlogin(Server, Database, User, Password))
{
                
    
    // Get some Marks for a period using the APIs
    XmlDocument d = SIMSInterface.Attendance.GetAttendanceRead(Start, End);
    // Get some marks using the reporting engine
    XmlDocument d2 = SIMSReportingEngine.ReportingEngine.Run("ATTENDANCE_REPORT",Server, Database, User, Password);
    // Use the change tracking APIs to get changes in that period
    XmlDocument d3 = SIMSInterface.Attendance.GetChangedAttendanceRead(Start, End); 
    // Once we have the data up to date we can pull in incremental changes.
    // NB: Getting todays changes can be optimised further if the TI remembers when they last asked for changes.
    // NB: Always allow for a minor variation in server time.
    XmlDocument d4 = SIMSInterface.Attendance.GetChangedAttendanceRead(DateTime.Today, DateTime.Now);
 }

The TI team obtained the results below:

• Time Taken Login:  00:00:00.8208272
• Time Taken via API:  00:00:04.1453924  (requests a year group for a term)
• Time Taken via Command Reporter :  00:00:08.3096727 (requests a year group for a term)
• Time Taken via Change track :  00:00:09.4383006 (Whole school since start of term above)
• Time Taken via Change track just for today:  00:00:00.3530334 (Whole school today)

The comparison is not a like for like here because the non-change track options only return 1/7 years at the school.  There were no changes today which explains the rapidity of the call, however even fully populated would return ~2200 marks and should take less than 1s.  

The results suggest:

1. Whilst some methods to initially populate the TI system are better than others in the test and on the test data set, if this task is an infrequent (e.g. once, termly, monthly, weekly, daily) it is potentially less impactful on the school's system, particularly overnight.
2. Any requirement to get session attendance data more on a frequent basis needs to use change tracking.
   • We note that many attendance systems seek nearly real time data,

ESS SIMS will look to phase in the review of attendance data exchange models within our accreditation process because the evidence is compelling that the use of change tracking would bring significant benefits to ESS, TIs and mutual customers.

The code needed to get the changed marks is very simple and is shown below.

public static XmlDocument GetChangedAttendanceRead(DateTime forDate, DateTime toDate)
{
     XmlDocument d = new XmlDocument(); ;
     SIMS.Processes.TPAttendanceRead tpar = new SIMS.Processes.TPAttendanceRead();
     d.InnerXml = tpar.GetXmlChangedSessionAttendancesInRange(forDate, DateTime.Now);
      return d;
}