This section details how to integrate with XVault for specific scenarios, providing example SQL where possible.Monitoring Data Freshness in XVault
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]
- 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
– 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
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'
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
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
- 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 < '...'
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
- 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
- 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
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())
- 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.
- 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.
- 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.
- 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.
- 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.