XVault Integrators Guide

Document Scope

This document provides assistance to technical developers using Groupcall XVault to provide data to their systems via SQL.  Its primary purpose is to provide technical direction and guidance when reading data from the XVault database and to highlight potential pitfalls of certain implementation approaches.  While the operation of XVault and how it obtains data from school fall outside of the scope of this document, a high-level overview is provided to enable the reader to contextualise the technical information provided.

Written documentation will never be able to cover all scenarios and/or implementation specific details, and so Groupcall also provide integration consultancy if required.  For more information about this please contact your account manager or your assigned implementation consultant.

XVault Overview

Xvault_Data_Collection

 

XVault Performance

Groupcall XVault is designed to be ready to scale, both in terms of performance achieved by a single instance and in terms of having a design that would enable development of a multi-instance load balanced approach once the requirement arises.

The largest factor in XVault performance is of course the underlying SQL database, which should be specified appropriately for the workload being considered.  Groupcall provide a set of indexes on that database to optimise XVault internal performance and you are able to add in your own indexes where you feel they are required.

XVault SQL access is optimised to cause minimal read and write load on the SQL server, this is detailed in the following section.

XVault Operational Behaviour

Data Requests

XVault works by requesting data from schools, driving the collection of data.  It is possible for schools to transmit change events to XVault in some configurations however since these are saved in the same way as responses to data requests the discussion of events is outside the scope of this document.

 

Collection Templates

XVault supports definition of multiple Collection Templates and allows a school to be assigned to a single Collection Template at any one time.  A Collection Template defines a set of SIF objects to request, any filtering criteria on those objects and the request frequency for each object.

 

New schools

When using Groupcall Data Broker Services with XVault any new school registering with Data Broker Services will be, by default, shown as a new school in XVault within around 60 minutes.  A default Collection Template is typically applied to such schools that requests only SchoolInfo, which is an object providing school summary details such as school name, statutory reporting identifiers and school contact details.  This Collection Template is used to confirm the school is working but not to risk data exposure, by virtue of only requesting public domain information.  A new school should be switched over to a more suitable Collection Template once confirmation of school consent has been double checked.

 

Data import and retention

XVault operates a data deletion policy that effects after the completion of response processing, it operates at a SIF Object level per school.  When a response is received for a SIF object for a school XVault calculates a checksum of the data and compares it to the checksum in the XVault database, if the value matches then the lastReceived column is updated accordingly and no further action is taken; if it doesn’t match then the record is updated and the lastModified column is also updated.  In the case that a new record is created the createdDate column is similarly set.  This applies for every row of data in the XVault database.

Once XVault has finished receiving a response for a SIF Object in a zone it then seeks all rows that have not been touched in some way, and considers them for deletion.  This consideration takes into account a soft-configuration value in XVault determining how long to retain objects for after they stop appearing in responses.  For any object with lastReceived before that threshold point the record is deleted.  This auto-deletion mechanism effectively provides a configurable period where records no longer presented by a school are eventually expired (and removed from the XVault database), without causing data deletion for schools that are experiencing e.g. a loss of service and are not returning any data at all.  The threshold value is measured in hours and can be set to immediate deletion, the default of 48 hours, or disabled and this can be done on a per SIF Object basis.

Where an event message is received from a school regarding the deletion of a record from the MIS data source, XVault will apply that change immediately regardless of the retention settings.

 

SIF Data Objects

Groupcall XVault stores data in a slightly normalised version of the UK SIF data model, hence for any queries about data specification for a specific SIF data object, element, or attribute you should refer to the UK SIF specification.

Object Data Area Contents[1]
SchoolInfo School Information about the school – e.g. name, head teacher, contact information, governance, phase.
LearnerPersonal Learner Personal information about a learner, including ULN, UPN, Address, DoB, Phone, Email.
LearnerSchoolEnrolment Learner/School Information about the learner in relation to the school, e.g. start date, academic year.
WorkforcePersonal Workforce Personal information about an employee in a school, including Address, DoB, Phone, Email and Post held.
SchoolGroupType School Different types of Group the school provides – e.g. class (lesson), year, registration (form).
SchoolGroup School Specific groups in the school, their type and which WorkforcePersonal are linked.
LearnerGroupEnrolment School Membership of LearnerPersonals into SchoolGroup.
ContactPersonal Parental contacts Information about a contact, including address, phone, email.
LearnerContact Parental contacts The relationship between a LearnerPersonal and a ContactPersonal, including parental responsibility and contact priority.
LearnerSpecialNeeds SEN Special needs status for a learner, and what their requirements are.
LearnerAttendance Attendance Detailed Session attendance marks for a learner.  This can optionally also support Lesson attendance for some MIS products.
LearnerAttendanceSummary Attendance Summary attendance statistics for a learner.
PersonPicture Learner, Workforce Photo of a learner or a workforce.
LearnerExclusion Exclusions Exclusion periods for a learner.
LearnerEntitlement Free School Meals Periods of FSM entitlement for a learner, historic entitlements are supported for most MIS products.
LearnerBehaviourIncident Behaviour/Achievement Negative or positive behaviour  events for a learner
TermInfo Timetable Details the term dates in the current year for the school.
Cycle Timetable Provides the structure of the current timetable(s), in terms of days, periods, timings, logical to temporal mappings, etc.
TTRoom Timetable Defines rooms used by the timetable
TTTeacher Timetable Defines teachers used by the timetable, which typically share their identifying ‘RefId’ with WorkforcePersonal objects.
TTSubject Timetable Defines timetabled subjects
TeachingGroup Timetable Defines timetabled groups of students, typically mapping back to the identifying ‘RefId’ of a SchoolGroup.
Lesson Timetable Defines a Lesson occurring during a timetable period, involving a TTSubject and a TeachingGroup.
AssessmentResultGradeSet Assessment Represents grades over time for a result.
AssessmentSession Assessment Defines an assessment period, such as summer exams.
AssessmentResultComponent Assessment Defines an assessment aspect, e.g. Drama practical
AssessmentResultComponentGroup Assessment Defines a grouping of assessment aspects.
AssessmentLearnerSet Assessment Defines a set of learners related to an aspect.
LearnerAssessmentResult Assessment Defines an assessment result, awarded on a date and optionally during a specific assessment session.

 


[1] Fullness / presence of contents will vary by MIS.


SIF Data Objects Entity Relationship Diagram

This diagram is available via the UK SIF Association website.  The UK SIF community develops the SIF standard for data in the UK.

SIF Data Objects Entity Relationship Diagram

Click to enlarge and zoom

SQL Tables per Data Object

Object Tables Join on
SchoolInfo SchoolInfo (no sub-tables)
LearnerPersonal LearnerPersonalOtherAddress,

OtherEmail,

OtherId,

OtherPhoneNumber

 OwnerRefId, OwnerType

OwnerRefId, OwnerType

OwnerRefId, OwnerType

OwnerRefId, OwnerType

LearnerSchoolEnrolment LearnerSchoolEnrolment (no sub-tables)
WorkforcePersonal WorkforcePersonalCurrentAssignment

OtherAddress,

OtherEmail,

OtherId,

OtherPhoneNumber

OwnerRefId, OwnerType

OwnerRefId, OwnerType

OwnerRefId, OwnerType

OwnerRefId, OwnerType

OwnerRefId, OwnerType

SchoolGroupType SchoolGroupType (no sub-tables)
SchoolGroup SchoolGroupTeacher OwnerRefId, OwnerType
LearnerGroupEnrolment LearnerGroupEnrolment (no sub-tables)
ContactPersonal ContactPersonalOtherAddress,

OtherEmail,

OtherId,

OtherPhoneNumber

OwnerRefId, OwnerType

OwnerRefId, OwnerType

OwnerRefId, OwnerType

OwnerRefId, OwnerType

LearnerContact LearnerContact (no sub-tables)
LearnerSpecialNeeds LearnerSpecialNeedsSENType OwnerRefId, OwnerType
LearnerAttendance LearnerAttendance (no sub-tables)
LearnerAttendanceSummary LearnerAttendanceSummary (no sub-tables)
PersonPicture PersonPicture[1] (no sub-tables)
LearnerExclusion LearnerExclusion (no sub-tables)
LearnerEntitlement LearnerEntitlementEntitlementPeriod OwnerRefId, OwnerType
LearnerBehaviourIncident LearnerBehaviourIncidentEmployee

Learner

Action

OwnerRefId, OwnerType

OwnerRefId, OwnerType

OwnerRefId, OwnerType

TermInfo TermInfo (no sub-tables)
Cycle CyclePeriod

TTDay

TTDayDate

OwnerRefId, OwnerType

OwnerRefId, OwnerType

OwnerRefId, OwnerType, TTDayId

TTRoom TTRoom (no sub-tables)
TTTeacher TTTeacher (no sub-tables)
TTSubject TTSubject (no sub-tables)
TeachingGroup TeachingGroupGroupTeacher OwnerRefId, OwnerType
Lesson LessonTeacher OwnerRefId, OwnerType
AssessmentResultGradeSet AssessmentResultGradeSetGradeSet

Grade

 OwnerRefId, OwnerType

OwnerRefId, OwnerType, GradeSetId

AssessmentSession AssessmentSession (no sub-tables)
AssessmentResultComponent AssessmentResultComponentSubject

AssessmentStageRefId

MarkSet

OwnerRefId, OwnerType OwnerRefId, OwnerType OwnerRefId, OwnerType
AssessmentResultComponentGroup AssessmentResultComponentGroupAssessmentResultComponentRefId OwnerRefId, OwnerType
AssessmentLearnerSet AssessmentLearnerSetLearnerPersonalRefId OwnerRefId, OwnerType
LearnerAssessmentResult LearnerAssessmentResultAssessmentSessionRefId  OwnerRefId, OwnerType


[1] PersonPicture can also optionally save images out to a folder on the local disk, with files named by RefId.


SIF RefIds

SIF data objects are identified using RefIds; these are basically GUIDs with a different formatting.

A RefId within a zone can be relied upon to be unique, however you should not rely on RefId to be unique between zones and for that reason you should safeguard against incorrect joins between SIF objects by matching on both RefId and ZoneId.

The RefId for a SIF object is ‘near-persistent’ within a zone; that is, it shouldn’t change but it is possible for it to (for example by virtue of a data integrity issue or due to re-initialisation of the agent providing data to XVault).  The likelihood of change varies by source MIS as some products keep a suitable value within the MIS and some require the extracting agent, typically Xporter, to generate and assign RefIds against other MIS identifier values.  The latter approach obviously carries a slightly increased risk of that mapping table being lost when e.g. a school reinstalls the agent.

RefIds do not move with any person (or other SIF data) between schools, as a learner’s UPN or ULN might.  Hence a learner present in two schools would appear in XVault twice, once in each school zone and with a different RefId in each case.  See below regarding Multi-site or Duplicate learners for more information.

In the case of LearnerAttendance and LearnerEntitlement, Groupcall Xporter agents assign a RefId that is computed rather than randomly generated.  This is to ensure writeback compatibility for those data objects, and they remain guaranteed unique within a zone.

XVault - Typical Integration Scenarios

This section details how to integrate with XVault for specific scenarios, providing example SQL where possible.

Monitoring Data Freshness in XVault
Data monitoring for XVault showing the full end-to-end status of transmission for every school is available via Groupcall Dashboard, a separate Groupcall product designed to simplify operational management and support of your Groupcall estate. However it is possible to use SQL queries to give you a view of general data shortages in the XVault database.

XVault has two tables for this:

  • ZoneDetails defines each school zone connected to XVault and which collection template it is using.
  • SifObject defines and controls the collection of each SIF object for each zone, based on the collection template assigned in ZoneDetails.

The SifObject table is updated each time data is received for a school/object pair and hence you can obtain a list of schools that haven’t, for example, returned data in the last 72 hours by running a query like this:

[sql]SELECT
z.[zoneId],
z.[schoolName],
s.[templateName],
s.[elementName],
s.[LastDataRecieved] FROM
[ZoneDetails] z
INNER JOIN [SIFObject] s on z.[id] = s.[zoneid] WHERE
s.[allowedSubscribe] = 1
AND s.[lastDataRecieved] < DATEADD(HOUR, -72, GETDATE())
ORDER BY
s.[lastDataRecieved][/sql]
Extracting a list of currently enrolled students
This is one of the most common things partners need to do, however you must take account of three caveats:
  • XVault may contain subsidiary or guest learners that are not ‘primary’ records for a learner.
  • XVault may contain preadmission or leaver records that are not current
  • The SIF standard stores the current academic year of learners in LearnerSchoolEnrolment, not in LearnerPersonal – because the latter is of the learner not of the learner in a school.

You can use a query such as the below to obtain the current primary records for all learners.

[sql]SELECT
lp.[RefId],
lp.[UPN],
lp.[ULN],
lp.[Name_GivenName],
lp.[Name_FamilyName],
lp.[Name_PreferredGivenName],
lp.[Name_PreferredFamilyName],
lp.[Gender],
lp.[BirthDate],
lp.[Email_Address],
lp.[Email_Type],
lse.[MembershipType],
lse.[NCYearGroupActual],
lp.[ZoneId] FROM
[LearnerPersonal] lp
INNER JOIN [LearnerSchoolEnrolment] lse on lp.RefId = lse.LearnerPersonalRefId and lp.ZoneId = lse.ZoneId
WHERE
lse.[MembershipType] IN ('C', 'M')
AND (lse.[ExitDate] IS NULL or lse.[ExitDate] > GETDATE())
AND (lse.[EntryDate] IS NULL or lse.[EntryDate] <= GETDATE())[/sql]

 

Extracting a list of Staff

The following SQL query will obtain staff and their current posts, note that it is possible for staff to have more than one concurrent post and more than one ‘Other email’, hence this specific query will cause duplicate rows – but it’s purpose is to show the relation between the tables.

[sql]SELECT
wfp.[RefId],
wfp.[Name_Title],
wfp.[Name_GivenName],
wfp.[Name_FamilyName],
wfp.[Name_PreferredGivenName],
wfp.[Name_PreferredFamilyName],
wfp.[Gender],
wfp.[Email_Address] as [MainEmail],
wfp.[Email_Type] as [MainEmailType],
ca.Post,
oe.Email_Address as [OtherEmail],
oe.Email_Type as [OtherEmailType],
wfp.ZoneId,
wfp.LastReceivedDate
FROM
[WorkforcePersonal] wfp
LEFT OUTER JOIN [CurrentAssignment] ca on wfp.RefId = ca.OwnerRefId and ca.OwnerType = 'WorkforcePersonal' and wfp.ZoneId = ca.zoneId
LEFT OUTER JOIN [OtherEmail] oe on wfp.RefId = oe.OwnerRefId and oe.OwnerType = 'WorkforcePersonal' and wfp.ZoneId = oe.zoneId[/sql]

 

Extracting a list of School Groups and/or Teaching Groups
School Groups in SIF are essentially a grouping of students and staff with a specific name and type, the type has two properties

– a category (Generic, Registration or Teaching), and a title (User, Year Group, House, etc)

[sql]SELECT
sgt.GroupType,
sgt.Title,
sg.GroupName,
sg.[Description],
sg.WorkforcePersonalRefId as [MainWorkforcePersonalRefId],
t.WorkforcePersonalRefId as [OtherWorkforcePersonalRefId],
t.[Role] as [OtherWorkforcePersonalRole] FROM
SchoolGroupType sgt
INNER JOIN SchoolGroup sg on sg.SchoolGroupTypeRefId = sgt.RefId and sg.ZoneId = sgt.ZoneId
LEFT OUTER JOIN Teacher t on sg.RefId = t.OwnerRefId and t.OwnerType = 'SchoolGroup' and sg.ZoneId = t.zoneId[/sql]

And for a list of LearnerPersonals in a given group:

[sql]SELECT
LearnerPersonalRefId
FROM
LearnerGroupEnrolment
WHERE
SchoolGroupRefId = '...' and ZoneId = '...'
AND (EntryDate IS NULL or EntryDate <= GETDATE()) AND (ExitDate IS NULL or ExitDate > GETDATE())[/sql]

 

Extracting Timetable Information
Timetable in SIF is relatively straightforward, and is defined in the Cycle object.

A Cycle has:

  • A RefId and a name
  • A number of days in the Cycle – e.g. 5, or 10 for a two-week timetable.
  • A list of standard periods in the cycle
  • A list of TTDays in the Cycle, each TTDay has:
    • An arbitrary TTDayID, e.g. Mo2 for the second Monday.  This is set by the school.
    • A short and long name for the day
    • A Day Index, indicating the order of the TTDay in the Cycle
    • A list of periods in the TTDay, each period has:
      • A PeriodIndex, indicating the order of the period in the TTDay
      • A long name and short name, e.g. Period 2 or Registration.
      • A start time and end time
      • A type (T = Teaching, N = Non-teaching)
  • Groupcall Xporter supplements this data where possible to provide a list of dates that a TTDay is effective on, enabling logical-to-temporal mapping.

Periods Ordered Across A Cycle

To obtain a list of periods ordered across a cycle you can use the following query:

[sql]SELECT
c.RefId,
c.CycleDays,
c.CycleName,
ttd.DayIndex,
ttd.ShortName,
ttd.LongName,
pd.PeriodIndex,
CONVERT(VARCHAR(8), StartTime, 108) as StartTime,
CONVERT(VARCHAR(8), EndTime, 108) as EndTime,
pd.ShortName,
pd.LongName,
pd.Type
FROM
Period pd
INNER JOIN TTDay ttd on pd.OwnerRefId = ttd.OwnerRefId AND ttd.OwnerType = 'Cycle' and pd.zoneId = ttd.zoneId and pd.TTDayId = ttd.TTDayId
INNER JOIN Cycle c on c.RefId = ttd.OwnerRefId and c.ZoneId = ttd.zoneId
WHERE
pd. OwnerType = 'TTDay'[/sql]
Note that Cycle does also contain a generic period list, where pd.OwnerType is ‘Cycle’ but since schools can have differing sets of periods on different days the implementer is generally directed to the per-TTDay period list.

Determine Which TTday Is Effective On A Given Date

To determine which TTDay is effective on a given date, you can query via the TTDayDate table:
[sql]SELECT
c.RefId,
c.CycleDays,
c.CycleName,
ttd.DayIndex,
ttd.ShortName,
ttd.LongName,
pd.PeriodIndex,
CONVERT(VARCHAR(8), StartTime, 108) as StartTime,
CONVERT(VARCHAR(8), EndTime, 108) as EndTime,
pd.ShortName,
pd.LongName,
pd.Type
FROM
Period pd
INNER JOIN TTDay ttd on pd.OwnerRefId = ttd.OwnerRefId AND ttd.OwnerType = 'Cycle' and pd.zoneId = ttd.zoneId and pd.TTDayId = ttd.TTDayId
INNER JOIN Cycle c on c.RefId = ttd.OwnerRefId and c.ZoneId = ttd.zoneId
INNER JOIN TTDayDate ttdd on c.RefId = ttdd.OwnerRefId and ttdd.OwnerType = 'Cycle' and ttdd.zoneId = c.ZoneId and ttdd.TTDayId = ttd.TTDayId
WHERE
pd.OwnerType = 'TTDay'
AND ttdd.DayDate = '2011-09-05'[/sql]

Which Lessons To Present In Given Periods For A Given Member Of Staff

The likely next requirement is to determine for a member of staff which lessons to present in those periods, this can be achieved by identifying the Lesson objects that apply for the given TTDayId and PeriodIndex. The main teacher can be identified via the GroupTeacher table, where the source MIS supports this concept, but all associated teachers can be identified via the TeachingGroup object.

For the main teacher, where the source MIS supports this concept:
[sql]SELECT
l.TTDayId,
l.PeriodIndex,
ttr.Code as [TTRoomCode],
l.Type as [LessonType],
l.Length as [PeriodsCovered],
t.TTTeacherRefId as [WorkforcePersonalRefId],
tg.GroupDefinition_Shortname,
tg.GroupDefinition_Longname,
l.ZoneId
FROM
Lesson l
LEFT OUTER JOIN Teacher t on t.OwnerRefId = l.RefId and t.OwnerType = 'Lesson' and t.zoneId = l.ZoneId
INNER JOIN TeachingGroup tg on tg.RefId = l.TeachingGroupRefId and tg.ZoneId = l.zoneId
LEFT OUTER JOIN TTRoom ttr on ttr.RefId = l.TTRoomRefId[/sql] For all teachers associated with a teaching group:
[sql]SELECT
l.TTDayId,
l.PeriodIndex,
ttr.Code as [TTRoomCode],
l.Type as [LessonType],
l.Length as [PeriodsCovered],
tts.Code as [SubjectCode],
tts.Description as [SubjectDescription],
t.WorkforcePersonalRefId as [WorkforcePersonalRefId],
t.Role as [WorkforcePersonalRole],
tg.GroupDefinition_Shortname,
tg.GroupDefinition_Longname,
l.ZoneId
FROM
Lesson l
INNER JOIN TeachingGroup tg on tg.RefId = l.TeachingGroupRefId and tg.ZoneId = l.zoneId
LEFT OUTER JOIN Teacher t on t.OwnerRefId = tg.RefId and t.OwnerType = 'SchoolGroup' and t.zoneId = tg.ZoneId
LEFT OUTER JOIN TTSubject tts on tg.GroupDefinition_TTSubjectRefId = tts.RefId and tg.zoneid = tts.zoneid
LEFT OUTER JOIN TTRoom ttr on ttr.RefId = l.TTRoomRefId[/sql]

Note that, for Groupcall agents, TTTeacherRefId is common with WorkforcePersonalRefId to reduce the number of joins required and the same also applies of TeachingGroup and SchoolGroup RefIds. Hence in both examples above we use TTTeacherRefId as a WorkforcePersonalRefId and in the latter example we also use TeachingGroupRefId to join to SchoolGroup.RefId.

 

Identify The Timetable For A Specific Student

The query above can be adjusted slightly to join LearnerGroupEnrolment and identify the timetable for a specific student:

[sql]SELECT
l.TTDayId,
l.PeriodIndex,
ttr.Code as [TTRoomCode],
l.Type as [LessonType],
l.Length as [PeriodsCovered],
tts.Code as [SubjectCode],
tts.Description as [SubjectDescription],
lge.LearnerPersonalRefId,
tg.GroupDefinition_Shortname,
tg.GroupDefinition_Longname,
l.ZoneId
FROM
Lesson l
INNER JOIN TeachingGroup tg on tg.RefId = l.TeachingGroupRefId and tg.ZoneId = l.zoneId
INNER JOIN LearnerGroupEnrolment lge on l.TeachingGroupRefId = lge.SchoolGroupRefId and l.ZoneId = lge.ZoneId
LEFT OUTER JOIN TTSubject tts on tg.GroupDefinition_TTSubjectRefId = tts.RefId and tg.zoneid = tts.zoneid
LEFT OUTER JOIN TTRoom ttr on ttr.RefId = l.TTRoomRefId[/sql]

 

Extracting Attendance
XVault supports three different types of Attendance:
  • Summary – simple statistics for authorised and unauthorised session absence
  • Session – marks for AM and PM attendance
  • Lesson – marks for learners in specific lesson periods.

The former two are subject to statutory definition in England and Wales while the latter is defined by each school as required, although usually based on the statutory codes with additions.

 

Summary Attendance Information

To obtain summary attendance information is a simple query in a single table:
[sql]SELECT
LearnerPersonalRefId,
SchoolYear,
SessionsPossible,
SessionsAttendedTotal,
AuthorisedAbsences,
UnauthorisedAbsences,
ZoneId
FROM
LearnerAttendanceSummary[/sql]

Session Attendance Mark String

Groupcall agents also provide a Marks column providing the Session attendance mark string to date for further analysis or presentation.

For attendance you should be very considerate to the volume of data you might be querying against. For a school of 1,500 students teaching for e.g. 195 (52 – 13 * 5) days in a year this will cause 585,000 (1500 * 195 * 2) session attendance records to be stored for that year. If you spread this across a local authority of e.g. 80,000 students this could result in just over 3.1m session attendance records per year. Hence you should carefully consider how you index, join and filter against such a large table – noting that the same table also stores lesson attendance marks which can number up to 8 or 10 marks per day, a scale factor of at least four against our examples above.

[sql]SELECT
AttendanceDate,
Session,
LearnerPersonalRefId,
AttendanceCode
FROM
LearnerAttendance
WHERE
AttendanceDomain = 'Session'
AND AttendanceDate > '...'
AND AttendanceDate < '...'[/sql]
Note the SIF attendance code types, and particularly SIF-specific code ‘K’ which means that there is no mark stored in the MIS for this session.

Lesson Attendance

For Lesson attendance it is necessary to relate back to a specific SchoolGroup or TeachingGroup by timepoint, as most MIS products don’t store Lesson attendance marks against a lesson but instead against a point in time. We therefore have to join across the timetable, first determining the TTDay that the AttendanceDate refers to, then determining the PeriodIndex on that TTDay that StartTime refers to, then determining which Lessons are occurring at that time and joining across LearnerGroupEnrolment to establish which of those lessons the learner was in!

[sql]SELECT
la.LearnerPersonalRefId,
la.AttendanceDate,
la.StartTime,
la.AttendanceCode,
ttd.LongName,
p.EndTime,
tg.GroupDefinition_Shortname,
tg.GroupDefinition_Longname,
tts.Description as [SubjectDescription] FROM
LearnerAttendance la
INNER JOIN TTDayDate ttdd on la.AttendanceDate = ttdd.DayDate and la.ZoneId = ttdd.zoneId
INNER JOIN TTDay ttd on ttd.TTDayId = ttdd.TTDayId and ttd.zoneId = ttdd.zoneId
INNER JOIN Period p on ttd.TTDayId = p.TTDayId and CONVERT(VARCHAR(8), p.StartTime, 108) = CONVERT(VARCHAR(8), la.StartTime, 108) and p.zoneId = ttd.zoneId
INNER JOIN Lesson l on l.TTDayId = ttdd.TTDayId and l.PeriodIndex = p.PeriodIndex and l.ZoneId = p.zoneId
INNER JOIN LearnerGroupEnrolment lge on lge.SchoolGroupRefId = l.TeachingGroupRefId and lge.LearnerPersonalRefId = la.LearnerPersonalRefId and lge.ZoneId = l.ZoneId
LEFT OUTER JOIN TeachingGroup tg on tg.RefId = l.TeachingGroupRefId and tg.ZoneId = l.ZoneId
LEFT OUTER JOIN TTSubject tts on tts.RefId = tg.GroupDefinition_TTSubjectRefId and tts.ZoneId = tg.ZoneId
WHERE
la.AttendanceDomain = 'Lesson'
AND (lge.EntryDate IS NULL or lge.EntryDate <= GETDATE()) AND (lge.ExitDate IS NULL or lge.ExitDate > GETDATE())
AND la.LearnerPersonalRefId = '...'
AND la.AttendanceDate = '...'[/sql]

 

Extracting Assessment Results
Assessment in SIF is quite normalised, and XVault supports the following SIF objects:

 

  • AssessmentResultComponent – the equivalent of an Aspect
  • AssessmentResultComponentGroup –the equivalent of a Mark Sheet
  • AssessmentSession – periods of assessment e.g. Summer assessment
  • LearnerAssessmentResult – specific assessment results for a learner

Caveats:

  • Assessment is big, schools can create millions of records.  Consequently Groupcall Agents provide only Key Stage results back by default.
  • Currently the SIF data model doesn’t make provision for forecast grades, although if a business case arose this could likely be accommodated.

An example query that provides results for a learner, attributing to a date, an aspect, a subject and potentially an assessment session is shown below:
[sql]SELECT
lar.LearnerPersonalRefId,
lar.Result,
lar.AchievementDate,
arc.Name,
arcs.[Subject],
sessname.SIF_Value as SessionName,
sessenddate.SIF_Value as SessionEndDate,
lar.ZoneId
FROM
LearnerAssessmentResult lar
inner join AssessmentResultComponent arc on lar.AssessmentComponentRefId = arc.RefId and arc.ZoneId = lar.ZoneId
inner join [Subject] arcs on arc.RefId = arcs.OwnerRefId and arcs.OwnerType = 'AssessmentResultComponent' and arcs.zoneId = arc.ZoneId
inner join AssessmentSessionRefId sesslink on sesslink.OwnerRefId = lar.RefId and sesslink.OwnerType = 'LearnerAssessmentResult' and sesslink.zoneId = lar.ZoneId
left outer join SIF_ExtendedElement sessname on sessname.OwnerRefId = sesslink.AssessmentSessionRefId and sessname.OwnerType = 'AssessmentSession' and sessname.SIF_Name = 'Name' and sessname.zoneId = lar.ZoneId
left outer join SIF_ExtendedElement sessenddate on sessenddate.OwnerRefId = sesslink.AssessmentSessionRefId and sessenddate.OwnerType = 'AssessmentSession' and sessenddate.SIF_Name = 'EndDate' and sessenddate.zoneId = lar.ZoneId
ORDER BY
lar.LearnerPersonalRefId,
arcs.[Subject],
lar.AchievementDate
[/sql]

 

Duplicate or Multi-Site Learners
One of the most common challenges when identifying learners in an aggregated data store is picking out duplicate learners, where those duplicates may or may not be intended. It is possible for a learner to legitimately be in two or more schools for their learning, but it is also possible for a learner to accidentally be in two schools through them moving school and their old school being offline to XVault and preventing the deletion of their old record.

It is possible in both cases to identify such learners once a duplicate record has been identified.  Let’s assume that we have two learners, A and B, that appear to be duplicates – that is they share common demographic details within LearnerPersonal and are presenting in different zones:

[sql]SELECT
[LearnerPersonalRefId],
[EntryDate],
[ExitDate],
[MembershipType],
[LastReceivedDate],
[ZoneId] FROM
[LearnerSchoolEnrolment] WHERE
[LearnerPersonalRefId] IN ([your list of duplicate RefIds])
AND ([ExitDate] IS NULL or [ExitDate] > GETDATE())
AND ([EntryDate] IS NULL or [EntryDate] <= GETDATE())[/sql]
  1. Check the current LearnerSchoolEnrolment table for both learners.  Specifically note the MembershipType column.  The values for this are in the UK SIF specification, but suffice to say that the following values are most common:
    • C – this is the learner’s current enrolment.
    • M – this is the learner’s current main enrolment, they may also exist in another school.
    • S – this is a subsidiary enrolment for a learner, they will have a Main enrolment in another school and attend this school for e.g. a specific subject or course.
    • G – the learner is a guest in this school and has their enrolment elsewhere.
      • Hence if learner A has a MembershipType of G and learner B has a MembershipType of M then you should hold precedence for learner B over learner A.
  2. Check the EntryDate and ExitDate columns for both learners.  By default school agents do not provide learners who are leavers or future enrolments but this functionality is possible and could, for example, result in a record for learner A being their current enrolment and learner B being their future enrolment (preadmission) at their next school.
  3. Check the lastReceived for each learner, if A is significantly more stale than B then most likely there is an auto-deletion issue in XVault or the source school has yet to upload a record set that doesn’t contain A.
  4. Allow 72 hours to pass and confirm if the issue still exists, XVault may not yet have had sufficient data to meet its auto-delete criteria.  Typically a learner movement means the learner stops appearing in the current set from their old school and starts appearing in the current set from their new school; however due to the nature of school data entry this can also happen the other way around and hence can take a few days to be automatically corrected.
  5. If both learners are current and there is no evidence to suggest which takes precedence then you should speak to the schools providing the records, they’ll be able to clarify and it may be that one of them has accidentally recorded their statutory data incorrectly.

When a Learner moves school within England and Wales the schools typically exchange a student definition file (called a CTF) that migrates the learner’s records from one school to the next.  This migration includes Key Stage assessment, parental contacts, attendance summary history, free school meal entitlements, their school history and their UPN and ULN where applicable.

 

 

Use of SQL Triggers

Groupcall XVault will continue to operate if the SQL database has triggers created within it; you should exercise caution in not causing excessive or recursive locks when invoking triggers.  The safest approach with triggers is to cause a change to an non-XVault table in the same database.  You can create extra tables in the XVault database if you require, however we strongly recommend prefixing them with something unique to your organisation to guard against future table name conflicts.

One of the most common requirements for integrators is to identify rows that have recently been deleted from the XVault database, for example the removal of LearnerPersonal records.  This could, for example, be used to populate a table of LearnerPersonal RefIds that have recently been deleted or recently modified.

Show Examples

For example, if a table was created and indexed as below:

[sql]CREATE TABLE XVaultUpdateHistory
(
TableName VARCHAR(40) not null,
RefId VARCHAR(64) not null,
CreatedDate DATETIME null,
[ModifiedDate] DATETIME null,
Dirty INT not null DEFAULT 0,
[Action] VARCHAR(3)

CONSTRAINT [XVaultUpdateHistory_PK] PRIMARY KEY CLUSTERED
(
[TableName] ASC,
[RefId] ASC
)

);

CREATE INDEX XVUpdateHistory_Dirty ON XVaultUpdateHistory(Dirty);
CREATE INDEX XVUpdateHistory_ModifiedDate ON XVaultUpdateHistory(ModifiedDate);
CREATE INDEX XVUpdateHistory_Action ON XVaultUpdateHistory([Action]);[/sql]

Then a trigger could be created on LearnerPersonal to pick up delete events:

[sql]CREATE TRIGGER after_delete_trigger ON LearnerPersonal FOR DELETE AS
SET NOCOUNT ON;
IF EXISTS(SELECT * FROM DELETED)
BEGIN
DECLARE @TableName VARCHAR(40);
DECLARE @Action VARCHAR(3);
DECLARE @RefId VARCHAR(64);
DECLARE @ModifiedDate DATETIME;

SET @TableName = 'LearnerPersonal';
SET @Action = 'DEL';
SELECT @RefId = [RefId],@ModifiedDate = [ModifiedDate] FROM DELETED;

UPDATE XVaultUpdateHistory
SET
Action = @Action,
ModifiedDate = @ModifiedDate,
Dirty = 1
WHERE
TableName=@TableName
AND RefId=@refid;

IF (@@rowcount=0)
BEGIN
-- no history table object was updated so it must not be
-- in the table yet, go create it instead
INSERT INTO XVaultUpdateHistory (TableName, RefId, CreatedDate, ModifiedDate, Dirty, Action)
values(@TableName, @RefId, CURRENT_TIMESTAMP, @ModifiedDate, 1, @Action);
END;
END;[/sql]

Similarly, code could be created to populate the same table on Update events too:

[sql]CREATE TRIGGER after_update_trigger ON LearnerPersonal FOR UPDATE AS
SET NOCOUNT ON;
IF (UPDATE(ModifiedDate))
BEGIN
DECLARE @TableName VARCHAR(40);
DECLARE @Action VARCHAR(3);
DECLARE @RefId VARCHAR(64);
DECLARE @ModifiedDate DATETIME;

SET @TableName = 'LearnerPersonal';
SET @Action = 'UPD';
SELECT @RefId = [RefId],@ModifiedDate = [ModifiedDate] FROM INSERTED;

UPDATE XVaultUpdateHistory
SET
Action = @Action,
ModifiedDate = @ModifiedDate,
Dirty = 1
WHERE
TableName=@TableName
AND RefId=@refid;

IF (@@rowcount=0)
BEGIN
-- no history table object was updated so it must not be -- in the table yet, go create it instead
INSERT INTO XVaultUpdateHistory (TableName, RefId, CreatedDate, ModifiedDate, Dirty, Action)
values(@TableName, @RefId, CURRENT_TIMESTAMP, @ModifiedDate, 1, @Action);
END;
END;[/sql]

The use of triggers is outside of the scope of Groupcall support boundary for XVault; as such the above examples are provided as-is with no warranty.

 
 

Use of SQL Indexes

Groupcall XVault will continue to operate if the SQL database has indexes created within it; you should exercise caution in your indexing policy to ensure that you do not create excessive or duplicate indexes.

The following SQL allows you to determine SQL indexes already present in the Groupcall XVault database, and Groupcall can provide a default index selection to further optimise the performance of most XVault installations.

[sql]SELECT
schema_name(schema_id) as SchemaName, OBJECT_NAME(si.object_id) as TableName, si.name as IndexName,
(CASE is_primary_key WHEN 1 THEN 'PK' ELSE '' END) as PK,
(CASE is_unique WHEN 1 THEN '1' ELSE '0' END)+' '+
(CASE si.type WHEN 1 THEN 'C' WHEN 3 THEN 'X' ELSE 'B' END)+' '+ -- B=basic, C=Clustered, X=XML
(CASE INDEXKEY_PROPERTY(si.object_id,index_id,1,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+
(CASE INDEXKEY_PROPERTY(si.object_id,index_id,2,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+
(CASE INDEXKEY_PROPERTY(si.object_id,index_id,3,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+
(CASE INDEXKEY_PROPERTY(si.object_id,index_id,4,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+
(CASE INDEXKEY_PROPERTY(si.object_id,index_id,5,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+
(CASE INDEXKEY_PROPERTY(si.object_id,index_id,6,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+
'' as 'Type',
INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,1) as Key1,
INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,2) as Key2,
INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,3) as Key3,
INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,4) as Key4,
INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,5) as Key5,
INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,6) as Key6
FROM sys.indexes as si
LEFT JOIN sys.objects as so on so.object_id=si.object_id
WHERE index_id>0 -- omit the default heap
and OBJECTPROPERTY(si.object_id,'IsMsShipped')=0 -- omit system tables
and not (schema_name(schema_id)='dbo' and OBJECT_NAME(si.object_id)='sysdiagrams') -- omit sysdiagrams
-- uncomment to exclude xv_* indexes
-- and si.name not like 'xv_%'
ORDER BY SchemaName,TableName,IndexName[/sql]

In terms of the recommended assignment of indexes within XVault this will depend fully on how you query into it, clearly the focus of any additional indexes you add will be determined by which columns you filter or join upon during your daily use.

Next Steps...

If you need any further assistance or get in to any difficulty, then please contact Groupcall Support. If the issue affects Groupcall Partner products you should refer to the support arrangements for that specific Groupcall Partner.

For issues regarding integration with XVault, Groupcall provide consultancy services to assist you in integrating with XVault quickly and efficiently based on your specific needs.  If you’d like to engage these services then please contact your Implementation Consultant or Account Manager.

…And Finally

Have you followed Groupcall on Twitter and Facebook? Stay informed, get the latest news, updates and useful tips on all of our products!