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:

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]

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.

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())

 

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.

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

 

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)

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

And for a list of LearnerPersonals in a given group:

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

 

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:

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'

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:

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'

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:

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

For all teachers associated with a teaching group:
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

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:

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

 

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:

SELECT
LearnerPersonalRefId,
SchoolYear,
SessionsPossible,
SessionsAttendedTotal,
AuthorisedAbsences,
UnauthorisedAbsences,
ZoneId
FROM
LearnerAttendanceSummary

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.

SELECT
AttendanceDate,
Session,
LearnerPersonalRefId,
AttendanceCode
FROM
LearnerAttendance
WHERE
AttendanceDomain = 'Session'
AND AttendanceDate > '...'
AND AttendanceDate < '...'

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!

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 = '...'

 

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:

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

 

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:

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())

  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.